Question about If/Else clause with SQl Query 1

[JohnOB]

Hi

I have the below query setup

 SELECT "USERX"."UserX" as "User Name", "USERX"."Login_name" as "Login ID", "GroupX"."GroupX" as "Group","USERX"."ACCOUNTLOCKED" as "Account Disabled"
 FROM   "premise"."dbo"."USERX" "USERX" INNER JOIN "premise"."dbo"."GroupX" "GroupX" ON "USERX"."GroupRef"="GroupX"."GroupRef"
 ORDER BY "USERX"."Login_name"

My problem is with the ACCOUNTLOCKED field, this is a data field containig 1 or 0, I would rather have this display text as would want to change the field to 'Account Status' and have 'Disabled' if ACCOUNTLOCKED = 1 Else 'Active' but am struggling, I tried to insert the below formula which I thought might do the job but can't get it to work either, maybe putting it in wrong place.

ACCOUNTLOCKED = CASE WHEN ACCOUNTLOCKED = 1 THEN 'Disabled' ELSE 'Active' END

Any assistance on this would be greatly appreciated, as I am a newby to SQL queries who is having to pick it up as he goes along

(for Info I created a report in Crystal Reports and pasted the SQL query code from that into my SQL database which is why the code may be a bit different from usual SQL).

Thanks

 

[collierd]

I think it's just the way you are using case

Try:

case
   when ACCOUNTLOCKED = 1 then 'Disabled'
   else 'Active'
end

Damian.

 

[collierd]

Thinking about it, probably need:

case
   when "USERX"."ACCOUNTLOCKED" = 1 then 'Disabled'
   else 'Active'
end as "Account Disabled"

To complete the code

 

[JohnOB]

Thanks for your reply, I have pasted this into my code, where I think it should go from your description, as below

 SELECT "USERX"."UserX" as "User Name", "USERX"."Login_name" as "Login ID", "GroupX"."GroupX" as "Group","USERX"."ACCOUNTLOCKED" case 
when "USERX"."ACCOUNTLOCKED" = 1 then 'Disabled'
else 'Active'
end as "Account Disabled"
 FROM   "premise_northayrshire_live"."dbo"."USERX" "USERX" INNER JOIN "premise_northayrshire_live"."dbo"."GroupX" "GroupX" ON "USERX"."GroupRef"="GroupX"."GroupRef"
 ORDER BY "USERX"."Login_name"

But get the error 'Incorrect syntax near the keyword 'case'

I am probably not inserting it properly, any further advice?

Many Thanks

 

[collierd]

Take out "USERX"."ACCOUNTLOCKED" prior to the case

SELECT "USERX"."UserX" as "User Name", "USERX"."Login_name" as "Login ID", "GroupX"."GroupX" as "Group",case 
when "USERX"."ACCOUNTLOCKED" = 1 then 'Disabled'
else 'Active'
end as "Account Disabled"
 FROM   "premise_northayrshire_live"."dbo"."USERX" "USERX" INNER JOIN "premise_northayrshire_live"."dbo"."GroupX" "GroupX" ON "USERX"."GroupRef"="GroupX"."GroupRef"
 ORDER BY "USERX"."Login_name"

 

[JohnOB]

Excellent, this worked perfectly many thanks for your help and quick response!