According to the following description I have to frame a CASE...END
statement in SQL server , help me to frame a complex CASE...END
statement to fulfill the following condition.
if PAT_ENT.SCR_DT is not null and PAT_ENTRY.ELIGIBILITY is null then display display 'Favor'
if PAT_ENT.SCR_DT is not null and PAT_ENTRY.EL is equal to No, display 'Error'
if PAT_ENTRY.EL is Yes and DS.DES is equal to null or OFF, display 'Active'
if DS.DES is equal to N, display 'Early Term'
if DS.DES is equal to Y, display 'Complete'
Thanks in advance.
You can evaluate multiple conditions in the CASE statement.
SQL case statement with multiple conditions is known as the Search case statement. So, You should use its syntax if you want to get the result based upon different conditions -.
You can put the condition after the WHEN
clause, like so:
SELECT
CASE
WHEN PAT_ENT.SCR_DT is not null and PAT_ENTRY.ELIGIBILITY is null THEN 'Favor'
WHEN PAT_ENT.SCR_DT is not null and PAT_ENTRY.EL = 'No' THEN 'Error'
WHEN PAT_ENTRY.EL = 'Yes' and ISNULL(DS.DES, 'OFF') = 'OFF' THEN 'Active'
WHEN DS.DES = 'N' THEN 'Early Term'
WHEN DS.DES = 'Y' THEN 'Complete'
END
FROM
....
Of course, the argument could be made that complex rules like this belong in your business logic layer, not in a stored procedure in the database...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With