Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conditional where Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 10:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:14 PM
Points: 9, Visits: 7
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?
Post #1508146
Posted Thursday, October 24, 2013 10:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:54 PM
Points: 3,637, Visits: 7,932
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508150
Posted Thursday, October 24, 2013 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:14 PM
Points: 9, Visits: 7
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
Post #1508154
Posted Thursday, October 24, 2013 10:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:54 PM
Points: 3,637, Visits: 7,932
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508163
Posted Wednesday, November 13, 2013 1:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:15 AM
Points: 2,734, Visits: 938
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...
Post #1514041
Posted Thursday, December 5, 2013 7:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 8, 2013 9:31 AM
Points: 19, Visits: 35
Seems like a case for the APPLY operator!


Post #1520375
Posted Friday, December 6, 2013 4:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1520514
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse