# Simplify Oracle SQL Unions

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;

-

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

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