?
Solved

Im facing syntax error in CASE statmne tin MS-SQL

Posted on 2011-03-04
6
Medium Priority
?
217 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 2000 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

649 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