Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Union Query returning zero values

I'm having a weird problem with Crystal. I have a complex query which returns the correct results when run in Oracle SQL developer, but it won't return all the results in Crystal. It returns all rows but has 0 for many of the values.

I have tried to code it as an oracle stored procedure, and have also tried putting it straight into Crystal as a SQL command. Both return the same (incorrect) results.

The problem seems to be with it's use of unions - if I strip out the unions just down to any 1 of the 5 parts, the query returns the expected results (for the part).

I could strip out each query and call them all seperately in sub-reports, but this is not desirable as the rows really should be together and there will be a major performance loss (the union part of query is just a final step of a larger query, to get the results into an easier format for Crystal).

Not that I expect it to help but here is the final part of the query:

SELECT 'Usage' charge_type,
    CASE tou_cd WHEN 'OFF' THEN 'Off Peak'
                WHEN 'ON' THEN 'Peak'
                WHEN 'SH' THEN 'Shoulder'
                ELSE '????' END tou,
    CASE WHEN uom_cd = 'KWH' THEN 'kWh'
         ELSE uom_cd END  uom_cd,
    SUM(CASE WHEN sqi_cd = 'TOT-EC' THEN msr_qty END) quantity,
    SUM(CASE WHEN sqi_cd = 'TOT-LEC' THEN msr_qty END) losses,
    SUM(CASE WHEN sqi_cd = 'TOT-EC' THEN nvl(val,0) ELSE 0 END) rate,
    SUM(CASE WHEN sqi_cd = 'TOT-EC' THEN calc_amt END) amount
FROM results
WHERE sqi_cd IN('TOT-EC','TOT-LEC')
 AND nvl(app_in_summ_sw,'Y') = 'Y'
GROUP BY 'Usage',
   CASE tou_cd WHEN 'OFF' THEN 'Off Peak'
                WHEN 'ON' THEN 'Peak'
                WHEN 'SH' THEN 'Shoulder'
                ELSE '????' END,
    CASE WHEN uom_cd = 'KWH' THEN 'kWh'
         ELSE uom_cd END   
UNION
SELECT 'Other' charge_type,
    'Service Charges' tou,
    sqi_cd uom_cd,
    bill_sq quantity,
    0 losses,
    val rate,
    calc_amt amount
FROM results
WHERE sqi_cd = 'DAYS'
 AND nvl(app_in_summ_sw,'Y') = 'Y'
UNION
SELECT 'Other' charge_type,
    'Market Fee' tou,
    CASE WHEN uom_cd = 'KWH' THEN 'kWh' ELSE uom_cd END uom_cd,
    msr_qty quantity,
    0 losses,
    val rate,
    calc_amt amount
FROM results
WHERE sqi_cd = 'TOT-MF'
 AND nvl(app_in_summ_sw,     'Y') = 'Y'
UNION
SELECT 'Network' charge_type,
    'Maximum Demand' tou,
    CASE WHEN uom_cd = 'KWH' THEN 'kWh' ELSE uom_cd END uom_cd,
    msr_qty quantity,
    0 losses,
    val rate,
    calc_amt amount
FROM results
WHERE sqi_cd = 'MAXDMND'
 AND nvl(app_in_summ_sw,     'Y') = 'Y'
UNION
SELECT 'Network' charge_type,
    CASE tou_cd WHEN 'OFF' THEN 'Off Peak'
                WHEN 'ON' THEN 'Peak'
                WHEN 'SH' THEN 'Shoulder'
                ELSE '????' END tou,
    CASE WHEN uom_cd = 'KWH' THEN 'kWh'
         ELSE uom_cd END  uom_cd,
    msr_qty quantity,
    0 losses,
    val rate,
    calc_amt amount
FROM results
WHERE sqi_cd = 'TOT-NC'
 AND nvl(app_in_summ_sw,     'Y') = 'Y'

Any ideas?

replied

I'm not seeing a syntactical problem with your query.

It looks like all of your unioned sub queries are all mutually exclusive, so you could try using a UNION ALL between each of them. It's somewhat more efficient, since it's not worrying about attempting to match out records in both datasets.

I have no idea if that is going to solve your issue, in fact, I suspect it won't.

What interests me is that you said you wrote it as a stored procedure, and it's working fine in Oracle, but not when run by Crystal. All Crystal does is calls the procedure and passes the required params. In the case of a 'Command', it passes the whole thing to the database, and the database simply returns records.

What kind of connection type are you using in Crystal? Are you using the native Oracle connection type?

Are you passing parameters to the stored procedure through Crystal? If so, ensure that the parameters are being passed in the correct order. (Crystal doesn't care about the names of the parameters). If there is a discrep there, the query may not work as expected. (Right click on the parameter list, and select 'Set Parameter Order' and ensure that the param order is identical to the stored procedure.

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question