• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

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.ORD_NO
AND
RSSPOLINE.STATUS <> 'Z'
 AND
RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD')
HAVING
      count(RSSPOLINE.CATALOGUE) <>count (distinct RSSPOLINE.CATALOGUE)

0
sochionnaitj
Asked:
sochionnaitj
  • 4
  • 2
1 Solution
 
OCDanCommented:
Please can you post the eror that it comes up with?
0
 
mehujeCommented:
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.ORD_NO, count(RSSPOLINE.CATALOGUE)  cn    
FROM RSSPOHEAD, RSSPOLINE
WHERE RSSPOHEAD.ORD_NO=RSSPOLINE.ORD_NO
AND RSSPOLINE.STATUS <> 'Z'
AND RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD')
GROUP BY RSSPOHEAD.ORD_NO,RSSPOLINE.ORD_NO, RSSPOLINE.CATALOGUE
HAVING  cn>1
0
 
sochionnaitjAuthor Commented:
OCDan: it comes back with: 100:SQL ERROR EXECUTING SQL: 3146
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sochionnaitjAuthor Commented:
mehuje: It is returning invaild identifier for cn

SQL> SELECT  RSSPOHEAD.ORD_NO,RSSPOLINE.ORD_NO, count(RSSPOLINE.CATALOGUE) cn
  2  FROM RSSPOHEAD, RSSPOLINE
  3  WHERE RSSPOHEAD.ORD_NO=RSSPOLINE.ORD_NO
  4  AND RSSPOLINE.STATUS <> 'Z'
  5  AND RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD')
  6  GROUP BY RSSPOHEAD.ORD_NO,RSSPOLINE.ORD_NO, RSSPOLINE.CATALOGUE
  7  HAVING cn >1;
HAVING cn >1
       *
ERROR at line 7:
ORA-00904: "CN": invalid identifier
0
 
mehujeCommented:
then try this...

SELECT  RSSPOHEAD.ORD_NO,RSSPOLINE.ORD_NO, count(RSSPOLINE.CATALOGUE)
 FROM RSSPOHEAD, RSSPOLINE
 WHERE RSSPOHEAD.ORD_NO=RSSPOLINE.ORD_NO
 AND RSSPOLINE.STATUS <> 'Z'
 AND RSSPOHEAD.ORD_DATE = to_char(sysdate,'YYYYMMDD')
 GROUP BY RSSPOHEAD.ORD_NO,RSSPOLINE.ORD_NO, RSSPOLINE.CATALOGUE
  HAVING count(RSSPOLINE.CATALOGUE) >1
0
 
sochionnaitjAuthor Commented:
Thanks mehuje: - that has resolved it
0
 
sochionnaitjAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now