×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

delete missing operand

delete missing operand

delete missing operand

(OP)
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

RE: delete missing operand

Hi,

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

MartinaJ

mJindrova

RE: delete missing operand

Looks perfectly OK to me. Are you sure you copied the command into your forum post correctly?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: delete missing operand

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.

RE: delete missing operand

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 Smileing

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

RE: delete missing operand

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 Smileing

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

RE: delete missing operand

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'

RE: delete missing operand

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 Smileing

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

RE: delete missing operand

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

RE: delete missing operand

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 Smileing

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

RE: delete missing operand

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

RE: delete missing operand

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

RE: delete missing operand

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

RE: delete missing operand

I'd be guessing that the SQL delete does not respect the filter condition

Regards

Griff
Keep Smileing

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

RE: delete missing operand

Quote:

I'd be guessing that the SQL delete does not respect the filter condition

That is correct. It'a also true of UPDATE and SELECT.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: delete missing operand

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

RE: delete missing operand

And just to be clear, none of this affects Fanlinux90's original question, which refers to a "missing operand" error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: delete missing operand

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 field IS NULL clause would only work as a part of a WHERE clause and would need to be done with ISNULL(field) 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

RE: delete missing operand

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

RE: delete missing operand

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

RE: delete missing operand

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

RE: delete missing operand

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 Smileing

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

RE: delete missing operand

Griff, I don't think there is an equivalent of INLIST() (I might be wrong) but SQL does support this syntax:

SELECT ... FROM ... WHERE SomeField IN (val1, val2, ... )


and of course also:

SELECT ... FROM ... WHERE SomeField IN (<a sub-query that returns one column>)


Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: delete missing operand

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 Smileing

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

RE: delete missing operand

Hi,

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

CODE -->

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

Quote (- from he Help file)



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

RE: delete missing operand

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 Smileing

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

RE: delete missing operand

Quote:

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.

Quote:

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

RE: delete missing operand

Although SELECT SQL does respect the SET DELETE filter setting.

Regards
Gary

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close