SQL QUERY
Hi, I am having trouble with the following piece of SQL.
I have 2 Tables: POHEAD and POLINE - THEY SHOULD BE LINKED ON THE ORD_NO field, primary for both. I am trying to identify and return lines on a purchase order where the same catalogue item has been ordered twice.
I want to get the ORD_NO from the POHEAD Table, ORD_NO & ORD_LIN_NO & CATALOGUE fron the POLINE table, where the same catalogue item has been ordered twice on 1 ORD_NO for sysdate.
I have tried a few options below the latest, but this is giving me an error back
SELECT
RSSPOHEAD.ORD_NO,
RSSPOLINE.ORD_NO
FROM
RSSPOHEAD,
RSSPOLINE
WHERE
RSSPOHEAD.ORD_NO=RSSPOLINE
AND
RSSPOLINE.STATUS <> 'Z'
AND
RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD'
HAVING
count(RSSPOLINE.CATALOGUE)
I have 2 Tables: POHEAD and POLINE - THEY SHOULD BE LINKED ON THE ORD_NO field, primary for both. I am trying to identify and return lines on a purchase order where the same catalogue item has been ordered twice.
I want to get the ORD_NO from the POHEAD Table, ORD_NO & ORD_LIN_NO & CATALOGUE fron the POLINE table, where the same catalogue item has been ordered twice on 1 ORD_NO for sysdate.
I have tried a few options below the latest, but this is giving me an error back
SELECT
RSSPOHEAD.ORD_NO,
RSSPOLINE.ORD_NO
FROM
RSSPOHEAD,
RSSPOLINE
WHERE
RSSPOHEAD.ORD_NO=RSSPOLINE
AND
RSSPOLINE.STATUS <> 'Z'
AND
RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD'
HAVING
count(RSSPOLINE.CATALOGUE)
OCDan
Please can you post the eror that it comes up with?
please share the table structure with some sample value.....if i consider RSSPOLINE table have multiple entity for different CATALOGUE having same order id then you can try below sql..........not sure it will work or not...
SELECT RSSPOHEAD.ORD_NO,RSSPOLINE
FROM RSSPOHEAD, RSSPOLINE
WHERE RSSPOHEAD.ORD_NO=RSSPOLINE
AND RSSPOLINE.STATUS <> 'Z'
AND RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD'
GROUP BY RSSPOHEAD.ORD_NO,RSSPOLINE
HAVING cn>1
SELECT RSSPOHEAD.ORD_NO,RSSPOLINE
FROM RSSPOHEAD, RSSPOLINE
WHERE RSSPOHEAD.ORD_NO=RSSPOLINE
AND RSSPOLINE.STATUS <> 'Z'
AND RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD'
GROUP BY RSSPOHEAD.ORD_NO,RSSPOLINE
HAVING cn>1
ASKER
OCDan: it comes back with: 100:SQL ERROR EXECUTING SQL: 3146
ASKER
mehuje: It is returning invaild identifier for cn
SQL> SELECT RSSPOHEAD.ORD_NO,RSSPOLINE
2 FROM RSSPOHEAD, RSSPOLINE
3 WHERE RSSPOHEAD.ORD_NO=RSSPOLINE
4 AND RSSPOLINE.STATUS <> 'Z'
5 AND RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD'
6 GROUP BY RSSPOHEAD.ORD_NO,RSSPOLINE
7 HAVING cn >1;
HAVING cn >1
*
ERROR at line 7:
ORA-00904: "CN": invalid identifier
SQL> SELECT RSSPOHEAD.ORD_NO,RSSPOLINE
2 FROM RSSPOHEAD, RSSPOLINE
3 WHERE RSSPOHEAD.ORD_NO=RSSPOLINE
4 AND RSSPOLINE.STATUS <> 'Z'
5 AND RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD'
6 GROUP BY RSSPOHEAD.ORD_NO,RSSPOLINE
7 HAVING cn >1;
HAVING cn >1
*
ERROR at line 7:
ORA-00904: "CN": invalid identifier
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks mehuje: - that has resolved it
ASKER
thanks