I'm using SQL to extract a line from three tables from an 11g Oracle database. One of the tables (Headers) contains a single line with columns:
The other table (Lines) may contain more than one line and has the following columns:
I'm not selecting anything from the third table.
As I only want to get one line i'm using the MAX function on the lines table. However, i'm still getting 3 lines instead of one.
Here the SQL i'm using:
SELECT ps.trx_number, l.service,TRUNC(MAX(l.service_date)), TRUNC(MAX(ps.gl_date)), ps.amount_due_remaining
FROM ar_payment_schedules_all ps, inc_doc_headers h, inc_doc_lines l
WHERE ps.trx_number = h.doc_number
AND h.doc_header_id = l.doc_header_id
AND ps.trx_number = 'BIC*7627'
GROUP BY ps.trx_number, l.service_date, ps.gl_date, ps.amount_due_remaining, l.service
Cam someone please let me know a way of getting only a single line?
Any help will be much welcome.
If you do not mind that SERVICE value may come from different row than SERVICE_DATE value...
Anyway, as you did not specify what shall be displayed on that one line, it probably will not matter that those values (SERVICE, SERVICE_DATE) will be taken from different rows of INC_DOC_LINES. Just do not presume that they are stored in a single row.