• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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