Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Smile Unanswered: [solved] multiple "select" within "case when"

    I just pasted my query below. It is working as is, nothing really wrong with it. But every time I run into a situation like this, I keep wondering if there's another way to do it, without repeating the same SELECT in the same table (TB_PARZ_DSCR_STAT) so many times. The only difference among them is a single attribute (NR_SEQU_COR).

    Sorry if this is addressed somewhere else, I couldn't find it, yet. Maybe it's something that could be done with DECODE. But, although I never used it, I don't see how.

    And thanks for reading. Just hope I can change this to [solved] as soon as I find an answer.


    Code:
    update CMA.TB_PONT_INFO_PE_FOPAG set CD_STAT_EXPV_RECB = (case
      when (CD_NSO_ARQU is null and sysdate between DT_EXPV_INIC and DT_EXPV_FINA) then (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR = 8)
      when (CD_NSO_ARQU is null and sysdate = DT_EXPV_FINA - 1) then (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR = 2)
      when (CD_NSO_ARQU is null and sysdate >= DT_EXPV_FINA) then (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR = 3)
      when (CD_NSO_ARQU is not null and sysdate <= DT_EXPV_FINA) then (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR = 1)
      when (CD_NSO_ARQU is not null and sysdate > DT_EXPV_FINA) then (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR = 5)
      when (CD_NSO_ARQU is not null and DT_EXPV_FINA is null) then (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR = 6)
      else 0
    end)
    where trunc(sysdate) > DT_EXPV_INIC
    and (CD_STAT_EXPV_RECB is null or CD_STAT_EXPV_RECB not in (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR in (1,5,6)))
    edit: solved as seem in the following posts.
    Last edited by Cawas; 12-23-08 at 10:25. Reason: marking issue as solved in the title

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    6 separate UPDATE statements? That's all I can think of ... Might run faster, depending upon your indexes.
    --=cf

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I may have made a mistake or 2, but something like this should do it:
    Code:
    update CMA.TB_PONT_INFO_PE_FOPAG set CD_STAT_EXPV_RECB = 
         (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT 
          where CD_PONT_CTRL = 992 
          and NR_SEQU_COR = case when CD_NSO_ARQU is null then 
                                case when sysdate between DT_EXPV_INIC and DT_EXPV_FINA then 8
                                     when sysdate = DT_EXPV_FINA - 1 then 2
                                     when sysdate >= DT_EXPV_FINA then 3
                                end
                            else
                                case when sysdate <= DT_EXPV_FINA then 1
                                     when sysdate >  DT_EXPV_FINA then 5
                                     when DT_EXPV_FINA is null    then 6
                                end
                            end
         )
    where trunc(sysdate) > DT_EXPV_INIC
    and (CD_STAT_EXPV_RECB is null or CD_STAT_EXPV_RECB not in (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR in (1,5,6)))
    I dispensed with the "else 0" case as I believe it can never occur - please double check!

    Incidentally, some of those cases seem to overlap - for example this one:

    Code:
    (CD_NSO_ARQU is null and sysdate between DT_EXPV_INIC and DT_EXPV_FINA)
    would seem to override this one:
    Code:
    (CD_NSO_ARQU is null and sysdate = DT_EXPV_FINA - 1)
    Also, without a TRUNC, sysdate is highly unlikely to match the value of DT_EXPV_FINA - 1!!!

  4. #4
    Join Date
    Dec 2008
    Posts
    5

    Thumbs up thanks andrewst!

    to chuck, I haven't thought about multiple updates, but that would make it harder for me to do. maybe I'll try that in some other cases, tho. it's always good to have alternatives.

    the most andrew, that truly shred a light. I actually knew I can use fields from the update, but I haven't thought of doing it like that. thanks, it works!

    the else 0 actually can occur, although it shouldn't. and it's good to make a fall back rule in this case, since that error will be better treated that way.

    thanks for reminding me of trunc. that single field actually is already trunced as every insert on it have a trunc. but it makes more sense to add trunc there anyway.

    at last, those rules truly overlap. the second one should prevail. the better way to do it is to fix the overlap and not depend on any language default behavior for operation precedence. that I need to fix. thanks again for so much attention!

    here's the modified code, thanks to andrewst:

    Code:
    update CMA.TB_PONT_INFO_PE_FOPAG set CD_STAT_EXPV_RECB = (
    
      select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR = (
    
        case when CD_NSO_ARQU is null then (
    
          case when sysdate between DT_EXPV_INIC and DT_EXPV_FINA - 1 then 8
    
               when sysdate = DT_EXPV_FINA - 1 then 2
    
               when sysdate >= DT_EXPV_FINA then 3
    
               else 0
    
          end
    
        ) else (
    
          case when sysdate <= DT_EXPV_FINA then 1
    
               when sysdate > DT_EXPV_FINA then 5
    
               when DT_EXPV_FINA is null then 6
    
               else 0
    
          end
    
        ) end
    
      )
    
    )
    
    where trunc(sysdate) > trunc(DT_EXPV_INIC)
    
    and (CD_STAT_EXPV_RECB is null or CD_STAT_EXPV_RECB not in (select NR_SEQU_STAT from CMA.TB_PARZ_DSCR_STAT where CD_PONT_CTRL = 992 and NR_SEQU_COR in (1,5,6)))
    much much cleaner. that's great! ^_^
    Last edited by Cawas; 12-23-08 at 10:26. Reason: forgot trunc

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Glad to have helped.

    I'm just curious about the "else 0" requirements. As far as I can see, the only possible way a 0 could be returned is if CD_NSO_ARQU is null and (DT_EXPV_FINA is null or DT_EXPV_FINA < DT_EXPV_INIC). I guess that could happen. But the 2nd else is definitely superfluous:
    Code:
          case when sysdate <= DT_EXPV_FINA then 1
               when sysdate > DT_EXPV_FINA then 5
               when DT_EXPV_FINA is null then 6
               else 0
    If DT_EXPV_FINA isn't less than, equal to or greater than sysdate, and it isn't null, what is it?! Not that it really matters...

  6. #6
    Join Date
    Dec 2008
    Posts
    5

    Wink

    I'm with you, I don't believe the second else 0 will ever be triggered. But I'll try and remember to let you know if it does. :P

    So yeah, it's still just a fall back. It'll be easier to debug that way, in my case, due the way the whole system is done.

    If by any case it does not add a value there, I'll have to open the console where the query runs and find that error. If it's 0, it will be visible on the GUI, so it'll be much faster to identify the issue.

Posting Permissions

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