Re: SQL List value as a variable

Thanks Charles.
This is only for 2 programs, so a UDF or Stored Proceedure is not needed, and 
the values are fixed.
I will use the first solution that you provided.

I appreciate the tip.
 
Jeff Young 
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer 
V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions 
Implementer V5R3 


  
 





----- Original Message ----
From: "Wilt, Charles" <CWilt@xxxxxxxxxxxx>
To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, December 29, 2006 8:11:55 AM
Subject: RE: SQL List value as a variable


Jeff,

If you want to use static SQL, I believe you need to do:
Select Count(*)  into :Nbr from workfile where type not in (:bktyp1,
:bktyp2, :bktyp3)

Otherwise you could use dynamic SQL, but then I think you'd need to use
a cursor as SELECT INTO isn't valid in a dynamic statement.

Best solutions:
If you have a proper RDB model then...

Select count(*) into :nbr 
from workfile 
Where type not in (select type
                   from typemaster
                   where bulkflag = 'Y'
                   )

Otherwise, consider a UDF or Stored Procedure.

HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Thursday, December 28, 2006 5:50 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL List value as a variable

I am attempting to pass a list value as a variable in an 
SQLRPGLE program.
The value of the variable (after removing the extra quote) is 
('BK','BE',RE').
My variable name is Bulk_Type.
The SQL statement is :
Select Count(*)  into :Nbr from workfile where type not in :Bulk_Type

The only records in my file all have a type of BK.
The SQL statement returns a value equal to all of the records 
in the file.
The SQLCOD is 0, SQLSTT is 0.

What do I need to do to make this work?

TIA,
 
Jeff Young 
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
IBM -e(logo) server Certified Systems Exper - iSeries 
Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical 
Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical 
Solutions Implementer V5R3

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) 
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.