Syntax error while trying to declare a variable in sql and using if-else statement. - syntax error at or near "VARCHAR"

Question

Below code shows error near datatype Varchar and also near if statement - syntax error at or near "VARCHAR"

i have tried checking case sensitive and assigning value.

Declare
Userstate VARCHAR := 'Active' ;
begin
if u.disabled_at is null
then
Userstate = 'Inactive';
else
Userstate = 'Active';
end if
end

select o.id as Organisationid,o.name as OrganisationName, orl.user_id as "UserID", u.username as "UserName" ,
Userstate as "Active/Inactive" ,orl.role as "UserType"
from org_user_roles orl
Full outer join orgs o on
o.id = orl.org_id 
Full outer join users u on
orl.user_id  = u.id

i have to declare a variable and use it in if-else statement. Where u.disabled_at is a column from table users. Next i m trying to join my tables to get output in select statement and variable from if-else statement.

This needs more context. Where does u.disabled_at come from? Post a minimal reproducible example. — sticky bit

Bergi Bergi · Accepted Answer · 2019-10-13T18:19:13

You don't need any plsql at all here, no variable declarations and no assignments. In your query, just use a case expression:

select
  o.id as "OrganisationID",
  o.name as "OrganisationName",
  u.id as "UserID",
  u.username as "UserName",
  (case when u.disabled_at is null
    then 'Inactive'
    else 'Active'
  end) as "Active/Inactive",
  orl.role as "UserType"
from
  org_user_roles orl
  full outer join orgs o on o.id = orl.org_id 
  full outer join users u on orl.user_id = u.id

Syntax error while trying to declare a variable in sql and using if-else statement. - syntax error at or near "VARCHAR"

1 Answers