1. 运行的时候报错ORA-00904:"TD"."ITEM":invalid identifier (在第二个EXISTS那段里面的A2前面那个TD报错)
2. 去掉AND EXISTS那段,一切运行正常(在第一个NOT EXISTS里面也有TD.ITEM,但是为什么就不报错呢?)
3. 把TD.ITEM直接改成TRAN_DATA.ITEM,照样报错SELECT TD.ITEM,
TD.LOCATION,
TD.TRAN_CODE,
TD.REF_NO_1,
TD.REF_NO_2,
COUNT(*)
FROM TRAN_DATA TD
WHERE TD.TRAN_CODE IN (30, 32)
AND TD.REF_NO_1 IS NOT NULL
AND TD.REF_NO_2 IS NOT NULL
AND (TD.ADJ_CODE IS NULL OR TD.ADJ_CODE <> 'U')
AND NOT EXISTS (SELECT 1
FROM ITEM_MASTER ITM
WHERE ITM.DEPOSIT_ITEM_TYPE = 'A'
AND ITM.ITEM = TD.ITEM)
AND EXISTS (SELECT A1.NUM
FROM (SELECT COUNT(CARTON) NUM
FROM (SELECT DISTINCT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A1,
(SELECT COUNT(CARTON) NUM
FROM (SELECT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A2
WHERE A1.NUM <> A2.NUM) GROUP BY ITEM, LOCATION, TRAN_CODE, REF_NO_1, REF_NO_2
HAVING COUNT(*) > 1
2. 去掉AND EXISTS那段,一切运行正常(在第一个NOT EXISTS里面也有TD.ITEM,但是为什么就不报错呢?)
3. 把TD.ITEM直接改成TRAN_DATA.ITEM,照样报错SELECT TD.ITEM,
TD.LOCATION,
TD.TRAN_CODE,
TD.REF_NO_1,
TD.REF_NO_2,
COUNT(*)
FROM TRAN_DATA TD
WHERE TD.TRAN_CODE IN (30, 32)
AND TD.REF_NO_1 IS NOT NULL
AND TD.REF_NO_2 IS NOT NULL
AND (TD.ADJ_CODE IS NULL OR TD.ADJ_CODE <> 'U')
AND NOT EXISTS (SELECT 1
FROM ITEM_MASTER ITM
WHERE ITM.DEPOSIT_ITEM_TYPE = 'A'
AND ITM.ITEM = TD.ITEM)
AND EXISTS (SELECT A1.NUM
FROM (SELECT COUNT(CARTON) NUM
FROM (SELECT DISTINCT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A1,
(SELECT COUNT(CARTON) NUM
FROM (SELECT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A2
WHERE A1.NUM <> A2.NUM) GROUP BY ITEM, LOCATION, TRAN_CODE, REF_NO_1, REF_NO_2
HAVING COUNT(*) > 1
好象是两层嵌套就找不到了 TD.REF_NO_2,
COUNT(*)
FROM TRAN_DATA TD
WHERE TD.TRAN_CODE IN (30, 32)
AND TD.REF_NO_1 IS NOT NULL
AND TD.REF_NO_2 IS NOT NULL
AND (TD.ADJ_CODE IS NULL OR TD.ADJ_CODE <> 'U')
AND NOT EXISTS (SELECT 1
FROM ITEM_MASTER ITM
WHERE ITM.DEPOSIT_ITEM_TYPE = 'A'
AND ITM.ITEM = TD.ITEM)
AND EXISTS (SELECT A1.NUM
FROM (SELECT COUNT(CARTON) NUM
FROM (SELECT DISTINCT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A1,
(SELECT COUNT(CARTON) NUM
FROM (SELECT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A2
WHERE A1.NUM <> A2.NUM)
SELECT TD.ITEM,
TD.LOCATION,
TD.TRAN_CODE,
TD.REF_NO_1,
TD.REF_NO_2,
COUNT(*)
FROM TRAN_DATA TD
WHERE TD.TRAN_CODE IN (30, 32)
AND TD.REF_NO_1 IS NOT NULL
AND TD.REF_NO_2 IS NOT NULL
AND (TD.ADJ_CODE IS NULL OR TD.ADJ_CODE <> 'U')
AND NOT EXISTS (SELECT 1
FROM ITEM_MASTER ITM
WHERE ITM.DEPOSIT_ITEM_TYPE = 'A'
AND ITM.ITEM = TD.ITEM)
AND EXISTS
(SELECT 1
FROM (SELECT COUNT(DISTINCT CARTON) "NUM1", COUNT(CARTON) "NUM2"
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM) AA
WHERE NUM1 <> NUM2)
GROUP BY ITEM, LOCATION, TRAN_CODE, REF_NO_1, REF_NO_2
HAVING COUNT(*) > 1;
感谢帮忙精简,不过还是报同样的错误
如果是嵌套的问题,那我把TD改成直接表名还是报错的,很奇怪
(SELECT TD.ITEM t1,
TD.LOCATION t2,
TD.TRAN_CODE t3,
TD.REF_NO_1 t4,
TD.REF_NO_2 t5,
COUNT(*) t6
FROM TRAN_DATA TD
WHERE TD.TRAN_CODE IN (30, 32)
AND TD.REF_NO_1 IS NOT NULL
AND TD.REF_NO_2 IS NOT NULL
AND (TD.ADJ_CODE IS NULL OR TD.ADJ_CODE <> 'U')
AND NOT EXISTS (SELECT 1
FROM ITEM_MASTER ITM
WHERE ITM.DEPOSIT_ITEM_TYPE = 'A'
AND ITM.ITEM = TD.ITEM)) tab1,
(SELECT A1.NUM tt1
FROM (SELECT COUNT(CARTON) NUM
FROM (SELECT DISTINCT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A1,
(SELECT COUNT(CARTON) NUM
FROM (SELECT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A2
WHERE A1.NUM <> A2.NUM)) tab2 GROUP BY tab1.t1, tab1.t2, tab1.t3, tab1.t4,tab1.t5
HAVING tab1.t6 > 1
感谢
不过,我注释掉COUNT、GROUP、HAVING,还是会报同样的错
SELECT TD.ITEM t1,
TD.LOCATION t2,
TD.TRAN_CODE t3,
TD.REF_NO_1 t4,
TD.REF_NO_2 t5,
COUNT(*) t6
FROM TRAN_DATA TD
WHERE TD.TRAN_CODE IN (30, 32)
AND TD.REF_NO_1 IS NOT NULL
AND TD.REF_NO_2 IS NOT NULL
AND (TD.ADJ_CODE IS NULL OR TD.ADJ_CODE <> 'U')
AND NOT EXISTS (SELECT 1
FROM ITEM_MASTER ITM
WHERE ITM.DEPOSIT_ITEM_TYPE = 'A'
AND ITM.ITEM = TD.ITEM);第二段:
SELECT A1.NUM tt1
FROM (SELECT COUNT(CARTON) NUM
FROM (SELECT DISTINCT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A1,
(SELECT COUNT(CARTON) NUM
FROM (SELECT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM)) A2
WHERE A1.NUM <> A2.NUM;这两段都正确吗?
WHERE A1.NUM <> A2.NUM)) tab2 是不是多了个括号,就应是:WHERE A1.NUM <> A2.NUM) tab2
分开执行,第一段可以执行成功(注释掉COUNT,因为没有GROUP了)
第二段还是报错,即使换掉TD为TRAN_DATA表名,还是报错TRAN_DATA.ITEM……invalid identifier
SELECT A1.NUM tt1
FROM (SELECT COUNT(k1.CARTON) NUM
FROM (SELECT DISTINCT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM) k1 ) A1,
(SELECT COUNT(k2.CARTON) NUM
FROM (SELECT CARTON
FROM SHIPSKU SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM) k2 ) A2
WHERE A1.NUM <> A2.NUM;
SELECT TD.ITEM,
TD.LOCATION,
TD.TRAN_CODE,
TD.REF_NO_1,
TD.REF_NO_2,
COUNT(*)
FROM TRAN_DATA TD
WHERE TD.TRAN_CODE IN (30, 32)
AND TD.REF_NO_1 IS NOT NULL
AND TD.REF_NO_2 IS NOT NULL
AND (TD.ADJ_CODE IS NULL OR TD.ADJ_CODE <> 'U')
AND NOT EXISTS (SELECT 1
FROM ITEM_MASTER ITM
WHERE ITM.DEPOSIT_ITEM_TYPE = 'A'
AND ITM.ITEM = TD.ITEM)
AND EXISTS (SELECT 1
FROM (SELECT DISTRO_NO,
ITEM,
COUNT(DISTINCT CARTON) "NUM1",
COUNT(CARTON) "NUM2"
FROM SHIPSKU
GROUP BY DISTRO_NO, ITEM) SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM
AND NUM1 <> NUM2)
GROUP BY ITEM, LOCATION, TRAN_CODE, REF_NO_1, REF_NO_2
HAVING COUNT(*) > 1;
SELECT A1.NUM tt1
FROM (SELECT COUNT(k1.kk1) NUM
FROM (SELECT DISTINCT CARTON kk1
FROM SHIPSKU SS,TRAN_DATA TD
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM) k1 ) A1,
(SELECT COUNT(k2.kk2) NUM
FROM (SELECT CARTON kk2
FROM SHIPSKU SS,TRAN_DATA TD
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM) k2 ) A2
WHERE A1.NUM <> A2.NUM;
可以了,不报错了,16F的代码可以执行。
能说说原因么?很困惑。10分钟后结贴。感谢给予过帮助的每个人
SELECT 1
FROM (SELECT DISTRO_NO,
ITEM,
COUNT(DISTINCT CARTON) "NUM1",
COUNT(CARTON) "NUM2"
FROM SHIPSKU
GROUP BY DISTRO_NO, ITEM) SS
WHERE SS.DISTRO_NO = TD.REF_NO_1
AND SS.ITEM = TD.ITEM
AND NUM1 <> NUM2
-- 上面的SQL是先在子查询中求出所有COUNT[根据DISTRO_NO, ITEM分组],然后再在最外层与TD表的字段关连,这样就不会出现在嵌套层中找不TD字段的错误了。Hope it will helpful for your analysis!