Query error in SQL Server

Question

I am trying to alternate names by querying to view in sql server. The query is as follows,

select 
    CalendarDate, year, month, day, date, hour,
    AVG_VAL, Sum_Val, DVCPORTmc1_Port_No, DVCm_Location,
    case 
       when DVCPORTmc1_SENSOR_Program_name = 'mesh-Cu_1'
         then 'Current A/C 1'
         else case 
                 when DVCPORTmc1_SENSOR_Program_name = 'mesh-Cu_2'
                   then 'Current A/C 2' 
                   else case 
                          when DVCPORTmc1_SENSOR_Program_name = 'mesh-Water' 
                          then 'Water' 
                        end         
              end          
        as DVCPORTmc1_SENSOR_Program_name  
from 
    [REPORT_Device_Dt_Calendar_YEAR_MONTH_DAY_HOUR]
where 
    DVCPORTmc1_SENSOR_Program_name like 'mesh-Cu_%' or
where DVCPORTmc1_SENSOR_Program_name like 'mesh-Water'
order by 
    [year], [month] desc, convert(int, [day]) asc

Which gives the error,

Incorrect syntax near the keyword 'as'.

What is wrong in query?

one of the ends before as DVCPORTmc1_SENSOR_Program_name are redundant — Ormoz
– Ormoz, Commented Jul 4, 2015 at 13:19

marc_s · Accepted Answer · 2015-07-04 13:27:06Z

4

First of all, you could simplify your CASE statement:

case 
   when DVCPORTmc1_SENSOR_Program_name = 'mesh-Cu_1'
      then 'Current A/C 1'
   when DVCPORTmc1_SENSOR_Program_name = 'mesh-Cu_2'
      then 'Current A/C 2' 
   when DVCPORTmc1_SENSOR_Program_name = 'mesh-Water' 
      then 'Water' 
end as DVCPORTmc1_SENSOR_Program_name

No need for all those nested CASE statements...

Secondly: if you want to have multiple criteria in your WHERE clause, don't repeat the WHERE keyword - use this instead (just use the OR or AND keyword):

WHERE
    DVCPORTmc1_SENSOR_Program_name LIKE 'mesh-Cu_%' 
    OR
    DVCPORTmc1_SENSOR_Program_name LIKE 'mesh-Water'

answered Jul 4, 2015 at 13:27

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Smith Over a year ago

Or could simplify it even further with case DVCPORTmc1_SENSOR_Program_name... as all conditions check the same column.

Gaurav Gupta · Accepted Answer · 2015-07-04 17:55:06Z

Buddy you need to learn the syntax for programming basics with SQL concepts. Use the below code for the time being:

select 
    CalendarDate, year, month, day, date, hour,
    AVG_VAL, Sum_Val, DVCPORTmc1_Port_No, DVCm_Location,
    case 
       when DVCPORTmc1_SENSOR_Program_name = 'mesh-Cu_1'
            then 'Current A/C 1'
       when DVCPORTmc1_SENSOR_Program_name = 'mesh-Cu_2'
            then 'Current A/C 2' 
       when DVCPORTmc1_SENSOR_Program_name = 'mesh-Water' 
            then 'Water' 
    end as DVCPORTmc1_SENSOR_Program_name  
from 
    [REPORT_Device_Dt_Calendar_YEAR_MONTH_DAY_HOUR]
where 
    DVCPORTmc1_SENSOR_Program_name like 'mesh-Cu_%' or
    DVCPORTmc1_SENSOR_Program_name like 'mesh-Water'
order by 
    [year], [month] desc, convert(int, [day]) asc

Collectives™ on Stack Overflow

Query error in SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related