WHERE clause in SELECT does not work !? 2

[mensudb]

Hello,

I have very simple query:
SELECT * FROM pordtxn WHERE slip=lslip INTO CURSOR ctmp
where lslip is local variable (numeric)

Every time this statement is executed I am getting the whole table PORDTXN. I have used debuger, and when this line has been executed I got the message telling me that filter is "skipped". What is causing this behavior?

Thanks

Mensud

 

[craigsboyd]

Try exactly equals:

WHERE slip==lslip

[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[Mike Gagnon]

Or if the slip and lslip are characters you may try:

SELECT * FROM pordtxn WHERE allt(slip)=allt(lslip) INTO CURSOR ctmp

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ramani]

HI

It is likely that your local variable lslip is not available when the SELECT is executed. That is possible, if you have set that value in some other method or event.

To make sure that it is available, I suggest you chechk with one of the following ways..
1. Insert a WAIT statement prior to SELECT statement and get the lSlip displayed... as.. WAIT WINDOW STR(lSlip)

OR

2. declare the lslip as PUBLIC for a trial run and see if that solves the issue.

Another possibility is that the field slip is not numeric and you are trying to compare a character type field to a numeric variable.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[craigsboyd]

ramani,

if the variable wasn't in scope wouldn't VFP be giving an error such as "undefined"?

if the variable was not the same datatype wouldn't VFP be giving an error such as "datatype mismatch"?

[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[Mike Lewis]

Mensud,

In addition to the advice you have already been given, you might try running the code with SET ANSI ON and SET ANSI OFF.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[mkrausnick]

It's probably something really simple. How about pasting the exact code into a code window, maybe with a few lines either side of the SELECT statement, along with a LIST STRUCTURE of the section of the table that shows the SLIP field.

You get a code window by enclosing your code with

 AND

tags, and making sure the "Process TGML" box at the bottom of the reply window is checked.

Mike Krausnick
Dublin, California

 

[mensudb]

Hi,

Craig's advice (WHERE sli==lslip) was working.
Thanks for all others who replied on this question.

Mensud

 

[dbMark]

Filter, seek and find behavior can be subtly different when SET EXACT is not ON or OFF as you may assume.

 

[AbashedGoat]

Craig;

PME4JI...

ramani,

if the variable wasn't in scope wouldn't VFP be giving an error such as "undefined"?

if the variable was not the same datatype wouldn't VFP be giving an error such as "datatype mismatch"?

I have seen this happen before. What I think is happening, is the missing variable is treated as a wildcard.

iow...

m.variable = .null.

SELECT cname, cadd1, ccity ;
FROM subscribers ;
WHERE nCustNo = m.variable ;
INTO CURSOR fred

is the same thing as

SELECT cname, cadd1, ccity ;
FROM subscribers ;
WHERE nCustNo = * ;
INTO CURSOR fred

In which case you'll get *all* the names.

Regards - Wayne

 

[Mike Lewis]

Wayne,


WHERE nCustNo = * ;
INTO CURSOR fred

In which case you'll get *all* the names

I see what you have in mind, but I don't think that's here. You wouild have to use the LIKE operator in order for wildcards to be recognised. Also, the SQL wildcards are % and _ rather than ? and *

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[AbashedGoat]

Mike;

Not by my machine right now. I understand about the asterisk. Replace the asterisk with a "null" or empty value and see what happens.

Regards - Wayne

 

[Mike Lewis]

Wayne,

Replace the asterisk with a "null" or empty value and see what happens.


I think the answer to that depends on the setting of SET ANSI. I'll have to try it when I get a moment.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[craigsboyd]

NULL - doesn't display any records in VFP7 or VFP8 whether or not the Ansi is set on or off, so at least for me, it doesn't function as a wild card.

Also, there is a pretty sizeable distinction between a variable with a value of NULL and a variable that is UNDEFINED

...and when using an undefined variable in an sql, VFP will treat it as a column name and report an error SQL: Column "field | variable" is not found (Error 1806)

http://www.sweetpotatosoftware.com

 

[AbashedGoat]

Mike;

"NULL - doesn't display any records in VFP7 or VFP8"

I'm sure Craig's information is correct, my memory is prehistoric. I was thinking vers. 2.6 where this worked.

Regards

 

[Mike Lewis]

Craig,

NULL - doesn't display any records

Yes, of course. I should have realised that. It's obvious now that you mention it.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com