none
using If or case in where clause

    Question

  • 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

    Thursday, October 17, 2013 6:53 PM

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

    Thursday, October 17, 2013 10:18 PM
  • 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;

    Friday, October 18, 2013 8:01 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

    Thursday, October 17, 2013 7:07 PM
  • 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 range-check) OR (Ascendig=-1 AND range-check).

    Thursday, October 17, 2013 7:10 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

    Thursday, October 17, 2013 7:14 PM
  • 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 = -1

    Please assist.

    Thank You

    Thursday, October 17, 2013 7:23 PM
  • Are you trying to perform this:

    SELECT * FROM TABLE
    WHERE (((sp.Station<s.Stattion_Begin)or (sp.Station>s.Station_End)) and s.Ascending=1)
     
    and (((sp.Station>s.Station_begin) or (sp.Station<s.Station_End)) and s.Ascending=-1)


    Thanks, hsbal

    Thursday, October 17, 2013 8:17 PM
  • 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

    Thursday, October 17, 2013 8:48 PM
  • What is the problem with the result, when you apply the condition?
    Thursday, October 17, 2013 8:51 PM
  • It is getting me everything..Not just the records satisfying the condition.
    Thursday, October 17, 2013 8:59 PM
  • Please post a concise and complet sample showing this behaviour. Include table DDL and enough sample data as a runnable T-SQL script.

    Thursday, October 17, 2013 9:02 PM
  • 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

    Thursday, October 17, 2013 10:18 PM
  • 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;

    Friday, October 18, 2013 8:01 AM