DB2 select statement does not return any rows

ebi rezaei 0
Viewed 49

I wrote query:

select * from tbl1 as tbl1 
where tbl1.pkey >= cast(080138738 as varchar(10)) 
and tbl1.pkey <= cast(080138738 as var char(10);

tbl1 contains row that its pkey value is 08013878 but this query dosn't retutn any rows.

parameter-passing db2 casting select dynamic
1 Answers

Ok, let's start guessing, if you don't want to provide some fully reproducible example.

select * 
from 
(
  VALUES
  -- doesn't return a row
    '080138738'
  -- returns a row
  --'80138738'
) tbl1 (pkey)
where tbl1.pkey >= CAST (080138738 as varchar(10)) 
  and tbl1.pkey <= CAST (080138738 as varchar(10))

If you have pkey of VARCHAR data type then look at the example above.
Let's look at the result of the CAST (080138738 as varchar(10)) expression.
The integer 080138738 value is equal to the 80138738 one without leading zero(es), and namely this value is converted to the '80138738' VARCHAR value.
But the following query returns no rows obviously:

SELECT 1
FROM SYSIBM.SYSDUMMY1
WHERE '080138738' >= 
'80138738' -- the result of CAST (080138738 as varchar(10))
  AND '080138738' <= 
'80138738' -- the result of CAST (080138738 as varchar(10))

The solution is to use correct data type for parameters.

--# SET TERMINATOR @
CREATE TABLE TBL1 (PKEY VARCHAR (10))@
INSERT INTO TBL1 (PKEY) VALUES '080138738'@

BEGIN
  DECLARE V_STMT VARCHAR (500);
  DECLARE V_CNT INT;
  DECLARE C1 CURSOR FOR S1;

  SET V_STMT = 
     'SELECT COUNT (*) FROM TBL1 '
  || 'WHERE pkey >= cast(? as varchar(10)) and pkey <= cast(? as varchar(10))';
  PREPARE S1 FROM V_STMT;

  -- Correct - using VARCHAR constants
  OPEN C1 USING '080138738', '080138738';

  -- Wrong - using INT constants
  -- OPEN C1 USING 080138738, 080138738;

  FETCH C1 INTO V_CNT;
  -- We throw an exception, if the result is unexpected
  IF V_CNT <> 1 THEN 
    SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT = 'Not expected result';
  END IF;
END
@

dbfiddle link

Mark Barinstein0
Related