How do I build an SQL Select Statement that returns a IS NOT NULL? 1

[rodrunner79]

Ok, here's the problem: I have to 2 tables, one has a packslip # (Table A) and one as an invoice # (Table B). They are related by the PO number. I need to build a query where the return set contain the Item Code, Packslip # and Invoice # where both packslip # and invoice # are not null.

Here's what I got.

SELECT Orders_PkSlp.ItemCode, Orders_PkSlp.PkslpNo, Orders_Invoice.InvNo
FROM Orders_PkSlp LEFT OUTER JOIN
Orders_Invoice ON Orders_PkSlp.PONum = Orders_Invoice.PONum
WHERE (Orders_Invoice.InvNo IS NOT NULL) AND (Orders_PkSlp.Pkslip IS NOT NULL)

When I run the query, the return set shows all records that have invoice numbers and some of these records have packslip numbers that are NULL. The first condition is met but not the second. Why?

It must not show records that have NULL packslip even if they have an invoice number and vice versa.
16 hours ago - 3 days left to answer.
Additional Details
By the way, I'm trying to the this in SQL Server Management Studio 2008. I'm connected to a SQL Database

Can't means won't try.

 

[IanWaterman]

try

SELECT Orders_PkSlp.ItemCode, Orders_PkSlp.PkslpNo, Orders_Invoice.InvNoFROM Orders_PkSlp
LEFT OUTER JOINOrders_Invoice ON Orders_PkSlp.PONum = Orders_Invoice.PONum
and (Orders_Invoice.InvNo IS NOT NULL)
AND (Orders_PkSlp.Pkslip IS NOT NULL)

Ian

 

[rodrunner79]

Thanks, Ian. But that didn't solve it. Ok, I looked closer at the data and turns out that the records themselves are not null values but rather they are spaces. I counted the number of spaces and then tried to filter them out by using ' ' inside my WHERE clause but still no luck. Here's the actual code:

SELECT     Harbor_POEXTINPOITEM.HMMSCde, Harbor_POEXTINPOITEM.Pkslip, Harbor_POEXTINPOINVO.InvNo
FROM         Harbor_POEXTINPOITEM LEFT OUTER JOIN
                      Harbor_POEXTINPOINVO ON Harbor_POEXTINPOITEM.PVNum = Harbor_POEXTINPOINVO.PONumber
WHERE     (Harbor_POEXTINPOINVO.InvNo IS NOT NULL) AND (Harbor_POEXTINPOITEM.Pkslip IS NOT NULL) OR
                      (Harbor_POEXTINPOINVO.InvNo <> '            ') AND (Harbor_POEXTINPOITEM.Pkslip <> '            ')

Help please.

Can't means won't try.

 

[gmmastros]

rodrunner79,

You now have an OR in your where clause without appropriate parenthesis to control the OR logic. I believe there is an easier way to handle this:

SELECT Harbor_POEXTINPOITEM.HMMSCde, 
       Harbor_POEXTINPOITEM.Pkslip, 
       Harbor_POEXTINPOINVO.InvNo
FROM   Harbor_POEXTINPOITEM 
       LEFT OUTER JOIN Harbor_POEXTINPOINVO 
         ON Harbor_POEXTINPOITEM.PVNum = Harbor_POEXTINPOINVO.PONumber
WHERE  Harbor_POEXTINPOINVO.InvNo > ''
       AND Harbor_POEXTINPOITEM.Pkslip > ''

This will filter the NULLS and also the empty strings. You are guaranteed to get some value in each of the columns. Please understand that this will only filter out empty strings and strings of spaces. If there are other non-printable characters in your data, they will still be returned (for example, the TAB character).

Also realize that your query is using a left join between the two tables, but the where clause is effectively removing rows where the right table doesn't have any match, so you have effectively turned the query in to an inner join. If this truly is your intent, then you should change the join to INNER instead of LEFT.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rodrunner79]

Hi George,

That was perfect. You, sir, are a gentleman and a scholar. Thank you very much!

I took a SQL class a couple of years back but never really applied what I learn in business operations so I forgot most of it. Now, I need to polish up.

Slowly but surely I'll get the hang of it.

Thanks again.

Can't means won't try.

 

[rodrunner79]

Oh, one more thing. I haven't been to this Forum and a while but everytime I do, I'm always treated with courtesy and respect. Always get my questions answered and issues adressed and resolved. Just want to acknowledge the many inteligent users that goes on this site. If it wasn't for you, I probably wouldn't have been where I am it. And I mean that in literal sense. I got promoted because of a Access DB application I built. And during that period, I got a lot of help from these forums. Thank you as always.

Can't means won't try.

 

[gmmastros]

Thank you for that warm praise. I have no illusions that I am the only one that has helped you. However, if I have helped in even a small way, I am proud of that accomplishment.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom