Correct SQL syntax

[SteveMott]

Hi 

I am trying to retrieve a value from a table based on the condition that it exists.

CASE
WHEN (SELECT COUNT(DrugReactionID) FROM FullReactionIngredients WHERE DrugReactionID = '[@calcfield:3]') = 1

THEN 
SELECT (Reactionname)
FROM FullReactionIngredients
WHERE DrugReactionID = '[@calcfield:3]'

ELSE 'this is false' 
END

Can't get this to validate. Any advice would be greatly appreciated.

Thanks

Steve

[CoopperBackpack]

Hello @SteveMott,

Please wrap the SELECT statement in parenthesis and test the formula.

So, the formula should be:

CASE
WHEN (SELECT COUNT(DrugReactionID) FROM FullReactionIngredients WHERE DrugReactionID = '[@calcfield:3]') = 1

THEN 
(SELECT (Reactionname)
FROM FullReactionIngredients
WHERE DrugReactionID = '[@calcfield:3]')

ELSE 'this is false' 
END

[SteveMott]

Once again, I can't thank you enough!

Cheers

Steve

[NiceDuck]

okay, I think I misread the problem, but sharing this anyway:

 

isnull((CAST((nullif((Select count(Reactionname) from FullReactionIngredients where DrugReactionID = '[@calcfield:3]'),1)) as VARCHAR)), (Select Reactionnamefrom FullReactionIngredients where DrugReactionID = '[@calcfield:2]'))

It kinda works without but without the ELSE part, so yeah, using a conditional statement is still more preferable.
 

[CoopperBackpack]

Hi @NiceDuck,

actually, it is possible to use a different formula that provides the same result in this particular case. 

Since the COALESCE() function is a kind of syntactic shortcut for the CASE expression, but with its own nuances, the formula can be:
 

COALESCE(
(SELECT (Reactionname) FROM FullReactionIngredients WHERE DrugReactionID = '[@calcfield:3]'), 
'this is false'
)

The readability of such a formula is worse but is it shorter.