DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: no case statements in cursors?

  1. #1
    Join Date
    Mar 2001
    Posts
    63
    I have been trying to install a procedure that uses a cursor with a case statement in it. I can get the cursor to run through SQL Plus, and if I comment out the case statement, the procedure will install.

    Obviously I've never run into this problem before, and I don't know a good way around the problem either, since the case statement uses a like clause that can't be decoded. Anyone know how I can get the case statement to work?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Depends on the database release you are using. I belive 9i is the first release that allows CASE expressions inside PL/SQL (as it has a common parser for both SQL and PL/SQL). In 8i there is no such luck, you won't be able to use CASE inside PL/SQL. To be more precise - the only option you have would be to use "dynamic SQL" if you want to use CASE.

    You say you don't think the CASE expression can be converted into DECODE. I find this hard to belive - almost any IF_ELSIF_ELSE expression can be written with DECODE. Can you post your CASE expression here, maybe we would be able to convert it into DECODE?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2001
    Posts
    63
    The case statement in question was:

    select
    (case when name like '%CG ANT%' then 'Ant' else
    (case when name like '%CG GP%' then 'Group' else
    (case when name like '%CGC%' then 'Cutter' else
    (case when name like 'CG STA%' then 'Station' else
    (case when name like '%ACT%' then 'Activity' else
    (case when name like 'CGD%' then 'Districts' else
    (case when name like '%BASE%' then 'Base' else
    'Other' end) end) end) end) end) end) end) AID_UNIT_TYPE
    from x;

    If you can tell me how to make this into a decode statement, I would be appreciative, I never have quite been able to figure out how to do like clauses in decodes. Likewise, I'm not sure how to do greater than and less than operations in decodes either.

    -----------

    Otherwise, I did solve this problem by simply going down to the procedure body, after the loop is called and using an IF statement to supply the info I needed. Actually a simple one to figure out, I think it was one of those problems you need to walk away from for a little bit.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    i agree with jmodic that it's hard to believe that a case statement cannot be converted to decode. chk the one given below:

    select decode(INSTR(JOB,'LESMA'),0,
    decode(INSTR(JOB,'NAG'),0,
    decode(INSTR(JOB,'LER'),0,
    decode(INSTR(JOB,'ALYS'),0,
    decode(SUBSTR(JOB,1,4),'PRES','PRESIDENT'),
    'ANALYST'),'CLERK'),'MANAGER'),'SALESMAN') COL1
    from emp

    hope this helps.
    Cheers!
    OraKid.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Krutsinger
    The case statement in question was:

    select
    (case when name like '%CG ANT%' then 'Ant' else
    (case when name like '%CG GP%' then 'Group' else
    (case when name like '%CGC%' then 'Cutter' else
    (case when name like 'CG STA%' then 'Station' else
    (case when name like '%ACT%' then 'Activity' else
    (case when name like 'CGD%' then 'Districts' else
    (case when name like '%BASE%' then 'Base' else
    'Other' end) end) end) end) end) end) end) AID_UNIT_TYPE
    from x;
    First of all, I even don't like the way you wrote your CASE expression - you have nested 6 CASE expressions one into another, while you could use a single case statement. Remember, CASE does not provide you only with IF-ELSE-END logic, it also enables you to write IF-ELSIF-ELSIF...-ELSE-END logic. So your select should rathere look like:
    Code:
    select 
      (case when name like '%CG ANT%' then 'Ant' 
            when name like '%CG GP%' then 'Group' 
            when name like '%CGC%' then 'Cutter' 
            when name like 'CG STA%' then 'Station' 
            when name like '%ACT%' then 'Activity' 
            when name like 'CGD%' then 'Districts' 
            when name like '%BASE%' then 'Base' 
            else 'Other'
       end) AID_UNIT_TYPE 
    from x;
    I'm sure this code is much more readable and simple. BTW, there is a valid argument to use nested CASE like in your example, but only when you are using extremely many ELSIF conditions and you are approaching the upper limit of 128 conditions, but that would be a whole new isue to discuss...

    Now back to the original question - how to translate this into DECODE? balajiyes have given you an example, but unfortunately not on your exact given statement, so I'm not sure you have understood the logic he used. Again in his example there is the same thing I hate - nested DECODEs. They are so hard to read and to follow their logic, and you can almost allways avoid the nesting by using a single DECODE statement. So with your exact CASE statement I would use the following DECODE version:

    Code:
    select DECODE (1,
      SIGN(INSTR(name, 'CG ANT')), 'Ant',
      SIGN(INSTR(name, 'CG GP')), 'Group',
      SIGN(INSTR(name, 'CGC')), 'Cutter',
      INSTR(name, 'CG STA'), 'Station',
      SIGN(INSTR(name, 'ACT')), 'Activity',
      INSTR(name, 'CGD'), 'Districts',
      SIGN(INSTR(name, 'BASE')), 'Base',
      'Other') AID_UNIT_TYPE
    from x;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Mar 2001
    Posts
    63
    Thanks for the help jmodic, every code example I saw when I was learning the case statement used a nested case, certainly I can see how that is bad.

    Also, using the decode statement to ask as a like clause was really nifty, I'll definitely be using all of this knowledge.

    Looks like it was worth coming to work after all today.

Posting Permissions

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


Click Here to Expand Forum to Full Width