Cursor with WHERE . . IN

[Frank.Huizinga at eu.averydennison.com]

Hi Peter,

Thanks a lot that works

Best regards,

Frank





Peter Bateman <peterbateman808 at hotmail.com> 
Sent by: 
powerh-l-bounces+frank.huizinga=eu.averydennison.com at lists.sowder.com
04-04-2008 14:30

To
<frank.huizinga at eu.averydennison.com>, <powerh-l at lists.sowder.com>
cc

Subject
RE: Cursor with WHERE . . IN






       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.com
Subject: Cursor with WHERE . . IN
From: Frank.Huizinga at eu.averydennison.com
Date: Fri, 4 Apr 2008 10:26:21 +0200


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.

You could win $1000 a day, now until May 12th, just for signing in to 
Windows Live Messenger. Check out SignInAndWIN.ca to learn more!-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: &quot;subscribe&quot; in message body to 
powerh-l-request at lists.sowder.com
Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message body to 
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
Add 'site:lists.sowder.com powerh-l' to your search terms to search the 
list archive at Google.


- -----------------------------------------------------------------
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/4fe014e1/attachment.html