Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: Suggestions for rewriting this query

    Does anybody have any suggestions to rewrite the 2nd WHEN part of the query??? Thank you.

    ------------------------------------------------------------
    update t_pgba_hdr
    set HCFA_PLACE_TRMT_CD2 =
    case when (select max(b.HCFA_PLACE_TRMT_CD)
    from t_pgba_hdr as b
    where t_pgba_hdr.clm_id2 = b.clm_id2) like '[A-Z]%'
    then '99'
    when (select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD)))
    from t_pgba_hdr as b
    where t_pgba_hdr.clm_id2 = b.clm_id2) in '[0-9]'
    then '0' + (select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD)))
    from t_pgba_hdr as b
    where t_pgba_hdr.clm_id2 = b.clm_id2)
    end

    ------------------------------------------------------------


    -soumya

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would rewrite your query like this:
    Code:
    update t_pgba_hdr 
       set HCFA_PLACE_TRMT_CD2 = 
              case when ( select max(b.HCFA_PLACE_TRMT_CD)
                            from t_pgba_hdr as b
                           where t_pgba_hdr.clm_id2 = b.clm_id2 ) 
                        like '[A-Z]%'
                   then '99'
                   when ( select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD))) 
                            from t_pgba_hdr as b
                           where t_pgba_hdr.clm_id2 = b.clm_id2 ) 
                        in '[0-9]'
                   then '0' + 
                        ( select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD))) 
                             from t_pgba_hdr as b
                            where t_pgba_hdr.clm_id2 = b.clm_id2 )
               end
    why do you LTRIM/RTRIM in the 2nd WHEN but not the 1st?

    and what are you actually trying to do here?

    can you give sample rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    7
    This is what the first when does

    If HCFA_PLACE_TRMT_CD is an alpha character (e.g. A, B, RX) then set HCFA_PLACE_TRMT_CD2 to “99”


    The following is what the second when does

    If HCFA_PLACE_TRMT_CD = “1” then set HCFA_PLACE_TRMT_CD2 to “01”; if “2” then “02”, if “3” then “03” etc. through if “9” then “09”


    I need to keep the self join because there are multiple rows in the table with the same clm_id2 and they all need to get the same HCFA_PLACE_TRMT_CD2

    Thank you for reading my post

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't understand

    why are you using MAX subqueries for this?

    Code:
    update t_pgba_hdr 
       set HCFA_PLACE_TRMT_CD2 = '99'
     where HCFA_PLACE_TRMT_CD like '[A-Z]%'
    
    update t_pgba_hdr 
       set HCFA_PLACE_TRMT_CD2 = '0' + HCFA_PLACE_TRMT_CD
     where HCFA_PLACE_TRMT_CD like '[0-9]'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2002
    Posts
    50
    Quote Originally Posted by snaidu
    I need to keep the self join because there are multiple rows in the table with the same clm_id2 and they all need to get the same HCFA_PLACE_TRMT_CD2
    Are you saying that you have mulitple rows with the same clm_id2, but different HCFA_PLACE_TRMT_CD, and you want all the rows with the same clm_id2 to have the same HCFA_PLACE_TRMT_CD2?

    I'm really not sure that your subquery would work to root that out. But, if it did produce the results you are looking for, I would probably do something more like this:

    Code:
    update t_pgba_hdr 
       set HCFA_PLACE_TRMT_CD2 = 
              case IsNumeric(select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD))) 
                            from t_pgba_hdr as b
                           where t_pgba_hdr.clm_id2 = b.clm_id2) 
                   when 0
                   then '99'
                   when 1
                   then Reverse(Convert(Char(2), Reverse('0' + 
                        ( select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD))) 
                             from t_pgba_hdr as b
                            where t_pgba_hdr.clm_id2 = b.clm_id2 ))))
               end

Posting Permissions

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