Hi,

I need help i rewriting belwo query.

In the below query every time when vehicle is in ('DOWN','REPAIR') status it will pick strtmil from bc.billhst else it will pick abmil from ab.billmilg.


I want to join with ab.billmilg with bc.billhst only when vehicle is in ( 'DOWN','REPAIR') not all the time.

Can any one please help me in rewriting below query.

Query
------

select abcorpid,abcust,abunitid, abmil,
case when vehicle in ('DOWN','REPAIR') then strtmil else abmil end km, anstrdt
from (


select abcorpid, abcust,abunitid, abmil, min(bcstrtmil) strtmil,abstrdt
from bc.billhist right outer join
(select abcorpid, abunitid,abcust max(abmil) abmil, max(abstrdt) abstrdt
from ab.billmilg
where abcust IN ('629874','654897')
and abuse = 'K'
and abclass = 'M'
group by abcorpid, abunitid, abcust
) billmilg
on abcorpid = bccorpid
and abunitid = bcunitid
and bcuse = 'K'
and bcclass = 'M'
and (substr(sbdt,1,5) = substr(cdbdte,1,5)
or substr(char(date(qgpl.cyy2date(sbdt))),1,7) =
substr(char(date(qgpl.cyy2date(cdbdte))- 1 months),1,7))
group by abcorpid, abcust,abunitid, abmil,abstrdt
) X,

de.vehicle
where abcorpid = decorpid
and abunitid = deunitid
order by abcorpid,abcust,abunitid

Add a predicate "vehicle in ( 'DOWN','REPAIR')" into the ON condition of outer join.

You need not care wheather column vehicle is in table ab.billmilg or table bc.billhst, if not both.
Only if column vehicle were in both of table ab.billmilg and table bc.billhst,
you should qualify it to specify which vehicle.

Two notes:

Note 1): You might know this, but I want to confirm it.
if you join ab.billmilg with bc.billhst only when vehicle is in ('DOWN','REPAIR'),
then
case when vehicle in ('DOWN','REPAIR') then strtmil else abmil end
can be replaced by
COALESCE(strtmil , abmil)

Note 2):
Apart from your question,
it might be not a good practice to mix join by traditional way(comma and where conditions) and join by join keyword,
even if one was inside a subquery and another was in outer select.

What are your DB2 version/release and platform?

1) I thought that nested table expressions were not necessary, like...
2) Although I don't know the format of sbdt and cdbdte and function of qgpl.cyy2date,
I guessed that
( substr(sbdt,1,5) = substr(cdbdte,1,5)
or substr(char(date(qgpl.cyy2date(sbdt))),1,7) = substr(char(date(qgpl.cyy2date(cdbdte)) - 1 months),1,7)
)
might be replaced by
substr(sbdt,1,5) IN
( substr(cdbdte,1,5)
, substr(char(date(qgpl.cyy2date(cdbdte)) - 1 months),3,5)
)