Taking unions and turning into subqueries 1

[Stewman]

Hello,

I have created a query using unions that runs fine but runs slow. I was trying to speed this up and am thinking subqueries would solve the problem. I am having difficulty in turning these into subqueries. I've tried a few iterations but keep returning incorrect data sets duplicating data. Any help greatly appreciated.

select a.inci_id, a.appstate, a.district, a.category2, c.descriptn, a.csstatus, b.prop_id, b.propcode, b.propdesc
from lwmain a, prmain b, systab1 c
where a.inci_id = b.case_id (+)
and a.category2 = c.code_agcy (+)
and c.code_key = 'CAT2'
and b.propcode = '13'

union

select a.inci_id, a.appstate, a.district, a.category2, c.descriptn, a.csstatus, b.prop_id, b.propcode, b.propdesc
from lwmain a, prmain b, systab1 c
where a.inci_id = b.case_id (+)
and a.category2 = c.code_agcy (+)
and c.code_key = 'CAT2'
and b.propcode = '77'
and b.propdesc LIKE '%CARTR%'

union
select a.inci_id, a.appstate, a.district, a.category2, c.descriptn, a.csstatus, b.prop_id, b.propcode, b.propdesc
from lwmain a, prmain b, systab1 c
where a.inci_id = b.case_id (+)
and a.category2 = c.code_agcy (+)
and c.code_key = 'CAT2'
and b.propcode = '77'
and b.propdesc LIKE '%PROJECT%'

 

[Stewman]

I've tweaked it and got it down to one union but still taking too long at 49 seconds. Any way to combine all into 1 query. I did a second subquery for propcode similar to the propdesc one but searching for prop codes 13 or 77 but it returned only if it met the like conditions. This doesn't work since a majority of prop 13 codes don't contain the language described in the likes.

select a.inci_id, a.appstate, a.district, a.category2, c.descriptn, a.csstatus,
b.prop_id, b.propcode, b.propdesc
from lwmain a, prmain b, systab1 c
where a.inci_id = b.case_id (+)
and a.category2 = c.code_agcy (+)
and c.code_key = 'CAT2'
and b.propcode ='77'
and b.propdesc in (select prmain2.propdesc
from prmain prmain2
where prmain2.propdesc LIKE '%PROJECTILE%' or prmain2.propdesc LIKE '%CARTR%')

union
select a.inci_id, a.appstate, a.district, a.category2, c.descriptn, a.csstatus,
b.prop_id, b.propcode, b.propdesc
from lwmain a, prmain b, systab1 c
where a.inci_id = b.case_id (+)
and a.category2 = c.code_agcy (+)
and c.code_key = 'CAT2'
and b.propcode = '13'

 

[gmmastros]

try this:

select a.inci_id, a.appstate, a.district, a.category2, c.descriptn, a.csstatus,
       b.prop_id, b.propcode, b.propdesc
from lwmain a,  prmain b, systab1 c             
where a.inci_id = b.case_id (+)
and a.category2 = c.code_agcy (+)
and c.code_key = 'CAT2'
and ( (b.propcode ='77' 
      And (b.propdesc LIKE '%PROJECTILE%' or  b.propdesc LIKE '%CARTR%'))
      Or b.propcode = '13'
    )

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Stewman]

That appears to have worked. Your are awesome. Thanks!

 

[gmmastros]

You're welcome.

still taking too long at 49 seconds

How long does it take now? (just curious)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom