RE: [sqr-users] Creating Comma Delimited Strings for "WHERE IN" statements

["Lynds,Rick" <rlynds@mwdh2o.com>]

You will have to use the "edit" function to convert the numeric value to a
string
i.e.

BEGIN-PROCEDURE ABC
let $FirstPass = 'Y'
BEGIN-Select
a.ID
a.NBR &NBR
if $FirstPass = 'Y'
let $FirstPass = 'N'
let $NBR = '(''' || edit(&NBR,'999.99')
else
let $NBR = $NBR || ''',''' || edit(&NBR,'999.99')
end-if
FROM TABLE A
END-Select


HTH..

Rick

-----Original Message-----
From: Darrel Scott [mailto:darrel_1977@yahoo.com]
Sent: Thursday, January 22, 2004 1:03 PM
To: sqr-users@sqrug.org
Subject: Re: [sqr-users] Creating Comma Delimited Strings for "WHERE IN"
statements


Don - 
 
Thanks, but I tried your suggestion and also received the below error (upon
compiling):
 
"(SQR 4048) Function or operator '||' must be a string or date argument."
 
- Darrel

don.barclay@srs.gov wrote:
This should do the trick...

BEGIN-PROCEDURE ABC
let $FirstPass = 'Y'
BEGIN-Select
a.ID
a.NBR &NBR
if $FirstPass = 'Y'
let $FirstPass = 'N'
let $NBR = '(''' || &NBR
else
let $NBR = $NBR || ''',''' || &NBR
end-if
FROM TABLE A
END-Select
if $FirstPass = 'N'
let $NBR = $NBR || ''')'
end-if
show $NBR
END-PROCEDURE

BEGIN-PROCEDURE ABC
BEGIN-Select
b.NBR
b.TEXT
FROM TABLE B
WHERE b.NBR in [$NBR]
END-Select
END-PROCEDURE

sqr-users-admin@sqrug.org wrote on 01/22/2004 03:13:30 PM:

> 
> Hello All...
> 
> I'm trying to automate a SQL process using SQR. Up to this point, 
> I've never had to deal with working with the "IN" portion of SQL 
> within SQR. How do I pass a list of returned variables into a comma 
> delimited string? Example:
> 
> BEGIN-PROCEDURE ABC
> BEGIN-Select
> a.ID
> a.NBR &NBR
> Move &NBR to $NBR
> FROM TABLE A
> END-Select
> END-PROCEDURE
> 
> Based on the above procedure, I need to format my $NBR string where 
> it now becomes ('$NBR','$NBR','$NBR','$NBR',....) for however many 
> instances of &NBR procedure ABC returns. This way I can then use the
> values in the below procedure...
> 
> BEGIN-PROCEDURE ABC
> BEGIN-Select
> b.NBR
> b.TEXT
> FROM TABLE B
> WHERE b.NBR in ($NBR)
> END-Select
> END-PROCEDURE
> 
> I will be doing this a few more times in my program and (for now) my
> values that I need passed into separate comma delimited strings are 
> number values.
> 
> Any assistance would be greatly appreciated.
> 
> Thanks,
> Darrel

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users