Union Query returning zero values

[Former Member]

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?

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

The UNION ALL doesn't help.

I actually hadn't tested the stored procedure, but just ran the query in SQL and it worked.

I have now tested the procedure inb oracle and it give the correct results, so there is something about what crystal is doing.

I'm using the Oracle ODBC connection, but I have the Crystal one to test so I'll see how that goes. Thanks for the suggestion.

I don't believe it will be a problem with parameters as it does return some of the columns correctly, it's just the values for Rate and Amount are not always populated.

[Former Member · Accepted solution]

Thanks. Using the Crystal provided Oracle ODBC connection returns the expected results.

[Former Member]

this may be a long shot, but i always thought a union query needed to have the same number of rows and the same column names. i find better results if i do something like this

x.charge_type,

x. tou,

x.uom_cd,

x.quantity,

x.losses,

x.rate,

x.amount

then my select statments in nested with the x label after the )

it may help you

[Former Member]

Hi, As expected this change didn't resolve the problem. Thanks though.