Oracle ProC 插入值((选择 ...))

2022-01-23 00:00:00 sql subquery insert oracle oracle-pro-c

在 Oracle 10g 上运行 Pro*C.

Running Pro*C on Oracle 10g.

我希望在插入语句值子句中执行子查询.此 sql 查询完全有效，在 TOAD 中运行没有问题，但 Pro*C 无法解析查询.

I am looking to do a subquery within an insert statement values clause. This sql query is fully valid and runs within TOAD with no problems, but Pro*C fails to parse the query.

EXEC SQL INSERT INTO TARGET_ATTACHMENT 
      (
          TARGET_ID
          FILENAME
      ) 
      VALUES ( 
         :targetID,
         ( SELECT CREATED_FLAG from TARGET t where t.TARGET_ID = :targetID ) || '.tif'
      )

如果我删除:

( SELECT (CREATED_FLAG || DISPLAY_ID) from TARGET t where t.TARGET_ID = :targetID ) ||**".

Pro*C 编译器工作正常，一切都按预期编译和运行.

The Pro*C compiler works and everything compiles and runs as expected.

如果我不删除:Pro*C 编译器抛出语法错误.

If I DO NOT remove: The Pro*C compiler throws a syntax error.

1>Syntax error at line 128, column 12, file        d:SVN...TA.pc:
1>Error at line 128, column 12 in file d:SVN...
1>...TA.pc
1>                ( select CREATED_FLAG from target t where t.TARGET_ID = :targetID )
1>...........1
1>PCC-S-02201, Encountered the symbol "CREATED_FLAG" when expecting one of the fol
1>lowing:
1>   ( ) * + - / . @ | at, day, hour, minute, month, second, year,

这是一个问题，因为我希望 Pro*C 能够在值 caluse 内编译子查询:

This is a problem, as I expect Pro*C to be able to compile subquerys within a values caluse:

即.

INSERT into table1 (col1) values ( (select t2.singleCol from table2 t2 where t2.priKey = :priKey) )

这是 Pro*C 的预期行为吗?还是应该支持 values 子句中的子查询?

Is this expected behaviour of Pro*C? or Should it support subqueries within the values clause?

推荐答案

可能将子查询改为:

( SELECT CREATED_FLAG || '.tif' from TARGET t where t.TARGET_ID = :targetID )

我认为我从未见过像您尝试的那样附加到子查询的内容.

I dont think I have ever seen something appended to a subquery the way you were attempting.

相关文章