我有如下的SQL语句: SELECT LOT_ID,
SOURCE_PART_NAME,
SUBSTR (MAX (SYS_CONNECT_BY_PATH(PHY_MATERIAL_ID, ',')), 2) PHY_MATERIAL_ID,
START_TIME
FROM (SELECT LOT_ID,
SOURCE_PART_NAME,
PHY_MATERIAL_ID,
rn,
START_TIME,
LEAD(rn)OVER(PARTITION BY LOT_ID ORDER BY rn) as "rn1"
FROM (SELECT lot_id,
SOURCE_PART_NAME,
PHY_MATERIAL_ID,
TO_CHAR(START_TIME, 'YYYY/MM/DD_HH24:MI:SS') START_TIME,
ROW_NUMBER()OVER(ORDER BY PHY_MATERIAL_ID) rn
FROM YMS_TBL_LOT_START_INFO
WHERE (TO_CHAR(START_TIME, 'YYYY/MM/DD HH24:MI:SS') > :gsStartTime
AND TO_CHAR(START_TIME, 'YYYY/MM/DD HH24:MI:SS') <= :gsEndTime)))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY LOT_ID,SOURCE_PART_NAME,START_TIME
ORDER BY LOT_ID;单独在运行是没有问题的。
但是放在pro*c下编译时却出了问题,错误提示如下:
Error at line 534, column 28 in file D:\Work\Klarity\custom_attr\custom_attr_translator.pc
LEAD(rn)OVER(PARTITION BY LOT_ID ORDER BY rn) as "rn1"...........................1PCC-S-02201, Encountered the symbol "(" when expecting one of the following: , into, from,注释掉相关错误行后在编译,出现的错误提示如下:
Error at line 539, column 35 in file D:\Work\Klarity\custom_attr\custom_attr_translator.pc
ROW_NUMBER()OVER(ORDER BY PHY_MATERIAL_ID) rn..................................1PCC-S-02201, Encountered the symbol "(" when expecting one of the following: , into, from,
不知该问题该如何解决?谢谢!
SOURCE_PART_NAME,
SUBSTR (MAX (SYS_CONNECT_BY_PATH(PHY_MATERIAL_ID, ',')), 2) PHY_MATERIAL_ID,
START_TIME
FROM (SELECT LOT_ID,
SOURCE_PART_NAME,
PHY_MATERIAL_ID,
rn,
START_TIME,
LEAD(rn)OVER(PARTITION BY LOT_ID ORDER BY rn) as "rn1"
FROM (SELECT lot_id,
SOURCE_PART_NAME,
PHY_MATERIAL_ID,
TO_CHAR(START_TIME, 'YYYY/MM/DD_HH24:MI:SS') START_TIME,
ROW_NUMBER()OVER(ORDER BY PHY_MATERIAL_ID) rn
FROM YMS_TBL_LOT_START_INFO
WHERE (TO_CHAR(START_TIME, 'YYYY/MM/DD HH24:MI:SS') > :gsStartTime
AND TO_CHAR(START_TIME, 'YYYY/MM/DD HH24:MI:SS') <= :gsEndTime)))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY LOT_ID,SOURCE_PART_NAME,START_TIME
ORDER BY LOT_ID;单独在运行是没有问题的。
但是放在pro*c下编译时却出了问题,错误提示如下:
Error at line 534, column 28 in file D:\Work\Klarity\custom_attr\custom_attr_translator.pc
LEAD(rn)OVER(PARTITION BY LOT_ID ORDER BY rn) as "rn1"...........................1PCC-S-02201, Encountered the symbol "(" when expecting one of the following: , into, from,注释掉相关错误行后在编译,出现的错误提示如下:
Error at line 539, column 35 in file D:\Work\Klarity\custom_attr\custom_attr_translator.pc
ROW_NUMBER()OVER(ORDER BY PHY_MATERIAL_ID) rn..................................1PCC-S-02201, Encountered the symbol "(" when expecting one of the following: , into, from,
不知该问题该如何解决?谢谢!
好,我去试试
LEAD(rn)OVER(PARTITION BY LOT_ID ORDER BY rn) as "rn1"
这句的什么位置写INTO吧?可是怎么写?查了一下PRO*C的文档,没有头绪啊!
EXEC SQL SELECT ename, sal
INTO :emp_name, :salary
FROM emp
WHERE (comm = :commission) OR ((comm IS NULL) AND
(:commission INDICATOR :ind_comm IS NULL));
宿主变量引用时加上:
LEAD(:temp_col1)OVER(PARTITION BY LOT_ID ORDER BY :temp_col1) as "rn1"
INTO :temp_col2
原来是编译器有BUG,遇到分析函数时发生错误,用SQL就解决了。