Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    4

    Unanswered: Required help in re-writing query

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are your DB2 version/release and platform?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) I thought that nested table expressions were not necessary, like...
    Code:
    select abcorpid
         , abcust
         , abunitid
         , max(abmil) abmil
         , COALESCE(
              min(bcstrtmil)
            , max(abmil)
           ) km
         , max(abstrdt) abstrdt
     from
           ab.billmilg
     LEFT  outer join
           bc.billhist
       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)
       )
       AND vehicle in ('DOWN','REPAIR')
     INNER JOIN
           de.vehicle
       ON  abcorpid = decorpid
       and abunitid = deunitid
     group by
           abcorpid
         , abcust
         , abunitid
     order by
           abcorpid
         , abcust
         , abunitid
    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)
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •