SQL query use tag for fallback value

Ignition
1

Fallback values for SQL expressions have been touched on before, but with quite the same problem.

I have been trying to come up with a query that will return something, even when there is no record found in the table.
I enabled the ‘Fallback Value’ feature and set to 0 which worked well for numeric values.

Ideally i would like to bind the Fallback Value to an Ignition tag. Why is this not possible?
What i am trying to achieve is; Query for a value in a table. If query returns no rows, then the record has not been created yet, and i want to set some default values. These should be provided by an Ignition tag.

I have tried to use various queries using “CASE” and “COALESCE”. Unfortunately i can’t get CASE to return something using “ELSE”, and i get errors from Ignition saying no rows are found.

Current query i am using:

[code]SELECT
CASE
WHEN EXISTS (SELECT ProductID FROM QAFingerJointTest WHERE Test_ID = ‘{Root Container.ContainerFingerJoint.Text Field Test ID.text}’) THEN ProductID
ELSE ‘{Gisborne01/ProdSystems/ProdRecipe/ProdRcp_ProductID/Value}’
END AS prodID

FROM
QAFingerJointTest
WHERE
Test_ID = ‘{Root Container.ContainerFingerJoint.Text Field Test ID.text}’[/code]

2

Hmmm. I would put your defaults in a static dataset property, then use an expression that looks at the number of rows in the originally returned dataset to select it or the static dataset.

3

Couldn’t get a CASE to work with an EXISTS as shown above, but this is how i got something acceptable working:

SELECT CASE WHEN {Root Container.ContainerLamCalibration.newTest} = 1 THEN ( '{Gisborne01/ProdSystems/ProdRecipe/ProdRcp_ProductID/Value}') ELSE ( SELECT ProductID FROM QALamGradeCal WHERE Test_ID = '{Root Container.ContainerLamCalibration.Text Field Test ID.text}') END AS text

The “newTest” variable is a different query to check whether the row record exists in the table or not, which i use for many other things in the window.