×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

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

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

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

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

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

What is the data type of that SKU field?

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

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.

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

Select * from tblInventory where !empty(sku)

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.

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

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:

CODE

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:

CODE

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

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

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

CODE -->

SELECT * FROM tblInventory WHERE NOT EMPTY(Sku) 

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

CODE -->

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

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

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.

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!

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