open query help

Adding numerical field to filters in openquery is as easy as typing the
number but I am having major problems using strings.

I have tried a few solutions including the usual concantenations but no
solution. Is there a simple way of including strings with the filters
for a query below, possibly without declaring variables and using the
string directly in the code? I have included one of the solutions I was
given..

Thankyou for any help ...

DECLARE @.SQL VARCHAR(8000) DECLARE @.strVar VARCHAR(1000)
SET @.strVar = '22'
SET @.SQL = '
select * FROM OPENQUERY(ISERIES,
"SELECT OOLINE.OBWHLO, OOLINE.OBCUNO, OCUSMA.OKCUNM, OOLINE.OBORNO,
OOLINE.OBPONR, OOLINE.OBITNO, OOLINE.OBDWDZ, OOLINE.OBORQA,
OOLINE.OBATV3, OOLINE.OBATV4,
OOLINE.OBATV5, OOLINE.OBROUT, OOHEAD.OADLSP, OOHEAD.OADSTX,
OCUSAD.OPCUNM, OCUSAD.OPCUA1, OCUSAD.OPCUA2, OCUSAD.OPCUA3,
OCUSAD.OPCUA4, MHDISH.OQDLIX, OOHEAD.OAFACI, CFACIL.CFFACN,
OOHEAD.OARGDT, OOHEAD.OAPRTX, mitbal.MBPUIT, mitbal.MBSUWH,
OOHEAD.OARESP, OOHEAD.OARGTM, OOLINE.OBORST, mitbal.MBOPLC,
(OOLINE.OBATV0) As mark_no,(OOHEAD.OACUOR) As po_no,OOLINE.OBATV6) As
cust_bund_ID,OOLINE.OBFACI,(OOLINE.OBRORN) As DO_no, MHDISH.OQDSDT

FROM
mvxcdtprod.OOHEAD oohead INNER JOIN mvxcdtprod.OCUSAD ocusad ON
OOHEAD.OACONO = OCUSAD.OPCONO
AND OOHEAD.OACUNO = OCUSAD.OPCUNO
AND OOHEAD.OAADID = OCUSAD.OPADID

INNER JOIN mvxcdtprod.CFACIL cfacil ON OOHEAD.OACONO = CFACIL.CFCONO
AND OOHEAD.OADIVI = CFACIL.CFDIVI
AND OOHEAD.OAFACI = CFACIL.CFFACI

INNER JOIN mvxcdtprod.OCUSMA ocusma ON OOHEAD.OACONO =
OCUSMA.OKCONO AND OOHEAD.OACUNO = OCUSMA.OKCUNO

INNER JOIN mvxcdtprod.OOLINE ooline ON OOHEAD.OACONO =
OOLINE.OBCONO AND OOHEAD.OAORNO = OOLINE.OBORNO

INNER JOIN mvxcdtprod.MITBAL mitbal ON OOLINE.OBCONO =
MITBAL.MBCONO AND OOLINE.OBWHLO = MITBAL.MBWHLO
AND OOLINE.OBITNO = MITBAL.MBITNO

INNER JOIN mvxcdtprod.MITMAS mitmas ON OOLINE.OBCONO =
MITMAS.MMCONO AND OOLINE.OBITNO = MITMAS.MMITNO

LEFT OUTER JOIN mvxcdtprod.MHDISL mhdisl ON MHDISL.URRIDN =
OOLINE.OBORNO
AND MHDISL.URRIDL/100 = OOLINE.OBPONR
AND MHDISL.URCONO = OOLINE.OBCONO

LEFT OUTER JOIN mvxcdtprod.mhdish mhdish ON OOLINE.OBCONO =
MHDISH.OQCONOAND MHDISL.URDLIX = MHDISH.OQDLIX

WHERE mitbal.MBOPLC = 3 and OOLINE.OBORST = ('' + @.strVar +
'')'') '

EXEC(@.SQL)(david.good@.stramit.com.au) writes:
> Adding numerical field to filters in openquery is as easy as typing the
> number but I am having major problems using strings.
> I have tried a few solutions including the usual concantenations but no
> solution. Is there a simple way of including strings with the filters
> for a query below, possibly without declaring variables and using the
> string directly in the code? I have included one of the solutions I was
> given..

I'm not really sure what your question is, but due to the rigid syntax
of OPENQUERY, you often end up with several layers of nested quotes,
and it can be very difficult to get it right. One comment to the query:

> select * FROM OPENQUERY(ISERIES,
> "SELECT OOLINE.OBWHLO, OOLINE.OBCUNO, OCUSMA.OKCUNM,

This works if the setting QUOTED_IDENTIFIER is OFF. The default for this
settings (in most contexts) is ON. When ON, "" delimits identifiers, not
string literals. Setting this setting to OFF can indeed be useful for
this kind of exercises, since you get two different quote operators
to play with. (But obsever that turning off this setting is not good
if there are indexed views or indexed computed columns around.)

One alternative is to build the string piece by piece. To this end
the function quotestring() may be helpful, see
http://www.sommarskog.se/dynamic_sql.html#quotestring.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp