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

[Dattatraya Shetty <datta_shetty@yahoo.com>]

Hi 
  I think your NBR is a Numeric Value, 

Try this 

let $NBR='(''' 
begin-select 
a.id   &id 
a.nbr  &nbr 
   let $NBR = $NBR || to_char(&nbr) || ''','''
from table a 
end-select 
let $NBR = $NBR || ''')'

  
--- Darrel Scott <darrel_1977@yahoo.com> wrote:
> 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


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

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