语句1-- 检查ITEM MASTER中的LAST PRICE与PO中的UNIT PRICE不一致的ITEM LIST
SELECT P.ITEM_CODE,P.INVOICE, P.SO_NUMBER, P.INDATE,
P.DESC1, P.UPRICE,I.LAST_PRICE,H.CRT_USER
FROM PO_LN1 P ,PO_HEAD1 H ,UN_ITEM1 I
WHERE P.ITEM_CODE=I.ITEM_CODE
AND H.PO_NUMBER =P.INVOICE
AND P.UPRICE<>I.LAST_PRICE
AND SUBSTRING(P.ITEM_CODE,9,1)='1'
AND P.CRRNCY NOT LIKE 'RMB'
AND P.CRRNCY NOT LIKE 'USD'
ORDER BY P.ITEM_CODE
语句2-- 检查ITEM MASTER中的LAST PRICE与COST SHEET 中的UNIT PRICE不一致的ITEM LIST
SELECT C.PRODUCT_CODE, C.ITEM_CODE, C.UPRICE, I.LAST_PRICE ,I.LST_USER,I.CRT_DATE
FROM CS_METAL C JOIN UN_ITEM1 I ON C.ITEM_CODE=I.ITEM_CODE
WHERE C.UPRICE<>I.LAST_PRICE
AND SUBSTRING(C.PRODUCT_CODE,9,1)='1'
ORDER BY C.PRODUCT_CODE即实现,只要在一个表里实现以上查询的结果?? 急!!!
我是这样写的,但查询结果有点乱,也不知我的为什么不行?错在哪里??(--当前一段语句作为一个临时结果表再与后者相内连接...为何不可?
select te.ITEM_CODE,C.PRODUCT_CODE,te.INVOICE AS PO_NO, te.SO_NUMBER, te.INDATE AS PO_LASTUPDATE,
te.DESC1, te.UPRICE AS PO_UPRICE,te.LAST_PRICE,C.UPRICE AS COSTSHEETUPRICE,te.CRT_USER AS PO_CREATEUSER ,te.CRT_DATE AS ITEM_LASTUPDATE
from (SELECT P.ITEM_CODE,P.INVOICE, P.SO_NUMBER, P.INDATE,
P.DESC1, P.UPRICE,I.LAST_PRICE,H.CRT_USER,I.CRT_DATE
FROM PO_LN1 P ,PO_HEAD1 H ,UN_ITEM1 I
WHERE P.ITEM_CODE=I.ITEM_CODE
AND H.PO_NUMBER =P.INVOICE
AND SUBSTRING(P.ITEM_CODE,9,1)='1'
AND P.CRRNCY NOT LIKE 'RMB'
AND P.CRRNCY NOT LIKE 'USD'
) as teinner join CS_METAL C --当前一段语句作为一个临时结果表再与后者相内连接...
ON C.ITEM_CODE=te.ITEM_CODE
and (C.UPRICE<>te.UPRICE or te.UPRICE<>te.LAST_PRICE or C.UPRICE<>te.LAST_PRICE )
and SUBSTRING(C.PRODUCT_CODE,9,1)='1'
ORDER BY C.ITEM_CODE,C.PRODUCT_CODE, te.INVOICE
SELECT P.ITEM_CODE,P.INVOICE, P.SO_NUMBER, P.INDATE,
P.DESC1, P.UPRICE,I.LAST_PRICE,H.CRT_USER
FROM PO_LN1 P ,PO_HEAD1 H ,UN_ITEM1 I
WHERE P.ITEM_CODE=I.ITEM_CODE
AND H.PO_NUMBER =P.INVOICE
AND P.UPRICE<>I.LAST_PRICE
AND SUBSTRING(P.ITEM_CODE,9,1)='1'
AND P.CRRNCY NOT LIKE 'RMB'
AND P.CRRNCY NOT LIKE 'USD'
ORDER BY P.ITEM_CODE
语句2-- 检查ITEM MASTER中的LAST PRICE与COST SHEET 中的UNIT PRICE不一致的ITEM LIST
SELECT C.PRODUCT_CODE, C.ITEM_CODE, C.UPRICE, I.LAST_PRICE ,I.LST_USER,I.CRT_DATE
FROM CS_METAL C JOIN UN_ITEM1 I ON C.ITEM_CODE=I.ITEM_CODE
WHERE C.UPRICE<>I.LAST_PRICE
AND SUBSTRING(C.PRODUCT_CODE,9,1)='1'
ORDER BY C.PRODUCT_CODE即实现,只要在一个表里实现以上查询的结果?? 急!!!
我是这样写的,但查询结果有点乱,也不知我的为什么不行?错在哪里??(--当前一段语句作为一个临时结果表再与后者相内连接...为何不可?
select te.ITEM_CODE,C.PRODUCT_CODE,te.INVOICE AS PO_NO, te.SO_NUMBER, te.INDATE AS PO_LASTUPDATE,
te.DESC1, te.UPRICE AS PO_UPRICE,te.LAST_PRICE,C.UPRICE AS COSTSHEETUPRICE,te.CRT_USER AS PO_CREATEUSER ,te.CRT_DATE AS ITEM_LASTUPDATE
from (SELECT P.ITEM_CODE,P.INVOICE, P.SO_NUMBER, P.INDATE,
P.DESC1, P.UPRICE,I.LAST_PRICE,H.CRT_USER,I.CRT_DATE
FROM PO_LN1 P ,PO_HEAD1 H ,UN_ITEM1 I
WHERE P.ITEM_CODE=I.ITEM_CODE
AND H.PO_NUMBER =P.INVOICE
AND SUBSTRING(P.ITEM_CODE,9,1)='1'
AND P.CRRNCY NOT LIKE 'RMB'
AND P.CRRNCY NOT LIKE 'USD'
) as teinner join CS_METAL C --当前一段语句作为一个临时结果表再与后者相内连接...
ON C.ITEM_CODE=te.ITEM_CODE
and (C.UPRICE<>te.UPRICE or te.UPRICE<>te.LAST_PRICE or C.UPRICE<>te.LAST_PRICE )
and SUBSTRING(C.PRODUCT_CODE,9,1)='1'
ORDER BY C.ITEM_CODE,C.PRODUCT_CODE, te.INVOICE
它们共同关联一个UN_ITEM1 表,都要找一个共同的字段I.ITEM_CODE
我想应该可以实现吧!!/高手请出招,谢谢
SELECT
P.ITEM_CODE,P.INVOICE, P.SO_NUMBER, P.INDATE,
P.DESC1, P.UPRICE,I.LAST_PRICE,H.CRT_USER,
C.PRODUCT_CODE, C.ITEM_CODE, C.UPRICE
FROM PO_LN1 P ,PO_HEAD1 H ,UN_ITEM1 I ,CS_METAL C
WHERE P.ITEM_CODE=I.ITEM_CODE and C.ITEM_CODE=I.ITEM_CODE
AND H.PO_NUMBER =P.INVOICE
AND P.UPRICE<>I.LAST_PRICE
AND SUBSTRING(P.ITEM_CODE,9,1)='1'
AND P.CRRNCY NOT LIKE 'RMB'
AND P.CRRNCY NOT LIKE 'USD'
AND C.UPRICE<>I.LAST_PRICE
AND SUBSTRING(C.PRODUCT_CODE,9,1)='1'
ORDER BY P.ITEM_CODE一个连接查询不行吗?
(C.UPRICE<>te.UPRICE or te.UPRICE<>te.LAST_PRICE or C.UPRICE<>te.LAST_PRICE )
SELECT
P.ITEM_CODE,P.INVOICE, P.SO_NUMBER, P.INDATE,
P.DESC1, P.UPRICE,I.LAST_PRICE,H.CRT_USER,
C.PRODUCT_CODE, C.ITEM_CODE, C.UPRICE
FROM PO_LN1 P ,PO_HEAD1 H ,UN_ITEM1 I ,CS_METAL C
WHERE
((P.ITEM_CODE=I.ITEM_CODE and AND H.PO_NUMBER =P.INVOICE)
or (C.ITEM_CODE=I.ITEM_CODE))AND P.UPRICE<>I.LAST_PRICE
AND SUBSTRING(P.ITEM_CODE,9,1)='1'
AND P.CRRNCY NOT LIKE 'RMB'
AND P.CRRNCY NOT LIKE 'USD'
AND C.UPRICE<>I.LAST_PRICE
AND SUBSTRING(C.PRODUCT_CODE,9,1)='1'
ORDER BY P.ITEM_CODE??这样?看不到数据,不知道你想要什么样的结果。
你第二段代码与我的其中一次想法如出一辙;SELECT P.ITEM_CODE,P.INVOICE, P.SO_NUMBER, P.INDATE, P.DESC1, P.UPRICE as pouprice ,I.LAST_PRICE,H.CRT_USER ,C.PRODUCT_CODE, C.UPRICE as costUprice, I.LST_USER,I.CRT_DATE
FROM PO_LN1 P ,PO_HEAD1 H ,UN_ITEM1 I ,CS_METAL C
WHERE P.ITEM_CODE = I.ITEM_CODE And C.ITEM_CODE = I.ITEM_CODE AND H.PO_NUMBER =P.INVOICE
AND (P.UPRICE<>I.LAST_PRICE OR C.UPRICE<>I.LAST_PRICE OR C.UPRICE<>P.UPRICE )
AND SUBSTRING(P.ITEM_CODE,9,1)='1'
AND P.CRRNCY NOT LIKE 'RMB'
AND P.CRRNCY NOT LIKE 'USD'
AND SUBSTRING(C.PRODUCT_CODE,9,1)='1'
ORDER BY P.ITEM_CODE,C.PRODUCT_CODE, P.INVOICE结果也没有对???不知道是不是我检查出错了....(我不知道怎么才能放我检查的附件
希望:只要满足下列条件之一,就要显示出来
1 ITEM MASTER中的LAST PRICE与COST SHEET 中的UNIT PRICE(单价)不一致
2 ITEM MASTER中的LAST PRICE与PO中的UNIT PRICE(单价)不一致的
1 ITEM MASTER中的LAST PRICE与COST SHEET 中的UNIT PRICE(单价)不一致
2 ITEM MASTER中的LAST PRICE与PO中的UNIT PRICE(单价)不一致的