INLIST using $

[txgeekgirl1]

I am trying to do a "macro substitution" type thing and have some pretty long Where clauses.

I am trying to shorten them by using something like this:

lcfilter = "LEFT(svccode, 3) <> '103' ;"
lcfilter = lcfilter + "and LEFT(svccode, 3) <> '207' "
lcfilter = lcfilter + "and LEFT(svccode, 6)!$('1102AM', '1102BM','2102AM','2102BM'")

Does anyone know the rule for using a NOT before the $. Should it be a <> or something else?

 

[JRB-Bldr]

The way that I use a NOT before a $ is as follows:

IF !(cText $ "ABC,DEF,GHI")

I have not tried it in any other way, but it works fine for me like that.

Good Luck,
JRB-Bldr

 

[jimstarr]

The NOT comes before the entire expression, not next to the $.

Workable options:

!(cText $ "ABC,DEF,GHI")
!cText $ "ABC,DEF,GHI"
NOT (cText $ "ABC,DEF,GHI")
NOT cText $ "ABC,DEF,GHI"

BTW, your example is a hybrid of the $ operator and the INLIST() function, and is syntactically incorrect.


Jim

 

[Mike Lewis]

Just to add to Jim's answer, the following would do what you want:

lcfilter = lcfilter + "AND NOT ;
  INLIST(LEFT(svccode, 6), ;
  '1102AM', '1102BM', '2102AM', '2102BM')"

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

If you set/keep ANSI Off you can even put '103' and '207' in the list, and no need to do LEFT(svccode,6). That's the advantage of VFP's way to compare strings only up to the length of the right side string:

? '10345' = '103'
but
? '103' = '10345'

So with ANSI OFF the following where clause would do:

where svccode NOT IN ('103','207','1102AM', '1102BM','2102AM','2102BM')

Also I didn't use INLIST here, but SQL's IN operator, INLIST is limited to 27 values, th IN operator can handle more values.

Even more scalable would be a join with a cursor containing all the values you want to check against:

create cursor curValues (vValue V(6))
insert into curValues Value ('103')
insert into curValues Value ('207')
insert into curValues Value ('1102AM')
insert into curValues Value ('1102BM')
insert into curValues Value ('2102AM')
insert into curValues Value ('2102BM')

Select * from thetable where svccode NOT IN (select vValue from curValues)

The advantage is, that in a filter form you can prepare a list of values to exclude (or exclude when skipping the NOT)in a cursor very easy with a mover (two listboxes, left with options, right with selections) and then take the selection cursor of the right listbox to filter the table.

Bye, Olaf.

 

[Mike Lewis]

Also I didn't use INLIST here, but SQL's IN operator, INLIST is limited to 27 values, th IN operator can handle more values.

True, but only in VFP 9.0. In earlier versions, the limit on the IN (<value list>) clause was 24.

Also, I think the IN constructs (with either a value list or a subquery) are both better optimised than INLIST() (but I can't find any reference to confirm that).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[markros]

I think they both are optimized, but of of course using IN syntax is preferrable.

Hovewer, since ANSI OFF is not SQL Server compatible, it limits this solution to VFP only.

If we want SQL Server compatible solution we may write this as

select * from myTable where myCodeField not like 'FirstVal%' and not like 'SecondVal%' etc.

 

[txgeekgirl1]

Thank you all so much. This has been quite frustrating as I currently have three sets of svccodes to filter and am trying to keep within the parameters of characters for a string. It's busting my nestled IF statements. I will let you know how it goes!

 

[JRB-Bldr]

Olaf's "Even more scalable" suggestion of building a reference cursor (curValues) would be a good approach.

You could add to the reference cursor any string combination you wanted (INSERT INTO ...) without running into string length limitations or having to use nested IF statements.

Then once the reference cursor was built, you could do, again as he suggested, merely run the SQL Query on the result.

Good Luck,
JRB-Bldr

 

[txgeekgirl1]

OK - I am atleast not bombing out but can anyone tell me where I went wrong?

IF plTCOMI THEN
lcfilter = "Kumbaya"
wait window lcfilter
ELSE
IF plKids THEN
CREATE CURSOR curValues (vValue V(6))
insert into curValues Value ('103')
insert into curValues Value ('207')
insert into curValues Value ('1102AM')
insert into curValues Value ('1102BM')
insert into curValues Value ('2102AM')
insert into curValues Value ('2102BM')
ELSE
Wait window "Hello"
ENDIF
ENDIF

SELECT ALL;
txpatid, ;
SVCDATE, ;
MedRecNo, ;
CLNAME, ;
SvcPack, ;
SVCPROG, ;
SVCTHID, ;
SVCTHNAME, ;
SVCCODE, ;
SVCDESC, ;
Tduratn ;
FROM SSVCSLDA_ ;
LEFT OUTER JOIN curvalues ;
ON svccode = vValue ;
Where svccode NOT IN vValue ;
ORDER BY txpatid, SVCCODE, SVCDATE ;
INTO CURSOR SSVCSLD_
BROWSE
RETURN

 

[markros]

If you want to exclude these values, then

IF plTCOMI THEN
    lcfilter = "Kumbaya"
    wait window lcfilter
ELSE
    IF plKids THEN
    CREATE CURSOR curValues (IdField I autoinc, vValue V(6))
    insert into curValues (vValue)  Values ('103')
    insert into curValues (vValue)  Values ('207')
    insert into curValues (vValue)  Values('1102AM')
    insert into curValues (vValue)  Values ('1102BM')
    insert into curValues (vValue)  Values ('2102AM')
    insert into curValues (vValue)  Values ('2102BM')
    ELSE
         Wait window "Hello"
    ENDIF
ENDIF    
        
SELECT ALL;
    txpatid, ;
    SVCDATE, ;
    MedRecNo, ;
    CLNAME, ;
    SvcPack, ;
    SVCPROG, ;
    SVCTHID, ;
    SVCTHNAME, ;
    SVCCODE, ;
    SVCDESC, ;
    Tduratn ;
    FROM SSVCSLDA_ ;
        LEFT OUTER JOIN curvalues ;
        ON svccode = vValue ;
    Where curValues.PKField IS NULL ;
    ORDER BY txpatid, SVCCODE, SVCDATE ;
    INTO CURSOR SSVCSLD_
    BROWSE
    RETURN

 

[markros]

I meant IDField here - you don't actually need it, you can also use where curValues.vValue IS NULL

 

[Mike Lewis]

Txgeekgirl1,

I think the idea was to use the IN operator with a sub-SELECT. In other words, something like this:

SELECT ALL;
    txpatid, ;
    SVCDATE, ;
    MedRecNo, ;
    CLNAME, ;
    SvcPack, ;
    SVCPROG, ;
    SVCTHID, ;
    SVCTHNAME, ;
    SVCCODE, ;
    SVCDESC, ;
    Tduratn ;
    FROM SSVCSLDA_ ;
    WHERE svcCode[b] NOT IN (SELECT vValue FROM CurValues)[/b];
    ORDER BY txpatid, SVCCODE, SVCDATE ;
    INTO CURSOR SSVCSLD_

Also, I think Olaf's idea of creating a cursor to hold the codes was only meant if you had a very large number of codes to select from (or if such a cursor already exists).

I question the benefit of creating the cursor solely for this one SELECT. Personally, I'd go back with my earlier suggestion:

NOT ;
  .... INLIST(svccode), ;
'1102AM', '1102BM', '2102AM', '2102BM'

On the other hand, if you didn't know which values to select from at coding time, that would be a good reason to create a separate cursor,

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[markros]

NOT IN and LEFT JOIN .. where RightTable.SomeField IS NULL are equavalent, but the later performs better.

 

[jimstarr]

You can't say

Where svccode NOT IN vValue

It needs to be (as in Olaf's code)

where svccode NOT IN (select vValue from curValues)




Jim

 

[txgeekgirl1]

Here are my current exclusion lists... these currently live in 6 different programs which I am attempting to combine into 1 because 90 % of the code is the same and I am sick and tired of making changes to 6 different programs everytime someone has a wild hair. As you can see... they are long. I am going back to Olaf's code...

I have tried the this and it busts:
where svccode NOT IN ('103','207','1102AM', '1102BM','2102AM','2102BM')

plKids
Where left(svccode, 3) <> "103" ;
and left(svccode, 3) <> "207" ;
and left(svccode, 6) <> "1102AM" ;
and left(svccode, 6) <> "1102BM" ;
and left(svccode, 6) <> "2102AM" ;
and left(svccode, 6) <> "2102BM" ;
and left(svccode, 5) <> "2506J" ;
and left(svccode, 4) <> "242J" ;
and left(svccode, 5) <> "2506H" ;
and left(svccode, 4) <> "242H" ;
and left(svccode, 4) <> "252H" ;
and left(svccode, 1) > "0" ;
and left(svccode, 1) < "3" ;
and left(svccode, 2) <> "16" ;
and left(svccode, 2) <> "26" ;
and left(svccode, 3) <> "221" ;
Adults
Where left(svccode, 3) <> "103" ;
and left(svccode, 3) <> "207" ;
and left(svccode, 6) <> "1102AM" ;
and left(svccode, 6) <> "1102BM" ;
and left(svccode, 1) > "0" ;
and left(svccode, 1) < "3" ;
and left(svccode, 2) <> "16" ;
plTCOMI
Where LEFT(svccode, 3) = "142" ;
OR LEFT(svccode, 3) = "152" ;
OR LEFT(svccode, 4) = "1101" ;
OR LEFT(svccode, 4) = "1102" ;
OR LEFT(svccode, 4) = "1506" ;
OR LEFT(svccode, 4) = "1103" ;

 

[Olaf Doschke]

You need to SET ANSI OFF. Please read the whole post to understand it.

Bye, Olaf.

 

[cbasoz]

I would suggest:

IF plTCOMI THEN
    lcfilter = "Kumbaya"
    wait window lcfilter
ELSE
    IF plKids THEN
    CREATE CURSOR curValues (vValue V(6))
    insert into curValues Value ('103')
    insert into curValues Value ('207')
    insert into curValues Value ('1102AM')
    insert into curValues Value ('1102BM')
    insert into curValues Value ('2102AM')
    insert into curValues Value ('2102BM')
    ELSE
         Wait window "Hello"
    ENDIF
ENDIF    
        
SELECT ; 
    txpatid, ;
    SVCDATE, ;
    MedRecNo, ;
    CLNAME, ;
    SvcPack, ;
    SVCPROG, ;
    SVCTHID, ;
    SVCTHNAME, ;
    SVCCODE, ;
    SVCDESC, ;
    Tduratn ;
    FROM SSVCSLDA_ ;
    WHERE NOT exist ;
    	(SELECT * FROM curvalues WHERE curvalues.vValue = SSVCSLDA_.svccode) ;
    ORDER BY txpatid, SVCCODE, SVCDATE ;
    INTO CURSOR SSVCSLD_
    BROWSE
    RETURN

-Exist has a better optimization.
-Using intermediate cursors is not subject to limits of IN
-Could be passed over the wire as an XML parameter using VFPOLEDB
-Code is also more portable between backends

PS: Use = or == to force implicit ANSI ON if it is against a VFP data.

My .02 cents

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

I would suggest:

IF plTCOMI THEN
    lcfilter = "Kumbaya"
    wait window lcfilter
ELSE
    IF plKids THEN
    CREATE CURSOR curValues (vValue V(6))
    insert into curValues Value ('103')
    insert into curValues Value ('207')
    insert into curValues Value ('1102AM')
    insert into curValues Value ('1102BM')
    insert into curValues Value ('2102AM')
    insert into curValues Value ('2102BM')
    ELSE
         Wait window "Hello"
    ENDIF
ENDIF    
        
SELECT ; 
    txpatid, ;
    SVCDATE, ;
    MedRecNo, ;
    CLNAME, ;
    SvcPack, ;
    SVCPROG, ;
    SVCTHID, ;
    SVCTHNAME, ;
    SVCCODE, ;
    SVCDESC, ;
    Tduratn ;
    FROM SSVCSLDA_ ;
    WHERE NOT exists ;
    	(SELECT * FROM curvalues WHERE curvalues.vValue = SSVCSLDA_.svccode) ;
    ORDER BY txpatid, SVCCODE, SVCDATE ;
    INTO CURSOR SSVCSLD_
    BROWSE
    RETURN

-Exists has a better optimization.
-Using intermediate cursors is not subject to limits of IN
-Could be passed over the wire as an XML parameter using VFPOLEDB
-Code is also more portable between backends

PS: Use = or == to force implicit ANSI ON if it is against a VFP data.

My .02 cents

Cetin Basoz
MS Foxpro MVP, MCP

 

[txgeekgirl1]

Just wanted to show you all what I ended up with that worked.... Thank you all for your suggestions and help.

IF TCOMI THEN
CREATE CURSOR curValues (vValue c(6))
INSERT INTO curValues (vValue) VALUE('142')
INSERT INTO curValues (vValue) VALUE('152')
INSERT INTO curValues (vValue) VALUE('1101')
INSERT INTO curValues (vValue) VALUE('1102')
INSERT INTO curValues (vValue) VALUE('1506')
INSERT INTO curValues (vValue) VALUE('1103')
ELSE
IF Kids THEN
CREATE CURSOR curValues (vValue c(6))
INSERT INTO curValues (vValue) VALUE('103')
INSERT INTO curValues (vValue) VALUE('207')
INSERT INTO curValues (vValue) VALUE('1102AM')
INSERT INTO curValues (vValue) VALUE('1102BM')
INSERT INTO curValues (vValue) VALUE('2102AM')
INSERT INTO curValues (vValue) VALUE('2102BM')
INSERT INTO curValues (vValue) VALUE('2506J')
INSERT INTO curValues (vValue) VALUE('242J')
INSERT INTO curValues (vValue) VALUE('2506H')
INSERT INTO curValues (vValue) VALUE('242H')
INSERT INTO curValues (vValue) VALUE('252H')
ELSE
CREATE CURSOR curValues (vValue c(6))
INSERT INTO curValues (vValue) VALUE('103')
INSERT INTO curValues (vValue) VALUE('207')
INSERT INTO curValues (vValue) VALUE('1102AM')
INSERT INTO curValues (vValue) VALUE('1102BM')
ENDIF
ENDIF

********************************************************
* Because TCOMI had to defy everything else, I created *
* another lcfilter to populate the Where clause.*
**********************************************************


IF TCOMI THEN
lcfilter = "svccode = vValue"
ELSE
IF Kids THEN
lcfilter = "svccode <> vValue AND LEFT(SVCCODE, 1)>'0' AND LEFT(svccode, 1)<'3' AND LEFT(svccode, 2)<>'16' "
lcfilter = lcfilter + "AND LEFT(svccode, 2)<>'26' AND LEFT(svccode, 3)<>'221' "
ELSE
lcfilter = "svccode <> vValue AND LEFT(SVCCODE, 1)>'0' AND LEFT(svccode, 1)<'3' AND LEFT(svccode, 2)<>'16' "
ENDIF
ENDIF

SELECT ALL;
txpatid, ;
SVCDATE, ;
MedRecNo, ;
CLNAME, ;
SvcPack, ;
SVCPROG, ;
SVCTHID, ;
SVCTHNAME, ;
SVCCODE, ;
SVCDESC, ;
Tduratn ;
FROM SSVCSLDA_ ;
LEFT OUTER JOIN curValues ;
ON svccode = vValue ;
WHERE &lcfilter ;
ORDER BY txpatid, SVCCODE, SVCDATE ;
INTO CURSOR SSVCSLD_