Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I have the following SQL query I am trying to use (save/make easy future modifications to) that is essentially the same query over and over and over, UNIONed each time, with only minor changes to the WHERE clause.

I am more familiar with MS Access, which allows me to use an IIf statement in the SELECT clause to accomplish the same, but I was not sure how to do this with Oracle, so I came up with this mess instead.

Since there may be future changes in the query, such as adding or removing items from the common.jurisdiction_td.state_cd pull, I want to be able to reduce the overall size/repeteiveness of the query so making those changes is easier. Other values like the dates are more flexible, so I've got those plugging in as user-specified variables already. It's the more static criteria that I'm concerned about, since I don't want to have the end-user putting in the same variable for those fields 10,000 times before the next potential change.

Is there a way I can simplify this so that either the UNIONs are reduced and/or the sub-queries are smaller, more compact, and better able to be manipulated?

I had also considered SELECTing my needed fields in the top-level query, but the DB is very large and each individual step takes a few minutes to run as-is. I was worried that doing SELECT *, '<variable field value>' in the sub-queries would make the process run far too long. (I have not tested this method, but I have assumed it is no good. Please correct me if that's wrong!)

Also, I feel like the use of () is a bit overboard, but this was produced by a query builder, and I'm presenting the output of that here.

SELECT Count(DISTINCT billrevw.bill_hdr.icn),
       billrevw.bill_hdr.bill_orgn_id,
       billrevw.bill_hdr.type_of_bill,
       billrevw.bill_hdr.bill_type_cd,
       '<1000' AS TBC
FROM   common.jurisdiction_td,
       billrevw.bill_hdr
WHERE  ( ( billrevw.bill_hdr.icn LIKE '%00' )
         AND ((( ( billrevw.bill_hdr.bill_orgn_id LIKE 'GAL%' )
                  OR ( billrevw.bill_hdr.bill_orgn_id LIKE 'GSA%' )
                  OR ( billrevw.bill_hdr.bill_orgn_id LIKE 'GEE%' ) )))
         AND ( billrevw.bill_hdr.fnlzd_paid_posting_dt BETWEEN
                   '01-May-2012 0:00:00' AND '31-Jul-2012 0:00:00' )
         AND ( billrevw.bill_hdr.bill_type_cd <> '6' )
         AND ( common.jurisdiction_td.state_cd NOT IN (
               '139', '199', '219', '239' ) )
       )
       AND (( ( billrevw.bill_hdr.rptng_exclusn_cd NOT IN ( 'MOD', 'NFS' ) )
               OR ( billrevw.bill_hdr.rptng_exclusn_cd IS NULL ) ))
       AND ( billrevw.bill_hdr.ttl_chrg BETWEEN 0 AND 999.99 )
       AND billrevw.bill_hdr.dw_jrsdctn_row_id =
           common.jurisdiction_td.dw_jrsdctn_row_id
           (
        +
    )
GROUP  BY billrevw.bill_hdr.bill_orgn_id,
          billrevw.bill_hdr.type_of_bill,
          billrevw.bill_hdr.bill_type_cd
UNION
SELECT Count(DISTINCT billrevw.bill_hdr.icn),
       billrevw.bill_hdr.bill_orgn_id,
       billrevw.bill_hdr.type_of_bill,
       billrevw.bill_hdr.bill_type_cd,
       '1000-5000' AS TBC
FROM   common.jurisdiction_td,
       billrevw.bill_hdr
WHERE  ( ( billrevw.bill_hdr.icn LIKE '%00' )
         AND ((( ( billrevw.bill_hdr.bill_orgn_id LIKE 'GAL%' )
                  OR ( billrevw.bill_hdr.bill_orgn_id LIKE 'GSA%' )
                  OR ( billrevw.bill_hdr.bill_orgn_id LIKE 'GEE%' ) )))
         AND ( billrevw.bill_hdr.fnlzd_paid_posting_dt BETWEEN
                   '01-May-2012 0:00:00' AND '31-Jul-2012 0:00:00' )
         AND ( billrevw.bill_hdr.bill_type_cd <> '6' )
         AND ( common.jurisdiction_td.state_cd NOT IN (
               '139', '199', '219', '239' ) )
       )
       AND (( ( billrevw.bill_hdr.rptng_exclusn_cd NOT IN ( 'MOD', 'NFS' ) )
               OR ( billrevw.bill_hdr.rptng_exclusn_cd IS NULL ) ))
       AND ( billrevw.bill_hdr.ttl_chrg BETWEEN 1000 AND 4999.99 )
       AND billrevw.bill_hdr.dw_jrsdctn_row_id =
           common.jurisdiction_td.dw_jrsdctn_row_id
           (
        +
    )
GROUP  BY billrevw.bill_hdr.bill_orgn_id,
          billrevw.bill_hdr.type_of_bill,
          billrevw.bill_hdr.bill_type_cd
UNION
SELECT Count(DISTINCT billrevw.bill_hdr.icn),
       billrevw.bill_hdr.bill_orgn_id,
       billrevw.bill_hdr.type_of_bill,
       billrevw.bill_hdr.bill_type_cd,
       '5000-10000' AS TBC
FROM   common.jurisdiction_td,
       billrevw.bill_hdr
WHERE  ( ( billrevw.bill_hdr.icn LIKE '%00' )
         AND ((( ( billrevw.bill_hdr.bill_orgn_id LIKE 'GAL%' )
                  OR ( billrevw.bill_hdr.bill_orgn_id LIKE 'GSA%' )
                  OR ( billrevw.bill_hdr.bill_orgn_id LIKE 'GEE%' ) )))
         AND ( billrevw.bill_hdr.fnlzd_paid_posting_dt BETWEEN
                   '01-May-2012 0:00:00' AND '31-Jul-2012 0:00:00' )
         AND ( billrevw.bill_hdr.bill_type_cd <> '6' )
         AND ( common.jurisdiction_td.state_cd NOT IN (
               '139', '199', '219', '239' ) )
       )
       AND (( ( billrevw.bill_hdr.rptng_exclusn_cd NOT IN ( 'MOD', 'NFS' ) )
               OR ( billrevw.bill_hdr.rptng_exclusn_cd IS NULL ) ))
       AND ( billrevw.bill_hdr.ttl_chrg BETWEEN 5000 AND 9999.99 )
       AND billrevw.bill_hdr.dw_jrsdctn_row_id =
           common.jurisdiction_td.dw_jrsdctn_row_id
           (
        +
    )
GROUP  BY billrevw.bill_hdr.bill_orgn_id,
          billrevw.bill_hdr.type_of_bill,
          billrevw.bill_hdr.bill_type_cd
UNION
SELECT Count(DISTINCT billrevw.bill_hdr.icn),
       billrevw.bill_hdr.bill_orgn_id,
       billrevw.bill_hdr.type_of_bill,
       billrevw.bill_hdr.bill_type_cd,
       '10000-25000' AS TBC
FROM   common.jurisdiction_td,
       billrevw.bill_hdr
WHERE  ( ( billrevw.bill_hdr.icn LIKE '%00' )
         AND ((( ( billrevw.bill_hdr.bill_orgn_id LIKE 'GAL%' )
                  OR ( billrevw.bill_hdr.bill_orgn_id LIKE 'GSA%' )
                  OR ( billrevw.bill_hdr.bill_orgn_id LIKE 'GEE%' ) )))
         AND ( billrevw.bill_hdr.fnlzd_paid_posting_dt BETWEEN
                   '01-May-2012 0:00:00' AND '31-Jul-2012 0:00:00' )
         AND ( billrevw.bill_hdr.bill_type_cd <> '6' )
         AND ( common.jurisdiction_td.state_cd NOT IN (
               '139', '199', '219', '239' ) )
       )
       AND (( ( billrevw.bill_hdr.rptng_exclusn_cd NOT IN ( 'MOD', 'NFS' ) )
               OR ( billrevw.bill_hdr.rptng_exclusn_cd IS NULL ) ))
       AND ( billrevw.bill_hdr.ttl_chrg BETWEEN 10000 AND 24999.99 )
       AND billrevw.bill_hdr.dw_jrsdctn_row_id =
           common.jurisdiction_td.dw_jrsdctn_row_id
           (
        +
    )
GROUP  BY billrevw.bill_hdr.bill_orgn_id,
          billrevw.bill_hdr.type_of_bill,
          billrevw.bill_hdr.bill_type_cd; 
share|improve this question
add comment

1 Answer 1

first of all you must avoid using "or" in where clause.

second, I understand that your sql code is not full, but still I think that you can get rid of all unions and use case/decode instead.

third, I would suggest not to use tables in "from" which fields not used in "select" use in/exists instead.

consider query I've wrote

Select Count(Distinct Icn), t.*
  From (Select Bh.Icn
              ,Bh.Bill_Orgn_Id
              ,Bh.Type_Of_Bill
              ,Bh.Bill_Type_Cd
              ,Case
                 When Bh.Ttl_Chrg Between 0 And 999.99 Then
                  '<1000'
                 When Bh.Ttl_Chrg Between 1000 And 4999.99 Then
                  '1000-5000'
                 When Bh.Ttl_Chrg Between 5000 And 9999.99 Then
                  '5000-10000'
                 When Bh.Ttl_Chrg Between 10000 And 24999.99 Then
                  '10000-25000'
               End Tbc
          From Billrevw.Bill_Hdr Bh
         Where Bh.Icn Like '%00'
           And Substr(Bh.Bill_Orgn_Id, 1, 3) In ('GAL', 'GSA', 'GEE')
           And Bh.Fnlzd_Paid_Posting_Dt Between '01-May-2012 0:00:00' And
               '31-Jul-2012 0:00:00'
           And Bh.Bill_Type_Cd <> '6'
           And Exists
         (Select Null
                  From Common.Jurisdiction_Td j
                 Where j.State_Cd Not In ('139', '199', '219', '239')
                   And Bh.Dw_Jrsdctn_Row_Id = j.Dw_Jrsdctn_Row_Id)
           And Nvl(Bh.Rptng_Exclusn_Cd, '') Not In ('MOD', 'NFS')) t
 Group By t.Bill_Orgn_Id, t.Type_Of_Bill, t.Bill_Type_Cd, t.Tbc
share|improve this answer
    
Why not use OR? Thanks for the CASE and IN. –  Gaffi Jan 9 '13 at 14:33
    
or is very costly –  Babur Jan 10 '13 at 7:52
    
You really shouldn't be using BETWEEN for ranges, even when it's (probably) a fixed-precision type. Please use an exclusive upper-bound ('<') - such as ... CASE WHEN bh.ttl_chrg >= 0 AND bh.ttl_chrg < 1000 THEN ..... This will also protect you if the precision does change in the future. Also, I find it difficult to believe that an OR is very costly; is there something in the Oracle documentation I'm not aware of? –  Clockwork-Muse Jan 10 '13 at 19:35
1  
select * from table_name where ( :A is null or A=:A ) full table scan select * from table_name where :A is not null and A=:A union all select * from table_name where :A is null table scan and index access –  Babur Jan 11 '13 at 5:51
    
@Babur "where :A is null" is going to be a full table scan, unless you have employed some special kind of indexing technique. –  David Aldridge May 1 '13 at 21:34
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.