Conditional where

Question

Post reply

Conditional where

derekmcdonnell3

SSC Journeyman

Points: 95
More actions
October 24, 2013 at 10:14 am

#284234

I need two tables to query together, but not if the value is blank(it is empty, not NULL) so I need a conditional WHERE statement like
WHERE IF(p.PartNoAlias='',,w.PartNoAlias=p.PartNoAlias)
what is the correct syntax?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I would take a different path.

WHERE (p.PartNoAlias='' OR w.PartNoAlias=p.PartNoAlias)

However, I'm not sure this is the best for your query. Seems like you're trying to do a left join with ANSI-89 syntax. If you post your entire query and possibly DDL and sample data, you could get better help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

derekmcdonnell3 SSC Journeyman Points: 95 More actions · Answer 2

I made a mistake, it is in the ON statement that must be conditional, and only on that one field. The data is too messy to get a good sample, but here is the code

SELECT p.Warehouse, p.PartNo, COUNT(w.WONo) AS OrderCount, SUM(w.Qty) AS QtySold, MAX(EntryDate) AS LastSaleDate, WONo

FROM (SELECT WONo, EntryDate, Qty, BillTo, PartNo, PartNoAlias

FROM woparts

WHERE Left(billto,1)='t' and EntryDate > '2012-10-16' and WONo<910000000) w

right join

(SELECT *

FROM parts

WHERE Left(Warehouse,1)='t' and OnHand > 0) p

ON (p.partno=w.partno or w.PartNo = p.PartNoOriginal or w.partno = p.PartNoAlias or w.PartNo = p.OldNUmber or if(p.PartNoAlias='',,w.PartNoAlias=p.PartNoAlias)) and p.Warehouse= left (w.BillTo,5)

GROUP BY p.Warehouse, p.PartNo, WONo

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

WOW, that's messy. Someone did some bad design decisions.

Instead of conditional filters, you have to play with logic.

Instead of

or if(p.PartNoAlias='',,w.PartNoAlias=p.PartNoAlias))

You could use

OR (p.PartNoAlias != '' AND w.PartNoAlias=p.PartNoAlias))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jcb SSCertifiable Points: 6574 More actions · Answer 4

Wow, that's messy and someone is likely playing with Warehouses Old Numbers...

I can take a different path just using relational logic and helping the engine to speed up things.

Someting like.

select tableA join tableB where (tables are joinable)

UNION

select tableA where (tables are not joinable)

but I bet that can be done with a outter join...

😀

ojrodriguez SSC Enthusiast Points: 177 More actions · Answer 5

ojrodriguez

SSC Enthusiast

Points: 177

December 5, 2013 at 7:41 pm

#1671351

Seems like a case for the APPLY operator!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

derekmcdonnell3 (10/24/2013)
I made a mistake, it is in the ON statement that must be conditional, and only on that one field. The data is too messy to get a good sample, but here is the code
SELECT p.Warehouse, p.PartNo, COUNT(w.WONo) AS OrderCount, SUM(w.Qty) AS QtySold, MAX(EntryDate) AS LastSaleDate, WONo
FROM (SELECT WONo, EntryDate, Qty, BillTo, PartNo, PartNoAlias
FROM woparts
WHERE Left(billto,1)='t' and EntryDate > '2012-10-16' and WONo<910000000) w
right join
(SELECT *
FROM parts
WHERE Left(Warehouse,1)='t' and OnHand > 0) p
ON (p.partno=w.partno or w.PartNo = p.PartNoOriginal or w.partno = p.PartNoAlias or w.PartNo = p.OldNUmber or if(p.PartNoAlias='',,w.PartNoAlias=p.PartNoAlias)) and p.Warehouse= left (w.BillTo,5)
GROUP BY p.Warehouse, p.PartNo, WONo

Try rewriting it in a more conventional manner, something like this:

SELECT

p.Warehouse,

p.PartNo,

COUNT(w.WONo) AS OrderCount, -- will always be 1 because w.WONo is in group by

SUM(w.Qty) AS QtySold,

MAX(w.EntryDate) AS LastSaleDate,

WONo

FROM parts p

LEFT JOIN woparts w

ON (w.partno IN (p.partno, p.PartNoOriginal, p.PartNoAlias, p.OldNUmber)

or (p.PartNoAlias = '' AND w.PartNoAlias = ''))

and p.Warehouse = left(w.BillTo,5)

AND Left(w.billto, 1) = 't'

and w.EntryDate > '2012-10-16'

and w.WONo < 910000000

WHERE Left(p.Warehouse,1) = 't'

and p.OnHand > 0

GROUP BY p.Warehouse, p.PartNo, w.WONo

This is much easier for most folks to understand. If they understand your code, you've got a better chance of them helping you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden