-
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
-
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,
-
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
