Answered by:
using If or case in where clause

Hello All,
I am working on a query that will use 2 tables to ge the result. Table 1 is station point and table 2 is series. station point table has 4 columns
Station_GUID Series_GUID Station Create_Date
1 2 6 10/16/2013
2 4 56 10/17/2013
Series table has 5 columns
Series_GUID BegStation EndStation Ascending Effective_Date
2 12 23 1 NULL
3 17 30 1 NULL
4 56 23 1 NULL
Both the tables can be joined with Series_GUID column. Now I want to get the records from station point table based on some conditions in the where clause. I am trying like this but it is not working.
select sp.* FROM Asset.DBO.STATION_POINT SP INNER JOIN Asset.DBO.SERIES S ON S.SERIES_GUID = SP.SERIES_GUID where 1 = (CASE WHEN S.ASCENDING = 1 THEN ((sp.STATION < s.STATION_BEGIN) or (sp.station > s.STATION_END)) WHEN S.ASCENDING = 1 THEN ((sp.STATION > s.STATION_BEGIN)) or ((sp.station < s.STATION_END)) ELSE 'NONE' END ) AND S.EFFECTIVE_DATE IS NULL
Please help me in this regard. Any suggestions would greatly help.
Thank You
Question
Answers

Hi
Please try below
select 1 Station_GUID, 2 Series_GUID , 6 Station , '10/16/2013' Create_date into #STATION_POINT union all select 2, 4 , 56, '10/17/2013' select 2 Series_GUID,12 BegStation, 23 EndStation, 1 Ascending,NULL Effective_date into #SERIES union all select 3, 17, 30, 1, NULL union all select 4, 56, 23, 1, NULL select sp.* FROM #STATION_POINT SP INNER JOIN #SERIES S ON S.SERIES_GUID = SP.SERIES_GUID where ( (S.ASCENDING = 1 and ((sp.STATION >= s.BegStation) and (sp.station <= s.EndStation)) ) or (S.ASCENDING = 1 and ((sp.STATION >= s.EndStation)) and ((sp.station <= s.BegStation)) ) ) AND S.EFFECTIVE_DATE IS NULL
Thanks
Saravana Kumar C
 Marked as answer by Allen Li  MSFTModerator Sunday, October 27, 2013 11:03 AM

It returns exactly one row according to you logic. You may use BETWEEN to make it more readable, but it works already.
SELECT * FROM #STATION_POINT SP INNER JOIN #SERIES S ON S.SERIES_GUID = SP.SERIES_GUID WHERE ( ( S.ASCENDING = 1 AND sp.STATION BETWEEN s.BegStation AND s.EndStation ) OR ( S.ASCENDING = 1 AND sp.STATION BETWEEN s.EndStation AND s.BegStation ) ) AND S.EFFECTIVE_DATE IS NULL;
 Marked as answer by Allen Li  MSFTModerator Sunday, October 27, 2013 11:02 AM
All replies

Hi
Try this
select sp.* FROM Asset.DBO.STATION_POINT SP INNER JOIN Asset.DBO.SERIES S ON S.SERIES_GUID = SP.SERIES_GUID where 1 = (CASE WHEN S.ASCENDING = 1 and ((sp.STATION < s.STATION_BEGIN) or (sp.station > s.STATION_END)) THEN 1 WHEN S.ASCENDING = 1 and ((sp.STATION > s.STATION_BEGIN)) or ((sp.station < s.STATION_END)) THEN 1 ELSE 0 END ) AND S.EFFECTIVE_DATE IS NULL OR select sp.* FROM Asset.DBO.STATION_POINT SP INNER JOIN Asset.DBO.SERIES S ON S.SERIES_GUID = SP.SERIES_GUID where ( (S.ASCENDING = 1 and ((sp.STATION < s.STATION_BEGIN) or (sp.station > s.STATION_END)) ) or (S.ASCENDING = 1 and ((sp.STATION > s.STATION_BEGIN)) or ((sp.station < s.STATION_END)) ) ) AND S.EFFECTIVE_DATE IS NULL
Thanks
Saravana Kumar C

The CASE expression must return an INT or a value which can be implicitly casted to an INT.
But for your logic it is not necessary. Just use a compound condition. In words: WHERE (Ascending=1 AND rangecheck) OR (Ascendig=1 AND rangecheck).
 Proposed as answer by Kalman TothModerator Thursday, October 17, 2013 8:20 PM

CASE must be written to return a single scalar value. It can't return a logical truth value like sp.STATION < s.STATION_BEGIN. So write the case statement to return 1 when you want the row and 0 otherwise. So something like
where 1 = CASE WHEN S.ASCENDING = 1 AND (sp.STATION < s.STATION_BEGIN or sp.station > s.STATION_END) THEN 1 WHEN S.ASCENDING = 1 THEN (sp.STATION > s.STATION_BEGIN or sp.station < s.STATION_END) THEN 1 ELSE 0 END
Tom

Thanks so much for the replies. I think I didn't explain the issue clearly. The where clause in the query should have the condition
((sp.STATION < s.STATION_BEGIN) or (sp.station > s.STATION_END)) when S.Ascending = 1
and the where condition should be ((sp.STATION > s.STATION_BEGIN)) or ((sp.station < s.STATION_END)) )
when s.Ascending = 1Please assist.
Thank You


No. I am trying to set the condition for s.Ascending = 1 and S.Ascending = 1. Whenever s.Ascending = 1, query should look for only this condition (((sp.Station<s.Stattion_Begin)or (sp.Station>s.Station_End)) in the where clause and return the records which satiisfies this condition and whenever s.ascending = 1, in the where clause I should have just (((sp.Station>s.Station_begin) or (sp.Station<s.Station_End)) condition , query should get the results which holds good only for this condition. Please let me know if you have any questions.
Thanks




Hi
Please try below
select 1 Station_GUID, 2 Series_GUID , 6 Station , '10/16/2013' Create_date into #STATION_POINT union all select 2, 4 , 56, '10/17/2013' select 2 Series_GUID,12 BegStation, 23 EndStation, 1 Ascending,NULL Effective_date into #SERIES union all select 3, 17, 30, 1, NULL union all select 4, 56, 23, 1, NULL select sp.* FROM #STATION_POINT SP INNER JOIN #SERIES S ON S.SERIES_GUID = SP.SERIES_GUID where ( (S.ASCENDING = 1 and ((sp.STATION >= s.BegStation) and (sp.station <= s.EndStation)) ) or (S.ASCENDING = 1 and ((sp.STATION >= s.EndStation)) and ((sp.station <= s.BegStation)) ) ) AND S.EFFECTIVE_DATE IS NULL
Thanks
Saravana Kumar C
 Marked as answer by Allen Li  MSFTModerator Sunday, October 27, 2013 11:03 AM

It returns exactly one row according to you logic. You may use BETWEEN to make it more readable, but it works already.
SELECT * FROM #STATION_POINT SP INNER JOIN #SERIES S ON S.SERIES_GUID = SP.SERIES_GUID WHERE ( ( S.ASCENDING = 1 AND sp.STATION BETWEEN s.BegStation AND s.EndStation ) OR ( S.ASCENDING = 1 AND sp.STATION BETWEEN s.EndStation AND s.BegStation ) ) AND S.EFFECTIVE_DATE IS NULL;
 Marked as answer by Allen Li  MSFTModerator Sunday, October 27, 2013 11:02 AM