Cursor with WHERE . . IN

[Peter Bateman]

       Hi Frank:
 
 
       PowerHouse needs to pad out to the size of the column in the IN predicate otherwise  you
       would not get a match.
 
 
       I think you need multiple HOST variables to do what you want.
 
       CURSOR product IN lilybase  &                               WHERE (product.prod_code IN (:FIRST , :SECOIND, :THIRD, :FORTH ) &         FIRST , SECOIND, THIRD, FORTH   etc represent one value to included
      in the IN predicate.
 
 
     Regards,
     Peer Bateman 
       
 
       


To: powerh-l at lists.sowder.comSubject: Cursor with WHERE . . INFrom: Frank.Huizinga at eu.averydennison.comDate: Fri, 4 Apr 2008 10:26:21 +0200Hi 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.
_________________________________________________________________
If you like crossword puzzles, then you'll love Flexicon, a game which combines four overlapping crossword puzzles into one!
http://g.msn.ca/ca55/208
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20080404/27158f00/attachment.html