RE: [sqr-users] Problem with SQR string concatenation ? Please help!

[James Womeldorf <jwomeldo@fastenal.com>]

I think you will have to use dynamic SQL here.  Imagine the SQL as you want
it to be executed on the database.  In my example it would be

select dummy from dual where dummy in ('A6','A7','X')

What you need to do is come up with the list of values EXACTLY as it appears
in this statement (complete with quotes), in this case

'A6','A7','X'

Create a variable that has that value, replace the values in the where
clause with that variable, and surround that variable with square brackets.
The square brackets tell SQR to evaluate the variable and 'plop' it into the
SQL where the variable is before compiling the SQL.

Try something like this.  I changed the field and table so it would run for
me.  It does NOT work without the [] since $agycodes is then considered to
be a SINGLE item, not a list.

let $Q = ''''
let $agycodes = $Q || 'A6'|| $Q || ','|| $Q || 'A7'|| $Q || ',' || $Q || 'X'
|| $Q
show $agycodes          ! Should give you ->      'A6','A7','X'
begin-select
dummy &testdummy    
    show '&testdummy ' &testdummy    
from dual                    
where dummy in ([$agycodes])
END-Select


Sorry if this is more than you wanted to know.  (I used to be an educator
and sometimes I just can't help myself!)
Good luck
Jim

-----Original Message-----
From: Ferdinand de Lesseps [mailto:ezfred0131@yahoo.com]
Sent: Wednesday, January 29, 2003 6:30 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Problem with SQR string concatenation ? Please
help!


I formed a string concatenating few of the command
line arguments. I checked for the wellformedness of
the resultant string, it was exactly what I wanted..

I went into a procedure, there I concatenated the
string with parenthesis; it looked good. But, when I
try using this string inside sql select statement
(begin-select...end-select), it won't be
recognized..don't know why..

Code fragments:
>>>string<<<
$agycodes = 'A6'||','||'A7'||','||'M4' =>
'A6','A7','M4'
>>>inside procedure<<<
let $agycodes = '('||$agycodes||')'
#debug show $agycodes.....would give me
('A6','A7','M4') which is what I want.

>>>inside select statement<<<
begin-select
agy_wc &agywc
agt_wc &agtwc
agt_seq &agtseq
.....
.....
from t_agents .....
where
agy_wc = $agycodes and .....
.....
end-select

Works when I hard-code ('A6','A7','M4') in place of
$agycodes, but won't work otherwise. It won't give me
any error or exception, however the sqr will terminate
immediately, which makes it even more difficult to
debug..Any idea what's happening ? Is it something to
do with parens, commas ? Would really appreciate any
help..

Thanks in advance..

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

_______________________________________________
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