Solved

Im facing syntax error in CASE statmne tin MS-SQL

Posted on 2011-03-04
6
214 Views
Last Modified: 2012-05-11
Case.sql

I run the attached SQL statement on SQL server  2005 .I got the SQL syntax error on second case statement..
Error i Got
Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'if'.
Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'else'.

Can we use select statement inside the if loop ...

Thanks
Harshal
0
Comment
Question by:harshalpotdar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 35037877
I think this fixes one of the problems, if not all:

       CASE
          WHEN t.multi_config_cd='N'
               THEN  ''
          WHEN t.multi_config_cd='U' and t.target_approver_lan_id=''
               THEN
                    (select top 1 MacroValue from dev_courion.dbo.MacroValues where MacroName='DefaultApprover')---IT return a single value//'MTEEHAN'
               else
                   'hpotdar'
               end if
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35037880
oops!  but remove the "end if", it is not needed
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35037882
more specifically, the "end" is needed to end the case, but the "if" part of the "end if" is not needed.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35037891
change to
SELECT r.*,
       crl.multi_config_cd,
       crl.data_owner_lan_id,
       crl.backup_owner_lan_id,
       crl.sensitive,
       crl.hidden,
       crl.disabled,
       t.target_system_name,
       t.target_system,
       t.multi_config_cd AS targetmulticonfig,
       CASE  
           WHEN t.multi_config_cd='N' 
               THEN  'NO APPROVAL'     
           WHEN t.multi_config_cd='U' 
                THEN 'TARGET APPROVAL'
          END AS TargetApproval,
       CASE 
          WHEN t.multi_config_cd='N'
               THEN  ''
          WHEN t.multi_config_cd='U' then
            case 
              when t.target_approver_lan_id='' THEN
                (select MacroValue from dev_courion.dbo.MacroValues where  MacroName='DefaultApprover')---IT return a single value//'MTEEHAN'
              else 
                'hpotdar'
            end
           
       END  AS target_approver_lan_id,
       al.audit_key,
       AT.audit_name
FROM   user_resource_catalog r
       INNER JOIN cour_target_lst t
         ON r.target_key = t.target_key
       INNER JOIN cour_resource_lst crl
         ON r.resource_cat_key = crl.resource_cat_key
       LEFT JOIN audit_lookup al
         ON al.resource_cat_key = r.resource_cat_key
       LEFT JOIN audit_type AT
         ON al.audit_key = AT.audit_key
WHERE  1 = 1
       AND r.resource_cat_key = 60482
ORDER  BY target_system_name,
          value1

Open in new window

0
 
LVL 24

Expert Comment

by:jimyX
ID: 35037913
Did you try to put "Then ( IF":
       CASE
          WHEN t.multi_config_cd='N'
               THEN  ''
          WHEN t.multi_config_cd='U'
               THEN (if(t.target_approver_lan_id='') THEN
                    (select MacroValue from dev_courion.dbo.MacroValues where  MacroName='DefaultApprover')---IT return a single value//'MTEEHAN'
                    else
                     'hpotdar'
                   end if)
       END  AS target_approver_lan_id,
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35038427
IF is t-sql program flow code, and cannot be used inside a SQL statement.
 you have to put several CASE/WHEN statements as needed, as shown above
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question