RE: [sqr-users] Replacement Variables and Oracle 9i "IN" clause

["Kent Schaetzly" <KSchaetzly@eprod.com>]

You need each variable enclosed in quotes and the parenthesis included.  Try 
something like...

Let $ids = 'IDs IN ('''||'123'||''','''||'456'||''','''||'789'||''')'

WHERE [$ids]

-----Original Message-----
From: sqr-users-bounces+kschaetzly=eprod.com@sqrug.org
[mailto:sqr-users-bounces+kschaetzly=eprod.com@sqrug.org]On Behalf Of
Matt Rogish
Sent: Thursday, March 11, 2004 2:21 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Replacement Variables and Oracle 9i "IN" clause


Hi all,

For some reason this does not work:

let $ids = '123, 456, 789, 1011, 1213'

begin-select
...
FROM mytable
INNER JOIN ( SELECT ...
                      FROM some_other_things
                     WHERE IDs IN( [$ids] )
                         AND stuff )
WHERE some_other_stuff
end-select

******* Error msg *******
(SQR 5528) ORACLE OCIStmtExecute error 936 in cursor 2:
    ORA-00936: missing expression

SQR shows the first part of the SQL statement but ends with:
WHERE IDs IN(



Nor does this work:
let $ids = '(123, 456, 789, 1011, 1213)'

begin-select
...
FROM mytable
INNER JOIN ( SELECT ...
                      FROM some_other_things
                     WHERE IDs IN [$ids] )
WHERE some_other_stuff
end-select

******* Error msg *******
(SQR 5528) ORACLE OCIStmtExecute error 921 in cursor 2:
    ORA-00921: unexpected end of SQL command

SQR shows the first part of the SQL statement but ends with:
WHERE IDs IN



Nor does this work:
let $id_where = 'IDs IN (123, 456...)'

begin-select
...
FROM mytable
INNER JOIN ( SELECT ...
                      FROM some_other_things
                     WHERE [$id_where]
                         AND stuff )
WHERE some_other_stuff
end-select

******* Error msg *******
(SQR 5528) ORACLE OCIStmtExecute error 921 in cursor 2:
    ORA-00921: unexpected end of SQL command

SQR shows the first part of the SQL statement but ends with:
WHERE


How can I do this?  Note that in all of these examples the parameter list 
really comes from the user in the form of an 'input' statement, but the 
results are the same if you hard-code a string.  The derived table in the 
INNER JOIN is also required (I can't figure out how to do it any other 
way), so I can't really restructure the SQL.


Thanks,

--
Matt  


_______________________________________________
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