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

Oracle Query Help

I have a query below...each table has the common element of Document_id, however the LINE_ITEMS_TABLE can have multiple rows in it.  As such my results set is coming back larger than expected.  How can have the query just pull one row from the LINE_ITEMS_TABLE?

SELECT <LIST Of VALUES>
FROM WORKFLOW_TABLE A,
  HEADER_TABLE B,
  LINE_ITEMS_TABLE C
WHERE A.QUEUE = 1
AND A.USER = 'MSMITH'
AND B.ORGID     = '123'
AND A.DOCUMENT_ID = B.DOCUMENT_ID
AND C.COMPANY    = '01'
AND C.DOCUMENT_ID  = A.DOCUMENT_ID
0
jmpatton
Asked:
jmpatton
  • 2
  • 2
1 Solution
 
DavidSenior Oracle Database AdministratorCommented:
try adding AND ROWNUM = 1
0
 
jmpattonAuthor Commented:
I tried that and it only returned 1 row over all.  What I currently have in the data base is a row in the header table, a row in the workflow table, and then two rows in the line_items_table.

When I join them all up like below, it returns two records because there are two records in the line items table.  In a live setting there will be hundreds of header records each with a corresponding workflow record and multiple line items for each.

SELECT A.INVOICE_NUMBER, B.WORKFLOW_QUEUE, C.COMPANY FROM WORKFLOW_TABLE A,
  HEADER_TABLE B,
  LINE_ITEMS_TABLE C
WHERE A.QUEUE = 1
AND A.USER = 'MSMITH'
AND B.ORGID     = '123'
AND A.DOCUMENT_ID = B.DOCUMENT_ID
AND C.COMPANY    = '01'
AND C.DOCUMENT_ID  = A.DOCUMENT_ID
0
 
jmpattonAuthor Commented:
I think I figured it out.  The document id in the header table was duplicated.

Thanks for your help
0
 
DavidSenior Oracle Database AdministratorCommented:
Best kind of solution, where there is effort and learning.  You're welcome.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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