VALUE function in SQL

V K R A S Srinivas · New User Joined: 02 Jan 2007 Posts: 9 Location: Hyderabad

Hi !

I have requirement as below:

Table A with Cloumns AC1 CHAR(10), AC2 SMALLINT, AC3
Table B with Cloumns BC1 SMALLINT, BC2 SMALLINT, BC3

I have used VALUE function in SELECT Clause to retrieve all the rows from A & B Tables with AC2 and BC2 are equal as below:

SELECT VALUE(SUBSTR(AC1, 1, 4), '0'), AC2
FROM TABLE1 A
TABLE2 B
WHERE AC2 = BC2.

Now I Have to retrieve only those rows where the first 4 characters of AC1 is equal to BC1. But as AC1 and BC1 are with different datatypes, I am getting -401 sqlcode when I use the following sql:

SELECT VALUE(SUBSTR(AC1, 1, 4), '0'), AC2
FROM TABLE1 A
TABLE2 B
WHERE AC2 = BC2
AND BC1 IN VALUE(SUBSTR(AC1, 1, 4), '0')

So Could you please suggest anyother way to implement this.

Thank you in advance
Srinivas Vaddadi

enrico-sorichetti · Posted: Wed Jan 27, 2010 6:48 pm

V K R A S Srinivas · New User Joined: 02 Jan 2007 Posts: 9 Location: Hyderabad

Hi Enrico,

THank you for the quick response.

We are sure that the first 4 chars of the field AC1 is getting populated with integer values only. I am not sure what can be done.

Thank you
Srinivas Vaddadi

enrico-sorichetti · Posted: Wed Jan 27, 2010 7:08 pm

what if the bc1 contains a number less than 1000 ??
what about the possible leading zeroes in the AC1 column??

V K R A S Srinivas · New User Joined: 02 Jan 2007 Posts: 9 Location: Hyderabad

Hi Enrico,
BC1 contains a max value of 25 only. It cannot contain more than 25.

Thank you
Srinivas Vaddadi

enrico-sorichetti · Posted: Wed Jan 27, 2010 7:20 pm

if bc1 has a max value of 25, how can You expect to match it against a 4 bytes value

there are a few things unclear in the explanation and/or the requirements and/or the design

GuyC · Posted: Thu Jan 28, 2010 8:58 pm

you can't substring an integer. first convert it with the available functions like DIGITS or CHAR , ...

I'm not sure you understand what the function VALUE() does, it has nothing to do with a numeric value within a char-column. but everything with nullable columns.

google ,read up and experiment on DB2 functions.
Once you find the correct function, I'm sure you can solve this with the case-specific answers to the questions Enrico poses.