DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQLPLUS report question

  1. #1
    Join Date
    Dec 1999
    Posts
    217
    I am trying to create a report in SQLPLUS using the functions "break on" and "compute". But I am not getting the desired results. The SQL is as follows:
    BREAK ON SERVICE_OID skip 2
    COMPUTE SUM OF FLAT_RATE ON SERVICE_OID
    SELECT substr(SERVICE_OID, 1, 25), substr(NVL(FLAT_RATE, 0), 1, 10)
    FROM CONTRACT_SERVICE
    WHERE SERVICE_OID IN ('S095', 'S096', 'S097', 'S098')
    ORDER BY SERVICE_OID

    the results are being shown as follows:

    SUBSTR(SERVICE_OID,1,25) SUBSTR(NVL
    ------------------------- ----------
    S095 1500
    S095 2000
    S096 0
    S097 0
    S097 0
    S097 0
    S097 0
    S097 0
    S098 0
    S098 0
    S098 0
    S098 0

    What is it that I am missing in my statement? I appreciate the help.

    Thanks,
    Chintz





  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You don't have a SERVICE_OID in your resultset because you've applied a function to it. So SQL*Plus can perform a BREAK and a COMPUTE on SERVICE_OID as there is no such column there. You should add an alias SERVICE_OID into your select list and you'll be fine:

    SELECT substr(SERVICE_OID, 1, 25) SERVICE_OID, substr(NVL(FLAT_RATE, 0), 1, 10)
    FROM CONTRACT_SERVICE .....

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 1999
    Posts
    217
    Thanks that did fix the issue with adding the break on in the report. The other issue I am having the report is not cumputing the sum. I used SERVICE_OID & FLAT RATE as aliases for the columns but it does not display the sum of the total service_oids.

    Chintz

Posting Permissions

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


Click Here to Expand Forum to Full Width