delete missing operand

[fanlinux90]

I must delete from the invent table when class has one of the following values: 'SUS','TB','CAD', 'EF', 'ND' and estado = normal


DELETE FROM invent where INLIST(UPPER(ALLTRIM(class)), 'SUS','TB','CAD', 'EF', 'ND' ) and UPPER(estado)=='NORMAL'

when i run the command i get the error missing operand

 

[TamarGranor]

There's one other important difference between Xbase DELETE and SQL DELETE. SQL DELETE uses record locks, while Xbase DELETE locks the whole table if there's more than one record to delete.

Tamar

 

[Chris Miller]

I see, thanks Tamar,

and it will be much easier to get locks on single records, especially if these are deleted, which menas there is low interest in them and likely no lock on single records, but likely there can be locks on other records for other reasons, that prevent xBase delete to get the table lock for a moment.

I can't remember a situation where an xbase DELETE failed, because there is alwas the mechanism of retries, if a table lcck isn't possible momentarily.

Chriss

 

[GriffMG]

I think I may have tripped over the problem

Is there an SQL INLIST() function or is it IN LIST?

**edit** I do note that Chriss touched on this earlier and that for a native table the SQL would use INLIST(), but perhaps not
if it is being passed through to MS SQL or MySQL?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

Griff, I don't think there is an equivalent of INLIST() (I might be wrong) but SQL does support this syntax:
[tt]
SELECT ... FROM ... WHERE SomeField IN (val1, val2, ... )[/tt]

and of course also:
[tt]
SELECT ... FROM ... WHERE SomeField IN (<a sub-query that returns one column>)[/tt]

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Hi Mike

That might well be why the error is reported as 'missing operand' then as SQL could be looking at INLIST() as an array
and needs something to compare it with... maybe. Shame OP appears to have lost the will...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[mjcmkrsr]

Hi,

In this case both IN and INLIST() work correctly with SQL (see code below).

Create CURSOR invent (class c(5), estado C(10))

Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("TB", "Normal")
Insert into invent values ("CAD", "AbNormal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("ND", "Normal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("TB", "Normal")
Insert into invent values ("CAD", "AbNormal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("ND", "Normal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("TB", "Normal")
Insert into invent values ("CAD", "AbNormal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("ND", "Normal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("TB", "Normal")
Insert into invent values ("CAD", "AbNormal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("ND", "Normal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("TB", "Normal")
Insert into invent values ("CAD", "AbNormal")
Insert into invent values ("SUS", "AbNormal")
Insert into invent values ("NSD", "Normal")

DELETE FROM invent where UPPER(ALLTRIM(class)) IN ('SUS', 'TB', 'CAD', 'EF', 'ND' ) and UPPER(estado)=='NORMAL'

WAIT WINDOW + TRANSFORM(_Tally) + " records deleted" TIMEOUT 2

RECALL all

WAIT WINDOW + TRANSFORM(_Tally) + " records recalled" TIMEOUT 2

DELETE FROM invent where INLIST(UPPER(ALLTRIM(class)), 'SUS', 'TB', 'CAD', 'EF', 'ND' ) and UPPER(estado)=='NORMAL'

WAIT WINDOW + TRANSFORM(_Tally) + " records deleted" TIMEOUT 2

BROWSE 

USE 

RETURN

However please be aware of some critical behavior changes in VFP9

SQL SELECT IN (Value_Set) Clause

In previous versions of Visual FoxPro, the IN (Value_Set) clause for the WHERE clause in the SQL SELECT command is mapped to INLIST( ) function. In the current release, Visual FoxPro might stop evaluating values and expressions in the Value_Set list when the first match is found. Therefore, if the IN clause is not Rushmore-optimized, you can improve performance by placing values most likely to match in the beginning of the Value_Set list. For more information, see the description for the IN clause in the SELECT - SQL Command topic and the INLIST( ) Function.

Conversion of INLIST( ) Function in the Query Designer and View Designer

In previous versions of Visual FoxPro, the Query Designer and View Designer convert INLIST( ) function calls in the WHERE clause of the SQL SELECT command into IN (Value_Set) clauses. In the current release, this conversion no longer occurs due to the differences between INLIST( ) and the SQL IN clause. INLIST( ) remains restricted to 24 arguments. For more information, see the description for the IN clause in the SELECT - SQL Command topic and the INLIST( ) Function.

Nevertheless Fanlinux might want to keep us posted instead of letting us guess

hth

MarL

 

[GriffMG]

Marl

Would that be different if the SQL call was made to a remote database - not via a vfp native database or the VFPOLEDB/ODBC driver

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

That might well be why the error is reported as 'missing operand' then as SQL could be looking at INLIST() as an array
and needs something to compare it with... maybe.

Well, that might be right Griff, but I think it's safe to assume that the OP's code is running against DBFs. If a back-end reported a missing operand, it wouldn't show up as VFP error message. But who knows what the OP really intended.

Shame OP appears to have lost the will...

I agree. It's not at all helpful when someone posts a question and then doesn't bother to acknowledge or comment on the replies.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Gary Sutherland]

Although SELECT SQL does respect the SET DELETE filter setting.

Regards
Gary