• Post Reply Bookmark Topic Watch Topic
  • New Topic

Need help with this query  RSS feed

 
Susan Monz
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I need help with this query. Can someone tell me whats wrong or even suggest a better way.

SELECT B.DOCNO, B.APPROV_REM
FROM B
WHERE B.DOCNO = ((SELECT A.DOCNO
FROM A
WHERE A.DOCDT=(SELECT MAX(C.DOCDT) FROM C WHERE C.EMPNO = :1)
AND A.EMPNO = C.EMPNO))
Here A and C are the same tables.

Susan
 
Campbell Ritchie
Marshal
Posts: 56534
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please use ctrl-c ctrl-v to post code; I presume the :1 bit is a misprint.

What goes wrong with your query? Apart from using A and C for the same table and not writing "FROM A AS C", which looks very strange indeed, that is?

Have you tried a JOIN on DOCNO? Or even from A, B USING (DOCNO)?

[edit]Add: Please use more informative subjects than "Need help with this query."[/edit]
[ October 07, 2008: Message edited by: Campbell Ritchie ]
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Susan Monz:
Hi,
I need help with this query. Can someone tell me whats wrong or even suggest a better way.

SELECT B.DOCNO, B.APPROV_REM
FROM B
WHERE B.DOCNO = ((SELECT A.DOCNO
FROM A
WHERE A.DOCDT=(SELECT MAX(C.DOCDT) FROM C WHERE C.EMPNO = :1)
AND A.EMPNO = C.EMPNO))
Here A and C are the same tables.

Susan


Without knowing what you're doing... you should make sure your sub-query produces the correct results before adding it to your main query... also.. if A is the same as C... there is no reason to use both A and C (or even for the existence of C) for your query.
[ October 07, 2008: Message edited by: Paul Campbell ]
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!