• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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
0
harshalpotdar
Asked:
harshalpotdar
1 Solution
 
knightEknightCommented:
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
Industry Leaders: 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!

 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now