Help filtering data from a table using the select statement (sql)

[Luiz Eduh]

Hello,

I need some help from the experts. I have an inventory vfp9 table that has over 6000 items. I want to use the select statement to filter out all those items that has data stored on a field called sku. There are some items that don't have anything stored on that "sku" field, so I dont need those. Can some one help me write a query. I tried adding where inv.sku is not null and unfortunately it didn't work.

Any help will be much appreciated.
Thank you

 

[Stella740pl]

What is the data type of that SKU field?

 

[Stella740pl]

I guess, in an inventory table SKU field can only be a number or a character string.
So if SKU is a character string, check for !EMPTY(SKU); if it is a number, check for SKU#0. That should do it.

 

[GriffMG]

Select * from tblInventory where !empty(sku)

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. ​

 

[Olaf Doschke]

If the field can be null you check for not null values with NOT SKU IS NULL, not with SKU NOT IS NULL or SKU IS NOT NULL, the order of these words is important, The only null comparison possibile is FIELD IS NULL to negate that the NOT operation has to be done on all of that, not within that. Jus like you also write NOT FIELD=somevalue and FIELD NOT= somevalue doesn't work, unless you use the specific operators meaning not equal like # or <>. NOT is an operator that needs an operand as next thing, not another operator, nmeither = nor IS. The only operators that combine to another one are < and > combining to <> other operators are standalone between two operands. So know what you're writing, what is an opeerand and operator and it becomes clear SKU IS NOT NULL will not work, also SKÙ NOT IS NULL does not work, just SKU IS NULL can overall be negated to NOT SKU US NULL.

And then in VFP you also can use ISNULL(field), but that's just like LIKE() a function not part of ANSI sql and also existing but having a different meaning in SQL Server T-SQL dialect.

So in short, if NULL values play the role:

Select * from tblInventory where NOT sku is NULL

EMPTY() could also help, if the field is not nullable and just empty.

You can also combine that:

Select * from tblInventory where NOT sku is NULL AND NOT sku=""

I prefer sku="" as it can make use of an index on sku, unlike EMPTY(sku), which would need an index specifc on EMPTY(field) and that is less useful as it only distinguishes between the two categories empty or not empty.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

The advantage of Griff's solution is that it does not depend on the datatyoe of Sku:

SELECT * FROM tblInventory WHERE NOT EMPTY(Sku)

But if there is also a possibility of Sku being Null:

SELECT * FROM tblInventory WHERE NOT EMPTY(Sku) AND NOT ISNULL(Sku)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I rarely think of the Null option - unless I am using someone else's data - thank you Mike

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. ​