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.
dBforums
>
sql to subsitute a column value with meaningful data and display
12-15-10, 00:58
Linear Mode
