Define variable for values used in IN function

[samir shah]

Hi ALL

I have a query

sele * from lmrm0309 where cextno in ('7392') and !(cdialno
in('92','6','93'))

now I want to specify a variable for the list of values

i.e ('92','6','93')

so that I can specify the list of values to a variable which will be used
with IN() and than can execute the query

Please suggest

Regards

Samir Shah

[Stefan Wuebbe]

"samir shah" <shahs...@hotmail.com> wrote in message
news:Oa5c6Q2u...@TK2MSFTNGP06.phx.gbl...

> Hi ALL
>
> I have a query
>
> sele * from lmrm0309 where cextno in ('7392') and !(cdialno in('92','6','93'))
>
> now I want to specify a variable for the list of values
>
> i.e ('92','6','93')
>
> so that I can specify the list of values to a variable which will be used with IN() and
> than can execute the query

Hi Samir -

In Visual FoxPro 9 you can for example ...

CREATE CURSOR lmrm0309 (cextno c(10), cdialno c(10))
INSERT INTO lmrm0309 VALUES ('7392', '92')
INSERT INTO lmrm0309 VALUES ('7392', '6')
INSERT INTO lmrm0309 VALUES ('7392', '93')
INSERT INTO lmrm0309 VALUES ('7392', '94')
SELECT * FROM lmrm0309 WHERE cextno In ('7392') And cdialno Not In ('92','6','93')

... either use a &macro "parameter":

Local lcIn
lcIn = "'92','6','93'"
SELECT * FROM lmrm0309 WHERE cextno In ('7392') And cdialno Not In (?&lcIn)

Or use something that I think is called a "tally table":

CREATE CURSOR tallytable (c c(10))
INSERT INTO tallyTable VALUES ('92')
INSERT INTO tallyTable VALUES ('6')
INSERT INTO tallyTable VALUES ('93')
SELECT * FROM lmrm0309 WHERE cextno In ('7392') And cdialno Not In (SELECT c FROM
tallyTable)

hth
-Stefan

--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------