Solved

Im facing syntax error in CASE statmne tin MS-SQL

Posted on 2011-03-04
6
215 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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