Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Unanswered: sql to subsitute a column value with meaningful data and display

    I am working on a db2 procedure that will check the value of 'status' column of a table and print meaningful information instead of number. These number
    represent status codes which is defined in C program.

    For example,
    if status is 0 then display 'issue' instead of 0
    if status is 1 then display 'expired' instead of 1
    if status is 2 then display 'canceled' instead of 2

    Code:
    Current result:
    
    Number     Value     Status
    --------   ------    -------
    000001     1000      0
    000001     1001      1
    000001     1002      2
    
    Expected result:
    
    Number     Value     Status
    --------   ------    -------
    000001     1000      ISSUE
    000001     1001      EXPIRED
    000001     1002      CANCELED
    I am new to procedural language. After some initial research, i have managed to create a procedure that is meant to check the column value and display the status tktTktSts more meaningfully. The sql procedure is in its beginning stage and is given below. The procedure is stored in a file named loop.db2 and it is executed using the following commands from a shell script.

    db2 connect to bas00
    db2 -td@ -vf loop.db2
    db2 "call tkt (?)"
    db2 drop procedure tkt
    db2 connect reset

    The problem is that i am getting the following error

    "SQL0304N A value cannot be assigned to a host variable because the value is not within the range of the host variable's data type. SQLSTATE=22003"

    I am not able to understand which variable is it ?

    Code:
    Schema for tkt copied from C program.
    
            tktTktNo        char(TKT_NO_LEN) not null
            tktVal          double precision not null,
            tktCyc          char(CYC_LEN) not null,
            tktTktSts       smallint not null,
            tktExpTim       double precision not null,
    
    Table description using db2 describe command
    
            tkttktno - (character - 16)
            tktval - (double - 8)
            tktcyc - (character - 2)
            tkttktsts - (smallint - 2)
            tktexptim - (double - 8)
    Code:
      1 create procedure tkt(out counter int)
      2 language sql
      3 begin
      4
      5 declare         v_tktTktNo      varchar(16);
      6 declare         v_tktVal          double;
      7 declare         v_tktCyc         varchar(2);
      8 declare         v_tktTktSts     smallint;
      9 declare         v_tktExpTim     double;
     10 declare         v_counter       smallint default 0;
     11
     12 declare tkt_cur cursor for
     13 select          tktTktNo
     14                 tktVal,
     15                 tktCyc,
     16                 tktTktSts,
     17                 tktExpTim
     18 from            tkt
     19 order by        tktIssTim;
     20
     21 declare continue handler for not found
     22 set counter = -1;
     23
     24 set counter = 0;
     25 open tkt_cur;
     26 fetch_loop:
     27 loop
     28         fetch tkt_cur into
     29                 v_tktTktNo;
     30                 v_tktVal,
     31                 v_tktCyc,
     32                 v_tktTktSts,
     33                 v_tktExpTim;
     34         set v_counter = v_counter + 1;
     35         if v_tktTktNo = ' ' then
     36                 leave fetch_loop;
     37         end if;
     38 end loop fetch_loop;
     39 close tkt_cur;
     40 set counter = v_counter;
     41 end @
    Can you guys help me in giving a start ?
    How can i print meaningful data for status instead of codes ?
    Is there an alternate method other than procedural language to achieve this, i mean using only SQL ?.

    Thanks.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    basilbc, the CASE expression should get you what you want with just SQL.
    Code:
    SELECT TKTTKTNO
         , TKTVAL
         , CASE TKTTKTSTS
             WHEN 0 THEN 'Issue'
             WHEN 1 THEN 'Expired'
             WHEN 2 THEN 'Canceled'
                    ELSE 'UNKNOWN'
           END AS TKTTKTSTS_TEXT
         , TKTEXPTIM
    FROM TKT
    ORDER BY TKTISSTIM

  3. #3
    Join Date
    Aug 2008
    Posts
    3

    sql to subsitute a column value with meaningful data and display

    Quote Originally Posted by Stealth_DBA View Post
    basilbc, the CASE expression should get you what you want with just SQL.
    Code:
    SELECT TKTTKTNO
         , TKTVAL
         , CASE TKTTKTSTS
             WHEN 0 THEN 'Issue'
             WHEN 1 THEN 'Expired'
             WHEN 2 THEN 'Canceled'
                    ELSE 'UNKNOWN'
           END AS TKTTKTSTS_TEXT
         , TKTEXPTIM
    FROM TKT
    ORDER BY TKTISSTIM
    yes, that will do.
    thanks for the help.

Posting Permissions

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