Im facing syntax error in CASE statmne tin MS-SQL

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
harshalpotdarAsked:
Who is Participating?
 
knightEknightConnect With a Mentor Commented:
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
 
knightEknightCommented:
oops!  but remove the "end if", it is not needed
0
 
knightEknightCommented:
more specifically, the "end" is needed to end the case, but the "if" part of the "end if" is not needed.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ephraim WangoyaCommented:
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
 
jimyXCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.