Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2003
    Posts
    10

    Unanswered: Case Statement in DB2 Stored Procedure

    Hi,

    I know that Case statements can be used with DB2 queries. How can I call a SQL query based on a column value say X and another SQL query based on same column with different column value Y in a Stored Procedure.

    I do not want to use UNION statement.
    Thanks,
    Gulshan

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not sure what you are after exactly. I am assuming you
    want something like this:


    Create procedure myproc(IN PKVal integer)
    begin
    declare testcol integer;

    Declare cursor1 cursor for select * from mytab1;
    Declare cursor2 cursor for select * from mytab2;


    select mycol into testcol from mytab3 where (pk = PKVal);

    case testcol
    when 1 then open cursor1;
    when 2 then open cursor2;
    end CASE;
    END


    HTH

    Andy

  3. #3
    Join Date
    Apr 2003
    Posts
    10
    Hi,

    My SP looks like this:

    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    CREATE PROCEDURE ncsdbo.gg ( )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR
    select
    rim_appl_id ,
    a.sco_long_desc "APPL ID DESC",
    rim_file_nm "FILE NAME",
    b.sco_long_desc "MSG STA",
    rim_err_desc "ERR DESC",
    substr(rmt_msg_txt ,4,8) "CORP REF",
    substr(rmt_msg_txt ,105,6) "CA TYPE"

    from
    ncsdbo.rim_rjctd_inp_msgs
    LEFT OUTER JOIN ncsdbo.sco_system_codes b on
    b.sco_code_type = rim_msg_sta_cd
    and
    b.sco_code_value = rim_msg_sta,
    ncsdbo.RMT_RJCTD_MSGS_TXT,
    ncsdbo.sco_system_codes a,
    ncsdbo.rit_rpt_instr,
    ncsdbo.sut_syg_updtn_tmstmp

    where
    rim_rcpt_dt=rmt_rcpt_tmstmp
    and
    (
    rim_rcpt_dt > SUT_UPDTN_TMSTMP
    )
    and
    rim_appl_id=a.sco_mnemonic
    and
    RMT_MSG_SGMNT_NUM = 1
    AND
    RMT_APPL_ID = 'NCSACTS'
    and
    rit_rpt_ident ='RPCI004'
    order by
    RIM_APPL_ID,
    RIM_RCPT_DT desc;

    OPEN c1;

    BEGIN NOT ATOMIC
    UPDATE
    ncsdbo.rit_rpt_instr
    SET
    rit_rpt_gen_date = current timestamp
    WHERE
    rit_rpt_ident='RPCI004'
    ;
    END;

    END P1
    @


    The column rim_appl_id can have values like A,B,C etc. Now if column rim_appl_id has value C, then I want to call a different SQL query within the same SP


    Originally posted by ARWinner
    I am not sure what you are after exactly. I am assuming you
    want something like this:


    Create procedure myproc(IN PKVal integer)
    begin
    declare testcol integer;

    Declare cursor1 cursor for select * from mytab1;
    Declare cursor2 cursor for select * from mytab2;


    select mycol into testcol from mytab3 where (pk = PKVal);

    case testcol
    when 1 then open cursor1;
    when 2 then open cursor2;
    end CASE;
    END


    HTH

    Andy
    Thanks,
    Gulshan

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am still not sure what you want to do.
    Do you want:
    1) if rim_appl_id = 'C' -- based on some other query -- return only RS1 else return only rs2
    2) if for each row of RS if rimappl_id = 'C' values of RS are from select1 else values are from select2

    if 1) then what i gave eariler should work.
    if 2) (and I suspect this is the case) Then use a union
    the case can be used but would be slow, since you would need to case
    every other column of the RS beside rim_appl_id unless the tables used are the same:

    if Different:
    select a.rim_appl_id,b.col1,c.col2 from mytab1 as a, mytab2 as b, mytab3 as c where (a.rim_appl_id = 'C') and (a.col1 = b.col1) and (b.col2 = c.col1)
    UNION
    select a.rim_appl_id,d.col1,e.col2 from mytab1 as a, mytab4 as d, mytab5 as d where (a.rim_appl_id <> 'C') and (a.col1 = d.col1) and (d.col2 = e.col1)

    if Same:

    select a.rim_appl_id,case rim_applid when 'C' then b.col1 else b.col3 end,case rim_appl_id when 'C' then c.col2 else b.col4 end
    from mytab1 as a, mytab2 as b, mytab3 as c where (a.col1 = b.col1) and (b.col2 = c.col1)


    HTH

    Andy


    Originally posted by Gulshan Gandhi
    Hi,

    My SP looks like this:

    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    CREATE PROCEDURE ncsdbo.gg ( )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR
    select
    rim_appl_id ,
    a.sco_long_desc "APPL ID DESC",
    rim_file_nm "FILE NAME",
    b.sco_long_desc "MSG STA",
    rim_err_desc "ERR DESC",
    substr(rmt_msg_txt ,4,8) "CORP REF",
    substr(rmt_msg_txt ,105,6) "CA TYPE"

    from
    ncsdbo.rim_rjctd_inp_msgs
    LEFT OUTER JOIN ncsdbo.sco_system_codes b on
    b.sco_code_type = rim_msg_sta_cd
    and
    b.sco_code_value = rim_msg_sta,
    ncsdbo.RMT_RJCTD_MSGS_TXT,
    ncsdbo.sco_system_codes a,
    ncsdbo.rit_rpt_instr,
    ncsdbo.sut_syg_updtn_tmstmp

    where
    rim_rcpt_dt=rmt_rcpt_tmstmp
    and
    (
    rim_rcpt_dt > SUT_UPDTN_TMSTMP
    )
    and
    rim_appl_id=a.sco_mnemonic
    and
    RMT_MSG_SGMNT_NUM = 1
    AND
    RMT_APPL_ID = 'NCSACTS'
    and
    rit_rpt_ident ='RPCI004'
    order by
    RIM_APPL_ID,
    RIM_RCPT_DT desc;

    OPEN c1;

    BEGIN NOT ATOMIC
    UPDATE
    ncsdbo.rit_rpt_instr
    SET
    rit_rpt_gen_date = current timestamp
    WHERE
    rit_rpt_ident='RPCI004'
    ;
    END;

    END P1
    @


    The column rim_appl_id can have values like A,B,C etc. Now if column rim_appl_id has value C, then I want to call a different SQL query within the same SP

  5. #5
    Join Date
    Apr 2003
    Posts
    10
    Hi,

    Regarding this example:

    select a.rim_appl_id,case rim_applid when 'C' then b.col1 else b.col3 end,case rim_appl_id when 'C' then c.col2 else b.col4 end
    from mytab1 as a, mytab2 as b, mytab3 as c where (a.col1 = b.col1) and (b.col2 = c.col1)


    Can we use CASE statement in WHERE clause like you have done in SELECT cause.


    Originally posted by ARWinner
    I am still not sure what you want to do.
    Do you want:
    1) if rim_appl_id = 'C' -- based on some other query -- return only RS1 else return only rs2
    2) if for each row of RS if rimappl_id = 'C' values of RS are from select1 else values are from select2

    if 1) then what i gave eariler should work.
    if 2) (and I suspect this is the case) Then use a union
    the case can be used but would be slow, since you would need to case
    every other column of the RS beside rim_appl_id unless the tables used are the same:

    if Different:
    select a.rim_appl_id,b.col1,c.col2 from mytab1 as a, mytab2 as b, mytab3 as c where (a.rim_appl_id = 'C') and (a.col1 = b.col1) and (b.col2 = c.col1)
    UNION
    select a.rim_appl_id,d.col1,e.col2 from mytab1 as a, mytab4 as d, mytab5 as d where (a.rim_appl_id <> 'C') and (a.col1 = d.col1) and (d.col2 = e.col1)

    if Same:

    select a.rim_appl_id,case rim_applid when 'C' then b.col1 else b.col3 end,case rim_appl_id when 'C' then c.col2 else b.col4 end
    from mytab1 as a, mytab2 as b, mytab3 as c where (a.col1 = b.col1) and (b.col2 = c.col1)


    HTH

    Andy
    Thanks,
    Gulshan

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I not sure what you want. You can only have predicates
    in the where clause. Please give me an example of what you are driving at. Just write what you want to do, and I will comment on that.

    Andy

    Originally posted by Gulshan Gandhi
    Hi,

    Regarding this example:

    select a.rim_appl_id,case rim_applid when 'C' then b.col1 else b.col3 end,case rim_appl_id when 'C' then c.col2 else b.col4 end
    from mytab1 as a, mytab2 as b, mytab3 as c where (a.col1 = b.col1) and (b.col2 = c.col1)


    Can we use CASE statement in WHERE clause like you have done in SELECT cause.

  7. #7
    Join Date
    Apr 2003
    Posts
    10
    Hi,

    If I run the SP which is mentioned above, following result would appear:

    rim_appl_id Segment Num CA TYPE
    NCSISWM 1 This is a test message
    NCSISWM 2 This is a test message
    NCSACTS 1 001002003
    NCSACTS 2 TEST MESSGAE

    Here rim_appl_id, segment num and "CA Type are colums of the table.
    This is the way data is stored in table.

    What I want is that when I run the SP and rim_appl_id is "NCSACTS", it should bring the result from Segment Num 1 column only with 1-4 char of the CA Type columd (0001) and if rim_appl_is other than "NCSACTS", it should display both Segment Num (1,2).

    The Result should be:

    rim_appl_id CA Type
    NCSISWM This is a test message
    NCSISWM This is a test message
    NCSACTS 0001

    I am using Actuate to call this SP


    I hope this is not confusiong and thanks for the your patience




    Originally posted by ARWinner
    I not sure what you want. You can only have predicates
    in the where clause. Please give me an example of what you are driving at. Just write what you want to do, and I will comment on that.

    Andy
    Thanks,
    Gulshan

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Actually it helps. what you need is a where clause
    that gives all rows when rim_appl_id <> 'NCSACTS' and only those
    wiith Segment Num = 1 when it is equal:

    where ((rim_appl_id <> 'NCSACTS') OR ((rim_appl_id = 'NCSACTS') and (Segment_num = 1)))

    This should do what you want... (you do not need a case, just some complex boolean logic)

    Andy


    Originally posted by Gulshan Gandhi
    Hi,

    If I run the SP which is mentioned above, following result would appear:

    rim_appl_id Segment Num CA TYPE
    NCSISWM 1 This is a test message
    NCSISWM 2 This is a test message
    NCSACTS 1 001002003
    NCSACTS 2 TEST MESSGAE

    Here rim_appl_id, segment num and "CA Type are colums of the table.
    This is the way data is stored in table.

    What I want is that when I run the SP and rim_appl_id is "NCSACTS", it should bring the result from Segment Num 1 column only with 1-4 char of the CA Type columd (0001) and if rim_appl_is other than "NCSACTS", it should display both Segment Num (1,2).

    The Result should be:

    rim_appl_id CA Type
    NCSISWM This is a test message
    NCSISWM This is a test message
    NCSACTS 0001

    I am using Actuate to call this SP


    I hope this is not confusiong and thanks for the your patience

  9. #9
    Join Date
    Apr 2003
    Posts
    10
    Great, this works !!!

    select
    rim_appl_id ,
    a.sco_long_desc "APPL ID DESC",
    rim_file_nm "FILE NAME",
    b.sco_long_desc "MSG STA",
    rim_err_desc "ERR DESC",
    case when rim_appl_id='NCSACTS' then substr(rmt_msg_txt ,4,8) else rmt_msg_txt end
    from
    ncsdbo.rim_rjctd_inp_msgs
    LEFT OUTER JOIN ncsdbo.sco_system_codes b on
    b.sco_code_type = rim_msg_sta_cd
    and
    b.sco_code_value = rim_msg_sta,
    ncsdbo.RMT_RJCTD_MSGS_TXT,
    ncsdbo.sco_system_codes a,
    ncsdbo.rit_rpt_instr,
    ncsdbo.sut_syg_updtn_tmstmp

    where
    rim_rcpt_dt=rmt_rcpt_tmstmp
    and
    (
    rim_rcpt_dt > SUT_UPDTN_TMSTMP
    )
    and
    rim_appl_id=a.sco_mnemonic
    and
    ((rim_appl_id <> 'NCSACTS') OR ((rim_appl_id = 'NCSACTS') and (RMT_MSG_SGMNT_NUM = 1)))
    and
    rit_rpt_ident ='RPCI004'
    order by
    RIM_APPL_ID,
    RIM_RCPT_DT desc;

    Thanks for all your help.

    Originally posted by ARWinner
    Actually it helps. what you need is a where clause
    that gives all rows when rim_appl_id <> 'NCSACTS' and only those
    wiith Segment Num = 1 when it is equal:

    where ((rim_appl_id <> 'NCSACTS') OR ((rim_appl_id = 'NCSACTS') and (Segment_num = 1)))

    This should do what you want... (you do not need a case, just some complex boolean logic)



    Andy
    Thanks,
    Gulshan

  10. #10
    Join Date
    Apr 2003
    Posts
    10
    Hi,

    Sorry to bother you again.Regarding the example I mentioned:

    ID Seg No Text
    NCSISWM 1 This is a test message1
    NCSISWM 2 This is a test message2
    NCSISWM 3 This is a test message3
    NCSISWM 4 This is a test message4


    These are the 4 rows from one table. I want my query to produce one row as:

    ID Text
    NCSISWM This is a test message1 This is a test message2
    This is a test message3 This is a test message4


    Thanks



    Originally posted by Gulshan Gandhi
    Great, this works !!!

    select
    rim_appl_id ,
    a.sco_long_desc "APPL ID DESC",
    rim_file_nm "FILE NAME",
    b.sco_long_desc "MSG STA",
    rim_err_desc "ERR DESC",
    case when rim_appl_id='NCSACTS' then substr(rmt_msg_txt ,4,8) else rmt_msg_txt end
    from
    ncsdbo.rim_rjctd_inp_msgs
    LEFT OUTER JOIN ncsdbo.sco_system_codes b on
    b.sco_code_type = rim_msg_sta_cd
    and
    b.sco_code_value = rim_msg_sta,
    ncsdbo.RMT_RJCTD_MSGS_TXT,
    ncsdbo.sco_system_codes a,
    ncsdbo.rit_rpt_instr,
    ncsdbo.sut_syg_updtn_tmstmp

    where
    rim_rcpt_dt=rmt_rcpt_tmstmp
    and
    (
    rim_rcpt_dt > SUT_UPDTN_TMSTMP
    )
    and
    rim_appl_id=a.sco_mnemonic
    and
    ((rim_appl_id <> 'NCSACTS') OR ((rim_appl_id = 'NCSACTS') and (RMT_MSG_SGMNT_NUM = 1)))
    and
    rit_rpt_ident ='RPCI004'
    order by
    RIM_APPL_ID,
    RIM_RCPT_DT desc;

    Thanks for all your help.
    Thanks,
    Gulshan

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The only way I can think of is to write a UDF.

    select id,case id when 'NCSISWM' then UDF_COMBINE_TEXT(id) else text
    from ...
    where ...

    the UDF looks something like

    create function udf_combine_text(p_id integer) returns varchar(2000)
    language SQL NOT deterministic NO EXTERNAL ACTION READS SQL DATA
    begin atomic
    declare returntext varchar(4000);
    DECLARE readtext varchar(200);
    declare cursor1 for select text from mytab where id = p_id;

    set returntext = '';

    open cursor1;

    while there are rows for the cursor:
    fetch cursor1 into readtext;
    set returntext = returntext || readtext;
    end while
    close cursor1;
    return returntext;
    end

    there are several ways to control the while loop.
    1) count the number of rows before and loop that many times.
    2) loop until error (EOF) and handle error

    HTH

    Andy



    Originally posted by Gulshan Gandhi
    Hi,

    Sorry to bother you again.Regarding the example I mentioned:

    ID Seg No Text
    NCSISWM 1 This is a test message1
    NCSISWM 2 This is a test message2
    NCSISWM 3 This is a test message3
    NCSISWM 4 This is a test message4


    These are the 4 rows from one table. I want my query to produce one row as:

    ID Text
    NCSISWM This is a test message1 This is a test message2
    This is a test message3 This is a test message4


    Thanks

  12. #12
    Join Date
    Apr 2003
    Posts
    10
    Thanks AR,
    I would try this.




    Originally posted by ARWinner
    The only way I can think of is to write a UDF.

    select id,case id when 'NCSISWM' then UDF_COMBINE_TEXT(id) else text
    from ...
    where ...

    the UDF looks something like

    create function udf_combine_text(p_id integer) returns varchar(2000)
    language SQL NOT deterministic NO EXTERNAL ACTION READS SQL DATA
    begin atomic
    declare returntext varchar(4000);
    DECLARE readtext varchar(200);
    declare cursor1 for select text from mytab where id = p_id;

    set returntext = '';

    open cursor1;

    while there are rows for the cursor:
    fetch cursor1 into readtext;
    set returntext = returntext || readtext;
    end while
    close cursor1;
    return returntext;
    end

    there are several ways to control the while loop.
    1) count the number of rows before and loop that many times.
    2) loop until error (EOF) and handle error

    HTH

    Andy
    Thanks,
    Gulshan

Posting Permissions

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