Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

delete missing operand

Status
Not open for further replies.
fanlinux90

fanlinux90

Programmer
Joined
Oct 31, 2022
Messages
22
Location
CO
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
 
Sort by date Sort by votes
Hi,

a command fail in program only or in "Command" window too?

MartinaJ

mJindrova
 
Upvote 0 Downvote
I agree with Mike, but I doubt the error was removed by copying from code to forum.
I suspect the line of error is another one, typical, if someone hasn't an error handler established.

The line has perfectly balanced brackets, no wrong nesting, all functions have the right number of parameters, but if something like that would be wrong you'd have an error about a missing bracket or wrong type or count of parameters, not a missing operand. An operand in code is about an operation like a mathematical or logical operation in the form operand operation operand. The expression a= would cause a missing operand error, because that's missing the righthand side operand. Your expression is nothing like that.

I bet it's an error of an IF statement that is right before this. Am I right, fanlilnux90?

Please for sake of knowing - attention: knowing, really explicitly knowing - where the error is, you can use this minimal error handling:
Code: 
ON ERROR Messagebox(Textmerge('Errror in <<Program()>>, line no <<LineNo()>>: <<Message()>>'))
And then go to that line in that program or method/event of a form, a control, or whatever, and copy that here.

Chriss

PS: another typical mistake, even IF you have error handling established is you take the error report from an EXE and lookup code in your current project state, which you may have changed already since the build you did for that EXE version. That's were source code versioning comes in and you analyze error reports with the code as it was when building this EXE - not as it is now.

PPS: The error might be caused by executing that line, but is in a stored procedure of a DBC that is triggered ON DELETE of records. It's less likely because I don't think if you would use such advanced features of VFP you wouldn't be able to analyze an error like that. You might maintain a project that has this feature without knowing. But the simple error handling would point that out, too,, a the line of error then isn't the DELETE FROM, but the line of code in the ON DELETE trigger procedure.
 
Upvote 0 Downvote
I would break the command down into shorter statements and test them individually

Start with a SELECT rather than a DELETE though

Code: 
SELECT FROM invent into cursor myCursor
SELECT FROM invent where UPPER(estado)=='NORMAL' into cursor myCursor
SELECT FROM invent where INLIST(UPPER(ALLTRIM(class)), 'SUS','TB','CAD', 'EF', 'ND' )  into cursor myCursor
SELECT FROM invent where INLIST(UPPER(ALLTRIM(class)), 'SUS','TB','CAD', 'EF', 'ND' ) and UPPER(estado)=='NORMAL' into cursor myCursor

See how far you get

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
​
 
Upvote 0 Downvote
Good thought Mark

'NORMAL' is a string, so that shouldn't hurt... perhaps:

Code: 
SELECT FROM invent into cursor myCursor
SELECT FROM invent where UPPER(invent.estado)=='NORMAL' into cursor myCursor
SELECT FROM invent where INLIST(UPPER(ALLTRIM(invent.class)), 'SUS','TB','CAD', 'EF', 'ND' )  into cursor myCursor
SELECT FROM invent where INLIST(UPPER(ALLTRIM(invent.class)), 'SUS','TB','CAD', 'EF', 'ND' ) and UPPER(invent.estado)=='NORMAL' into cursor myCursor

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
​
 
Upvote 0 Downvote
I prefer to use the Foxpro Delete command rather than the SQL variety.

e.g. DELETE ALL FOR INLIST(UPPER(ALLTRIM(class)),'SUS','TB','CAD','EF','ND') AND UPPER(estado)=='NORMAL'

 
Upvote 0 Downvote
I just did a little test with a table where I added a text field called class.

Code: 
** works
? class
** works
? INLIST(class,"a")
** works
delete for inlist(class,"a")
** fails
SELECT where !EMPTY(class)
** fails
SELECT where inlist(class,"a")

Even preceding the field name with the table name fails in a SQL style select/update/delete from

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
​
 
Upvote 0 Downvote
Well, Griff...

SELECT where !EMPTY(class) and SELECT where inlist(class,"a") are invalid/incomplete SQL commands, that has nothing to do with the original problem and isn't even caused by using a reserved word as a field name.

It would need to be

Code: 
Create Table invent (class char(5) null)
Insert into invent values (.null.)
Insert into invent values ('a')
** works
? class
** works
? INLIST(class,"a")
** works
SELECT * from invent where !EMPTY(class)
** works
SELECT * from invent where inlist(class,"a")
** works
delete for inlist(class,"a")
** works
delete from invent where inlist(class,"a")

A little quiz for you, Griff and Gary Sutherland. There is a difference in using the two forms of DELETE, and I don''t mean that the xbase DELETE FOR is shorter. Can you tell what's the difference?

Chriss
 
Upvote 0 Downvote
Well Chriss, I can see you are right about my select test not being formed right, and if I do the * from it does work.

The main difference between the two (SQL/dBase) seems to me that there is no concept of a current work area in SQL, so
you have to specify the table in the select - which I didn't do in my quick tests above.

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
​
 
Upvote 0 Downvote
Yes, SQL does not select from the current workarea, you have to have the FROM clause, usually FROM tablename.
I was thinking of another more important difference, though.

I give you a hint: SQL does accept a tablename it can find in the currently open and SET DBC, it also takes the alias name of a workarea. In the end, SQL will always do its query in a new workarea, though, i.e. it opens the table if it's not open at all, or it uses the workarea again. Now just think of the consequences of that:.

Btw: SQL tidies up all temporarily used workareas except the result set workarea it creates. Alias "Query", if you don't specify an INTO clause
The motivation is not to show that SQL is superior or not, just a differece you also have to keep in mind, always. On top of the difference, that SQL uses new workareas to work out the query result, but related to it.

Chriss
 
Upvote 0 Downvote
Also the default scope for DELETE SQL is ALL whereas you have to specify it for the FoxPro DELETE, and the table doesn't need to already be open when you issue the DELETE SQL command so long as you properly specify it in the FROM clause.

Regards
Gary
 
Upvote 0 Downvote
Also true, also not what I'm after

Explain this difference of _tally:
Code: 
* preparation
Cd GetEnv("TEMP")
Set deleted on
Create Table test free (number integer, class char(5) null) 
Index on number tag number
Set Filter To number>1
Set Order To
Insert into test values (1,'a')
Insert into test values (2,'b')
Insert into test values (3,'c')
Insert into test values (4,'z')
Insert into test values (5,.null.)

* diffference demonstration
Clear
Delete For class<'z'
? _tally, 'deleted records by DELETE command'
Recall all
Delete from test where class<'z'
? _tally, 'deleted records by DELETE SQL query'

Chriss
 
Upvote 0 Downvote
I'd be guessing that the SQL delete does not respect the filter condition

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
​
 
Upvote 0 Downvote
Yes, and that's the difference you should have in mind, if you prefer the xbase DELETE command. I point out it's a difference, It's neither a flaw of DELETE nor SQL-DELETE, it's what you might want to make use of, but to me it's always cleaner and free of side effects to use SQL.

On the other side, you can make use of it and take it that way: Your DELETE or also LOCATE or BROWSE and other xbase commands simply only work on the records visible by the currently set FILTER, and that's what you should expect but also always have in mind. If not, you get into the danger of not knowing why your DELETE doesn't delete all those records it should delete.

Both behaviors are by design, none of them is worse or better, bad or good. You just need to know the difference. And you can make use of it by mixing both ways of deleting.

Chriss
 
Upvote 0 Downvote
Yes, that's true. I still suspect the error is in another line of code right before or after this DELETE. If it would be in it, it would be in the condition and then it doesn't matter whether it's in the FOR or in the Where condition. The evaluation of that is evaluation of that, no matter if FOR or WHERE. The conditions also have no clauses in them, which only work in case of SQL as a [tt]field IS NULL[/tt] clause would only work as a part of a WHERE clause and would need to be done with [tt]ISNULL(field)[/tt] in case of xbase DELETE FOR. On the contrary Fanlinux90 uses the INLIST function, which works in both cases. It could be done with IN (list of values) in the SQL DELETE, instead, but it does not have to be done that way just because you change from xBase to SQL Delete.

Chriss
 
Upvote 0 Downvote
On that basis, it would be helpful if Fanlinux90 could come back and let us know if any of our replies have been useful.

Fanlinux90, if you are reading this, please note that it has been over a week since you posted your question, since when we have heard nothing from you. At the very least, you might acknowledge our replies.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

IAmTheWolf
  • Question Question
Properties missing from properties sheet
Replies
3
Views
467
IAmTheWolf
IAmTheWolf
Vince Wen
  • Locked
  • Question Question
.dbf files mysteriously gone missing (or being deleted)
2 3
Replies
55
Views
5K
Vince Wen
Vince Wen
dylim
  • Locked
  • Question Question
List Out Missing Numbers of a Number Range
2 3
Replies
42
Views
3K
Chriss Miller
C
foxup
  • Locked
  • Solved
delete having more than 1 count
Replies
13
Views
1K
Grandchoir
Grandchoir
Bondjames
  • Locked
  • Question Question
missing acd copied to a new table
Replies
19
Views
1K
mjcmkrsr
mjcmkrsr

Part and Inventory Search

Sponsor

Back
Top