filter IF statement

[ali32j]

hi all

I have a query with several columns, at present it shows duplicate records if it has both a "soldto" and "shipto" in column "Hierarchy" within this there are 3 possible results, "soldto", "shipto" or "node", when it filters i would like it to do the following:

show "soldto", if no "soldto" for a record then show "shipto" and always exclude "node"

can anyone help how i would go about doing this?

Look forward to anyone help here

Thanks

Ali

 

[Remou]

Perhaps something on the lines of:

SELECT ID, Hierarchy, Col1, ColEtc 
FROM Table
WHERE Hierarchy = "soldto"
UNION 
SELECT ID, Hierarchy, Col1, ColEtc 
FROM Table
WHERE Hierarchy = "shipto"
AND ID Not IN (SELECT ID FROM Table
               WHERE Hierarchy = "soldto")

If the table is large, it may be better to use a join.

http://lessthandot.com

 

[ali32j]

Hi sorry i m struggling with this one, i tried simplify when explaining, but the sql is little more complex so i m struggling to figure where to place this.

my SQL is

SELECT DISTINCT Customer.[Name 1], CustomerDetails.ContactNote, CustomerDetails.PotentialOverall, CustomerDetails.CustomerType, CustomerDetails.Column1, CustomerDetails.Column2, Businesswarehousetemp.[FY YTD Daily], Businesswarehousetemp.[FY-1 Full Year], Businesswarehousetemp.[FY-2 Full Year], CustomerDetails.Column3, CustomerDetails.Column4, CustomerDetails.Column5, CustomerDetails.Column6, CustomerDetails.Column7, CustomerDetails.Column8, Count(*) AS [Count Of Customer], CustomerDetails.CallBackDate, CustomerDetails.AppointmentDate, Customer.[Postal Code], Customer.City, Customer.[Sales District], CustomerDetails.ID, CustomerDetails.CustomerTimeUnits, CustomerDetails.ActualCustomerTimeUnits, Customer.[Potential Indicator], CustomerDetails.CDTAM, Customer.[Hierarchy]
FROM (UserInformation INNER JOIN (CustomerDetails INNER JOIN Customer ON CustomerDetails.ID = Customer.ID) ON UserInformation.User = CustomerDetails.User) LEFT JOIN Businesswarehousetemp ON CustomerDetails.ID = Businesswarehousetemp.ID
GROUP BY Customer.[Name 1], CustomerDetails.ContactNote, CustomerDetails.PotentialOverall, CustomerDetails.CustomerType, CustomerDetails.Column1, CustomerDetails.Column2, Businesswarehousetemp.[FY YTD Daily], Businesswarehousetemp.[FY-1 Full Year], Businesswarehousetemp.[FY-2 Full Year], CustomerDetails.Column3, CustomerDetails.Column4, CustomerDetails.Column5, CustomerDetails.Column6, CustomerDetails.Column7, CustomerDetails.Column8, CustomerDetails.CallBackDate, CustomerDetails.AppointmentDate, Customer.[Postal Code], Customer.City, Customer.[Sales District], CustomerDetails.ID, CustomerDetails.CustomerTimeUnits, CustomerDetails.ActualCustomerTimeUnits, Customer.[Potential Indicator], CustomerDetails.CDTAM, Customer.[Hierarchy];

Still remains, Hierarchy i need to look for "soldto", if none present show "shipto"

Could you explain where i fit this into complex code as above?

Ali

 

[MajP]

To do what Remou is suggesting you can bite it off in a couple pieces instead of all at once.

1) Make "qrysoldTo" which is your big query above with a criteria returning only the "soldTo" records
2) Make "qryShipToOnly" which is your big query above with a criteria
WHERE Hierarchy = "shipto"
AND ID Not IN (SELECT ID qrySoldTo)
3) Now union select the two
select * from qrySoldTo union Select * from qryShipToOnly

Same idea as Remou's but in three steps making it a little easier to get the sql correct.

 

[ali32j]

Hi

I m getting the following error in the soldto query:

"the specified field 'ID' could refer to more than one table listed in the FROM clause of your SQL statement"

Is this because i have a field in my table called "ID"??

Ali

 

[Remou]

There is a small typo in MajP's suggestion:

WHERE Hierarchy = "shipto"
AND ID Not IN (SELECT ID [red]FROM[/red] qrySoldTo)

Does that match your SQL?

http://lessthandot.com

 

[PHV]

AND CustomerDetails.ID Not IN (SELECT ID FROM qrySoldTo)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Thanks. I need to pay better attention.