Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    65

    Unanswered: Problem using MAX

    Dear all,

    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:
    TRX_NUMBER
    AMOUNT_DUE_REMAINING

    The other table (Lines) may contain more than one line and has the following columns:
    SERVICE
    GL_DATE
    SERV_DATE

    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:

    Code:
    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.
    Octavio
    Attached Thumbnails Attached Thumbnails Unique.jpg  

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why didn't you MAX(l.service) as well? Besides, you put date columns into GROUP BY clause which *might* cause problems if they aren't truncated to DAY.

    How about
    Code:
      SELECT ps.trx_number,
             MAX(l.service) max_service,
             TRUNC (MAX (l.service_date)) service_date,
             TRUNC (MAX (ps.gl_date)) 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,
             ps.amount_due_remaining

  3. #3
    Join Date
    Jun 2007
    Posts
    65

    Red face

    Dear Littlefoot, it was so obvious!
    I'm angry with myself.
    Thanks a million and sorry for taking your time.
    I would pay you a beer if i could.
    Octavio

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    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.

  5. #5
    Join Date
    Jun 2007
    Posts
    65
    Hi Flyboy.
    You are right, this is a weird request from a user. I explained him that the data will not be accurate, but he doesn't care...
    Thank you for you answer.
    Octavio

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •