Skip to Main Content
  • Questions

  • Rewrite Query using lots of Union All to use Case statement

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kaushal.

Asked: February 19, 2016 - 1:43 pm UTC

Last updated: February 23, 2016 - 9:52 am UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

Hi ASKTOM Team,

Are the two of the below snippets same semantically:

select sNo,
       pk,
       receipt_no,
       GL_Account_code,
       receipt_date,
       sac_no,
       msr_no,
       sum(Amount) Amount,
       FUND,
       FCTR,
       ba,
       FUNCTIONCODE,
       FUNDCODE,
       CollDate,
       created_at
  from (select     rr.receipt_no,
                 2 as sNo,
                 50 as pk,
                 rr.receipt_date,
                 a.sac_no,
                 sc.msr_no,
                 (case
                   when (crtd.tax_id = 1 and crtd.tax_colln_amt >= 0) then
                    '431100100'
     when (crtd.tax_id = 2 and crtd.tax_colln_amt >= 0) then
      '431190201'
    end) GL_Account_code,
                 (case 
                 when crtd.tax_id = 1 then
   (sum(crtd.tax_colln_amt) * 26 / 30)
                 when crtd.tax_id = 2 then
   sum(crtd.tax_colln_amt)
   end) Amount,
                 CFC.FC_RRLOC_ID FUND,
                 CFC.FC_FUND_CENTER FCTR,
                 CFC.FC_BUSINESS_AREA BA,
                 '99100000000' FUNCTIONCODE,
                 (case 
    when crtd.tax_id = 1 then
    '11'
    when crtd.tax_id = 2 then
    '40'
   end) FUNDCODE,
                 rr.receipt_date as CollDate,
                 rr.created_at
           from cv_receipt_sac_detail sc,
                 cv_receipt_master     rr,
                 cv_assessment         a,
                 cv_fund_collection    cfc,
                 cv_receipt_tax_detail crtd
          where rr.rct_id = sc.rct_id
            and a.cvp_id = sc.cvp_id
            and cfc.fc_rrloc_id = a.ward_id
            and crtd.rct_det_id = sc.rcpt_det_id
            and rr.status = 'A'
            and sc.status = 'A'
            and (a.status = 'A' or a.status = 'O')
            and crtd.status = 'A'
          group by CFC.FC_RRLOC_ID,
                    CFC.FC_FUND_CENTER,
                    rr.created_at,
                    CFC.FC_BUSINESS_AREA,
                    rr.receipt_date,
                    crtd.tax_id,
                    crtd.tax_colln_amt,
                    a.sac_no,
                    sc.msr_no,
                    rr.receipt_no,
                    rr.receipt_date
union all 
         select rr.receipt_no,
                 2 as sNo,
                 50 as pk,
                 rr.receipt_date,
                 a.sac_no,
                 sc.msr_no,
                 (case
                   when (crtd.tax_id = 1 and crtd.tax_colln_amt >= 0) then
                    '431100200'
                 end) GL_Account_code,
                 decode(crtd.tax_id, '1', ((sum(crtd.tax_colln_amt)) * 4 / 30)) Amount,
                 CFC.FC_RRLOC_ID FUND,
                 CFC.FC_FUND_CENTER FCTR,
                 CFC.FC_BUSINESS_AREA BA,
                 '99100000000' FUNCTIONCODE,
                 '11' FUNDCODE,
                 rr.receipt_date as CollDate,
                 rr.created_at
           from cv_receipt_sac_detail sc,
                 cv_receipt_master     rr,
                 cv_assessment         a,
                 cv_fund_collection    cfc,
                 cv_receipt_tax_detail crtd
          where rr.rct_id = sc.rct_id
            and a.cvp_id = sc.cvp_id
            and cfc.fc_rrloc_id = a.ward_id
            and crtd.rct_det_id = sc.rcpt_det_id
            and crtd.tax_id = 1
            and rr.status = 'A'
            and sc.status = 'A'
            and (a.status = 'A' or a.status = 'O')
            and crtd.status = 'A'
          group by CFC.FC_RRLOC_ID,
                    CFC.FC_FUND_CENTER,
                    rr.created_at,
                    CFC.FC_BUSINESS_AREA,
                    rr.receipt_date,
                    crtd.tax_id,
                    crtd.tax_colln_amt,
                    a.sac_no,
                    sc.msr_no,
                    rr.receipt_no,
                    rr.receipt_date)
where amount > 0     
 group by GL_Account_code,
          FUND,
          FCTR,
          FUNCTIONCODE,
          FUNDCODE,
          CollDate,
          sNo,
          pk,
          ba,
          sac_no,
          msr_no,
          receipt_no,
          receipt_date,
          created_at;


And...

select sNo,
       pk,
       receipt_no,
       GL_Account_code,
       receipt_date,
       sac_no,
       msr_no,
       sum(Amount) Amount,
       FUND,
       FCTR,
       ba,
       FUNCTIONCODE,
       FUNDCODE,
       CollDate,
       created_at
  from (select rr.receipt_no,
                 2 as sNo,
                 50 as pk,
                 rr.receipt_date,
                 a.sac_no,
                 sc.msr_no,
                 (case
                   when (crtd.tax_id = 1 and crtd.tax_colln_amt >= 0) then
                    '431100100'
                 end) GL_Account_code,
                 decode(crtd.tax_id, '1', ((sum(crtd.tax_colln_amt)) * 26 / 30)) Amount,
                 CFC.FC_RRLOC_ID FUND,
                 CFC.FC_FUND_CENTER FCTR,
                 CFC.FC_BUSINESS_AREA BA,
                 '99100000000' FUNCTIONCODE,
                 '11' FUNDCODE,
                 rr.receipt_date as CollDate,
                 rr.created_at
           from cv_receipt_sac_detail sc,
                 cv_receipt_master     rr,
                 cv_assessment         a,
                 cv_fund_collection    cfc,
                 cv_receipt_tax_detail crtd
          where rr.rct_id = sc.rct_id
            and a.cvp_id = sc.cvp_id
            and cfc.fc_rrloc_id = a.ward_id
            and crtd.rct_det_id = sc.rcpt_det_id
            and crtd.tax_id = 1
            and rr.status = 'A'
            and sc.status = 'A'
            and (a.status = 'A' or a.status = 'O')
            and crtd.status = 'A'
          group by CFC.FC_RRLOC_ID,
                    CFC.FC_FUND_CENTER,
                    rr.created_at,
                    CFC.FC_BUSINESS_AREA,
                    rr.receipt_date,
                    crtd.tax_id,
                    crtd.tax_colln_amt,
                    a.sac_no,
                    sc.msr_no,
                    rr.receipt_no,
                    rr.receipt_date
union all 
         select rr.receipt_no,
                 2 as sNo,
                 50 as pk,
                 rr.receipt_date,
                 a.sac_no,
                 sc.msr_no,
                 (case
                   when (crtd.tax_id = 1 and crtd.tax_colln_amt >= 0) then
                    '431100200'
                 end) GL_Account_code,
                 decode(crtd.tax_id, '1', ((sum(crtd.tax_colln_amt)) * 4 / 30)) Amount,
                 CFC.FC_RRLOC_ID FUND,
                 CFC.FC_FUND_CENTER FCTR,
                 CFC.FC_BUSINESS_AREA BA,
                 '99100000000' FUNCTIONCODE,
                 '11' FUNDCODE,
                 rr.receipt_date as CollDate,
                 rr.created_at
           from cv_receipt_sac_detail sc,
                 cv_receipt_master     rr,
                 cv_assessment         a,
                 cv_fund_collection    cfc,
                 cv_receipt_tax_detail crtd
          where rr.rct_id = sc.rct_id
            and a.cvp_id = sc.cvp_id
            and cfc.fc_rrloc_id = a.ward_id
            and crtd.rct_det_id = sc.rcpt_det_id
            and crtd.tax_id = 1
            and rr.status = 'A'
            and sc.status = 'A'
            and (a.status = 'A' or a.status = 'O')
            and crtd.status = 'A'
          group by CFC.FC_RRLOC_ID,
                    CFC.FC_FUND_CENTER,
                    rr.created_at,
                    CFC.FC_BUSINESS_AREA,
                    rr.receipt_date,
                    crtd.tax_id,
                    crtd.tax_colln_amt,
                    a.sac_no,
                    sc.msr_no,
                    rr.receipt_no,
                    rr.receipt_date
union all
         select rr.receipt_no,
                 2 as sNo,
                 50 as pk,
                 rr.receipt_date,
                 a.sac_no,
                 sc.msr_no,
                 (case
                   when (crtd.tax_id = 2 and crtd.tax_colln_amt >= 0) then
                    '431190201'
                 end) GL_Account_code,
                 decode(crtd.tax_id, '2', ((sum(crtd.tax_colln_amt)))) Amount,
                 CFC.FC_RRLOC_ID FUND,
                 CFC.FC_FUND_CENTER FCTR,
                 CFC.FC_BUSINESS_AREA BA,
                 '99100000000' FUNCTIONCODE,
                 '40' FUNDCODE,
                 rr.receipt_date as CollDate,
                 rr.created_at
           from cv_receipt_sac_detail sc,
                 cv_receipt_master     rr,
                 cv_assessment         a,
                 cv_fund_collection    cfc,
                 cv_receipt_tax_detail crtd
          where rr.rct_id = sc.rct_id
            and a.cvp_id = sc.cvp_id
            and cfc.fc_rrloc_id = a.ward_id
            and crtd.rct_det_id = sc.rcpt_det_id
            and crtd.tax_id = 2
            and rr.status = 'A'
            and sc.status = 'A'
            and (a.status = 'A' or a.status = 'O')
            and crtd.status = 'A'
          group by CFC.FC_RRLOC_ID,
                    CFC.FC_FUND_CENTER,
                    rr.created_at,
                    CFC.FC_BUSINESS_AREA,
                    rr.receipt_date,
                    crtd.tax_id,
                    crtd.tax_colln_amt,
                    a.sac_no,
                    sc.msr_no,
                    rr.receipt_no,
                    rr.receipt_date)
where amount > 0     
 group by GL_Account_code,
          FUND,
          FCTR,
          FUNCTIONCODE,
          FUNDCODE,
          CollDate,
          sNo,
          pk,
          ba,
          sac_no,
          msr_no,
          receipt_no,
          receipt_date,
          created_at;


1. What happens when there are some NULL crtd.tax_id

2. In a carefully conducted test like this:
create table test (NO number);


insert four different values 1,2,3 and NULL fifty times. Total 200 Rows

select NO from
(select (case when NO =1 then
'YES'
when no = 2 then
'NO'
end) NO
from test
);


Result : 200 Rows with 50 YES, 50 NO and rest NULL

select no from
(select (case when no =1 then
'YES'
end) NO
from test
where no = 1
union all
select (case when no =2 then
'NO'
end) NO
from test
where no = 2
);


Result : 100 Rows with 50 YES and 50 NO

But for the original problem above the results are identical. I am a bit confused over here. IS it because of Amount > 0 ?

3. What are your thoughts about rewriting this kind of queries.

Thanks for your time.

and Chris said...

1. The queries that have crtd.tax_id = :val will exclude the rows where tax_id is null. I don't know enough about your tables or queries to comment for the other queries.

2. Null doesn't equal anything. So in your second query the predicates 

where no = 1

and 
where no = 2


exclude the null values. You have no where clause in the first to do this. The queries are not equivalent. You can't take a query which has predicates against columns which contain nulls, remove them and get the same answer.

So if your question is

"Can I take a case expression on a nullable column and convert it to separate where clauses with equality (=) conditions against it?"

The general answer is NO.

I don't understand how amount > 0 relates to anything? See my answer to 1 regarding not knowing anything about your tables or data.

3. I like to left align the keywords and again on column/table names e.g.:

select col1, 
       col2
from   table
where  col1 = 1
and    col2 = 2;

See how everything lines up? I'd rewrite it to do that.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Converting Union all to Case

Kaushal Ruparel, February 19, 2016 - 3:12 pm UTC

Hi Chris,

Thanks for your time and inputs.

Amount in the main select is sum(amount) from sub-queries and then finally there is a predicate in the main select
 where amount > 0


Actually I am trying to rewrite the code from performance standpoint wherein there are many union all and trying to convert it into case statements. My only doubt is when I convert my problem query (specifically sub-query) to case statement, do I have to include the combined where clause like for eg.:
select A from 
(select 'YES' as A
from test
where col1 =  1
union all
select 'NO' as A
from test
where col1 =  2
)

to...
select A from
(select (case when co11 = 1 then 'YES'
             when col1 = 2 then 'NO'
        end) A
from test
);

or 
select A from
(select (case when co11 = 1 then 'YES'
             when col1 = 2 then 'NO'
        end) A
from test
where col1 in (1, 2)
);

According to me 
 where col1 in (1, 2)

should be included, otherwise the results will be different.

The same is the case with actual problem query in my question, they should return different results but they do not. I am not able to gauge why? Can you throw some light on this?


For my third question, it was not really about alignment or formatting. It was all about performance. What are your expert thoughts on rewriting it from a performance standpoint.

Thanks again
Kaushal Ruparel
Chris Saxon
February 22, 2016 - 4:53 pm UTC

If you're converting multiple union-alls with where clauses into a single select, then yes, you do need to include a where clause like:

where col1 in (1, 2)


I don't know anything about your tables or data other than what you've posted. So it's hard for me to comment on why they're not returning what you expect.

I know you were asking for rewrite from a performance standpoint.

But I don't have enough information to do this. At a minimum you need to provide us with an execution plan, showing Oracle's estimated and actual rows for each step. Definitions of all the tables and their indexes is better. A stripped down test-case we can copy and run locally is best - though I appreciate this isn't always possible.

Thanks for clearing the doubt

Kaushal Ruparel, February 23, 2016 - 8:25 am UTC

Hi Chris,

Thanks for clearing the doubt, without having information on tables and indexes and execution plans and all that. Really Appreciate.

In the course of explaining the problem over here I got the glitch. Actually my Amount column comes from inline view in the problem query, which again is a case statement. Then in the outer query I have a predicate amount > 0 which filters out Null values by implication.

As we say, talking out our problems with others takes us towards a solution, same happened in this case. ASKTOM Team is doing a great job.

Cheers
Kaushal Ruparel.
Chris Saxon
February 23, 2016 - 9:52 am UTC

Thanks, glad to be of help.

Join Factorization in 11GR2

Rajeshwaran Jeyabal, February 23, 2016 - 2:50 pm UTC

Kaushal - When you upgrade to 11gR2, watch out for Join Factorization. that should be useful here.

https://blogs.oracle.com/optimizer/entry/optimizer_transformations_join_factorization
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.