# SQL query use tag for fallback value

**URL:** <https://forum.inductiveautomation.com/t/sql-query-use-tag-for-fallback-value/12582>\
**Category:** Ignition\
**Created:** [January 20, 2017, 3:08am UTC](https://forum.inductiveautomation.com/t/sql-query-use-tag-for-fallback-value/12582 "2017-01-20T03:08:47Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [January 20, 2017, 3:08am UTC](https://forum.inductiveautomation.com/t/sql-query-use-tag-for-fallback-value/12582/1 "2017-01-20T03:08:47Z")

</div>

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]

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 20, 2017, 1:03pm UTC](https://forum.inductiveautomation.com/t/sql-query-use-tag-for-fallback-value/12582/2 "2017-01-20T13:03:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [January 22, 2017, 8:02pm UTC](https://forum.inductiveautomation.com/t/sql-query-use-tag-for-fallback-value/12582/3 "2017-01-22T20:02:00Z")

</div>

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.