Cursor with WHERE . . IN

[Frank.Huizinga at eu.averydennison.com]

Hi All,

I'm facing a problem with using embedded SQL WHERE .. IN

When I'm only selecting one product it works fine (although I should than 
use WHERE . . =)
When I want to retrieve multiple products it fails. The examples below 
explains what is happening in the background. Has any one a solution to 
this?

Selecting one product:

SCREEN in 
SET LIST SQL 
 
TEMPORARY t_prod_array CHARACTER SIZE  256 & 
  INITIAL "DA7022000" 
 
CURSOR product IN lilybase  & 
  WHERE (product.prod_code IN (:TRUNC(t_prod_array))) & 
     PRIMARY KEY prod_code OCCURS 5 
 
SKIP TO 3 
 
ALIGN (1,,4) (,,30) 
CLUSTER OCCURS WITH qad_product 
FIELD prod_code DISPLAY 
FIELD prod_desc1 DISPLAY 
 
BUILD 

Resulting DMAPIFIL content:

prepare r1 from 
"SELECT * FROM LILY.PRODUCT  where PRODUCT.PROD_CODE IN (:phE1 )"
 in t1;
describe input r1;
declare c1 cursor for r1 read write, keep;
open c1 parm phE1:"DA7022000         ";

The phE1 variable is padded with blanks to the size of prod_code CHAR(18)
but the record is retieved.


Selection of multiple products

SCREEN in 
SET LIST SQL 
 
TEMPORARY t_prod_array CHARACTER SIZE  256 & 
  INITIAL "DA7022000,DA7021980" 
 
CURSOR product IN lilybase  & 
  WHERE (product.prod_code IN (:TRUNC(t_prod_array))) & 
     PRIMARY KEY prod_code OCCURS 5 
 
SKIP TO 3 
 
ALIGN (1,,4) (,,30) 
CLUSTER OCCURS WITH product 
FIELD prod_code DISPLAY 
FIELD prod_desc1 DISPLAY 
 
BUILD 

Resulting DMAPIFIL content:

prepare r1 from 
"select * from LILY.PRODUCT where PRODUCT.PROD_CODE IN (:phE1 )"
 in t1;
describe input r1;
declare c1 cursor for r1 read write, keep;
open c1 parm phE1:"DA7022000,DA702198";

The phe1 parameter is truncated to the size of the attribute prod_code 
which is CHAR(18)
and 'No records found matching . . ' is issued


Thanks in advance

Best regards,

Frank


- -----------------------------------------------------------------
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20080404/6f0b0496/attachment.htm