i have a problem with passing multiple values to a variable.
Here is the code

-SET &PW1='''' | 'test1.net' | ''',''' |'test2.net' | '''';


here is the error message i am getting :

ALL
'test1.net','test2.net'
ADRPTHTML
OTHSYSsms
(FOC1400) SQLCODE IS 102 (HEX: 00000066) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near
: 'test1'. [42000] Statement(s) could not be prepared. [] Deferred prepar
: e could not be completed.
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND
------------------------------------------------

I am thinking this happening becuase of the '.' inthe value of the variable.
I dont know how to avoid. I spent couple of hours on this , i did not found any solution.
Help is appreciated.

thanks
Rekha

This message has been edited. Last edited by: Kerry,

quote:

ALL
'test1.net','test2.net'
ADRPTHTML
OTHSYSsms

What is this part of? A SQL passthru SELECT statement? What are test1.net and test2.net, column names?

THIS IS THE PART OF THE sql QUERY THAT NEEDS TO BE PREPARED
THE VALUES 'TEST1.NET' AND 'TEST2.NET' WILL BE PASSED TO THE COLUMN DOMAIN THE TABLE LIKE THIS--

WHERE DOMAIN IN('TEST1.NET','TEST2.NET')

You need to provide more of the SQL statement for us to see how your values are embedded in it.

So my understanding is: you have a list of constant values you want to pass to an SQL "IN" structure to be probed against a table column. Is that correct?

You may use the sample code below which uses the CAR table:

-SET &PW1='''JAPAN'',''ENGLAND''';
SQL
select country, car, model, bodytype, seats
  from car
where country in (&PW1)
order by country, car
;
END

Cheers!

My sample above assumed you were using SQL passthru to get your data. If you are using a straight TABLE FILE command however, your condition should be adjusted a bit since you will be using a structure like

 WHILE column EQ val1 OR val2 [OR ...] 

which gets automagically translated to an IN structure when resolved into SQL.

-SET &PW1='''JAPAN'' OR ''ENGLAND''';
TABLE FILE CAR
PRINT MODEL
      BODYTYPE
      SEATS
BY COUNTRY
BY CAR
WHERE COUNTRY EQ &PW1
END

Hope that helps.