Select ... Where "in the list of"?

[beatlejus]

Hi,

Is it possible to include a list of arguments after the "where" function, e. g.

Select * from sampledatabase
where desc "in the list of" (desc1,desc2,desc3)? Ideally it should work as input variable (&).

Or does this only work in the form
"where desc = desc1 or desc = desc2"?

------------------
--
Rgds,
Norbert

 

[Marco Kalter]

If you specify the where clause like this:

where code in (&list)

the end user can enter something like

1,3,8

to search for codes 1, 3 and 8. The type of the &list substitution variable must be none (or omitted, which is equivalent).

------------------
Marco Kalter
Allround Automations

 

[beatlejus]

If I do that, e.g.

where iassalhead.docnum = iassalitem.docnum
and iassalitem.material like '%10121777%'
and iassalhead.country in (&list)

Input is: S, N, DK, SF or S,N,DK,SF
but I receive the error message "invalid column name"

Can you help?

------------------
--
Rgds,
Norbert

 

[Marco Kalter]

Note that the substitution variable will literally be replaced and the resuling SQL statement will be sent to the server. The error message is caused by the fact that there is no S, N, DK, or SF column. Assuming that Country is a character column, the end user must type something like:

'S','N','DK'

------------------
Marco Kalter
Allround Automations