SQL syntax problem

[SteveMott]

Hi 

I have an issue with writing the correct SQL syntax.

Please see attached document.

Any help really appreciated.

Thanks

Steve

PharmAdvisor14.docx

[NiceDuck]

What is the 'Select' before the 'Case' there for?

[SteveMott]

Removed that SELECT, there in in error.

Still no result showing.

Updated document attached

Thanks

Steve

PharmAdvisor14.docx

[CoopperBackpack]

Hello @SteveMott,

I don`t see the PRR field on the screenshots that your provided. I assume that this is some numeric field in the FullReactionIngredients table.

If so, it looks like the syntax for converting the value is not correct.

Please test this instead:

CASE 

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

THEN (SELECT CONVERT(VARCHAR, PRR) FROM FullReactionIngredients WHERE DrugReactionID = '[@calcfield:1]')

ELSE 'No cases reported'

END

 

I think for the similar requests you may contact support team via tickets to get a quicker reply.

[SteveMott]

Thanks @CoopperBackpack

The calculation still goes to ELSE even when the conditions for the WHEN statement are true.

I have the WHEN statement working correctly in other tables, so I am wondering what the preconditions for this to succeed need to be met.

I shall take your advice and put in a ticket to support.

I do appreciate your time.

Thanks

Steve

[SteveMott]

Hi @CoopperBackpack

 

Support got back to me with this code:

CASE

WHEN (SELECT COUNT(DrugReactionID) FROM FullReactionIngredients WHERE DrugReactionID = (target.[@field:IngredientID]+'[@ReactionID]')) = 1

THEN (SELECT CONVERT(VARCHAR, (PRR)) FROM FullReactionIngredients WHERE DrugReactionID = (target.[@field:IngredientID]+'[@ReactionID]'))

ELSE 'No cases reported'

END

 

Thanks and Cheers

Steve