case in pl/sql block.....
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: case in pl/sql block.....

  1. #1
    Join Date
    Jun 2003
    Location
    India
    Posts
    24

    case in pl/sql block.....

    Hi all,

    I am using oracle 8.1.0.6,

    here i am giving a case statement in sql statement

    SELECT (CASE WHEN ((b.block_code_desc='J') AND TRUNC(a.date_block_code1) = '31-dec-2001') THEN 'Y' END),
    (CASE WHEN ((b.block_code_desc='J') AND TRUNC(a.date_block_code1) = '31-dec-2001') THEN a.date_block_code1 END),
    (CASE WHEN ((b.block_code_desc='J') AND TRUNC(a.date_block_code1) = '31-dec-2001') THEN a.current_balance END)
    FROM L_CUST_ACCT_DAILY a, ref_block_code b
    WHERE a.countrycode = 'UK'
    AND b.countrycode = 'UK'
    AND a.block_code1 = b.block_code;

    it's working.


    if i am giving that case statement in pl/sql block

    declare
    a varchar2(100);
    b varchar2(100);
    c varchar2(100);
    begin
    SELECT
    CASE WHEN b.block_code_desc='J' AND TRUNC(a.date_block_code1) = '31-dec-2001' THEN
    'Y'
    END case1,
    CASE
    WHEN b.block_code_desc='J' AND TRUNC(a.date_block_code1) = '31-dec-2001' THEN
    a.date_block_code1
    END case2,
    CASE
    WHEN b.block_code_desc='J' AND TRUNC(a.date_block_code1) = '31-dec-2001' THEN
    a.current_balance END case3 into a, b, c
    FROM L_CUST_ACCT_DAILY a, ref_block_code b
    WHERE a.countrycode = 'UK'
    AND b.countrycode = 'UK'
    AND a.block_code1 = b.block_code;
    dbms_output.put_line(a || b || c);
    end;

    then it is giving the following error..


    ERROR at line 7:
    ORA-06550: line 7, column 4:
    PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
    ( * - + all mod null

    table avg count current distinct max min prior sql stddev sum
    unique variance execute the forall time timestamp interval
    date

    ;

    can anyone help regarding this......

    thanks in advance...

    kanna.
    Luv
    Kanna

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    In a recent thread Chris Long recommended using dynamic sql:
    http://forums.dbasupport.com/forums/...0&pagenumber=2

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    TRUNC(a.date_block_code1) = '31-dec-2001'
    ... is bad practice. Do ...

    Code:
    TRUNC(a.date_block_code1) = to_date('31-dec-2001','DD-mon-YYYY')
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by DaPi
    In a recent thread Chris Long recommended using dynamic sql:
    http://forums.dbasupport.com/forums/...0&pagenumber=2
    Yeah, and if Chris Long says you should do it, then you better do it. I hear he's really smart. (although a bit too conceited, if you ask me)

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That's actually the type of CASE statement that would convert easily to a decode(), which i believe PL/SQL would have no problem with.

    Also, you seem to be doing some rather funky things there -- could ...

    Code:
    Decode(b.block_code_desc,
       'J',Decode(Trunc(a.date_block_code1),
          To_Date('31-dec-2001','DD-mon-YYYY'),'Y'||a.date_block_code1||a.current_balance))
    You could even just ...

    Code:
    select
       'Y'||
       to_char(a.date_block_code1,'DD-Mon-YYYY')||
       a.current_balance
    from
       L_CUST_ACCT_DAILY a,
       ref_block_code b
    WHERE
       a.countrycode = 'UK' AND
       b.countrycode = 'UK' AND
       a.block_code1 = b.block_code AND
       b.block_code_desc = 'J' AND
       a.date_block_code1 >= To_Date('31-dec-2001','DD-mon-YYYY') AND
       a.date_block_code1 < To_Date('31-dec-2001','DD-mon-YYYY') + 1
    ... couldn't you?

    I'd also be nervous about the implicit conversion you seem to have on a.current_balance
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Feb 2001
    Posts
    203
    As Slimdave said, You can replace CASE code with DECODE
    Or
    Pull values using the cursor and use the
    "IF-ELSIF-ELSE-ENDIF" statements. That's what I did.

    Sree.
    sree

  7. #7
    Join Date
    Jun 2003
    Location
    India
    Posts
    24

    thanks for your replies regarding case to decode

    hi all,
    thanks to everyone for the answer,

    but i finished yesterday itself with DECODE,

    i want to know whether we can use CASE statement in pl/sql block of oracle version 8.1.0.6?

    this is my query.....

    so please clarify this......

    thanks and regards
    kanna

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by chrisrlong
    . . . . Chris Long . . . . he's . . . . a bit too conceited, if you ask me
    So you've noticed it too

  9. #9
    Join Date
    Jun 2003
    Location
    India
    Posts
    24

    thanks for your replies regarding case to decode

    hi all,
    thanks to everyone for the answer,

    but i finished yesterday itself with DECODE,

    i want to know whether we can use CASE statement in pl/sql block of oracle version 8.1.0.6?

    this is my query.....

    so please clarify this......

    thanks and regards
    kanna

  10. #10
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    Re: thanks for your replies regarding case to decode

    Originally posted by kanna
    i want to know whether we can use CASE statement in pl/sql block of oracle version 8.1.0.6?
    No, you cannot.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

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