# Sql Help

**URL:** <https://forum.inductiveautomation.com/t/sql-help/2788>\
**Category:** Ignition\
**Created:** [March 21, 2011, 6:51pm UTC](https://forum.inductiveautomation.com/t/sql-help/2788 "2011-03-21T18:51:27Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\
**Post date:** [March 21, 2011, 6:51pm UTC](https://forum.inductiveautomation.com/t/sql-help/2788/1 "2011-03-21T18:51:27Z")

</div>

I have this SQL Query binding to a text field. It will work if there is a value in the table between range. If there is not it comes back with a nothing. How can I get it to bring back the “MinVal +1” if there is nothing in the table between range?

`SELECT
	MIN(materialnumber)+1
FROM
	tbl_ourmaterials a
WHERE
	materialnumber Between {Root Container.MaterialTypeDropdown.MinVal} AND {Root Container.MaterialTypeDropdown.MaxVal}
AND
/* Determine highest number that dont exist */
	NOT EXISTS
		(
		SELECT
			0
		FROM
			tbl_ourmaterials b
		WHERE
			b.materialnumber = a.materialnumber +1
		)`

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [March 21, 2011, 7:04pm UTC](https://forum.inductiveautomation.com/t/sql-help/2788/2 "2011-03-21T19:04:27Z")

</div>

I bet your query still returns a row where the value is NULL. If that is the case you can wrap the MIN(materialnumber)+1 with a COALESCE like this:`SELECT
   COALESCE(MIN(materialnumber)+1,1)
FROM
   tbl_ourmaterials a
WHERE
   materialnumber Between {Root Container.MaterialTypeDropdown.MinVal} AND {Root Container.MaterialTypeDropdown.MaxVal}
AND
/* Determine highest number that dont exist */
   NOT EXISTS
      (
      SELECT
         0
      FROM
         tbl_ourmaterials b
      WHERE
         b.materialnumber = a.materialnumber +1
      )`Where the second argument is the value you want when not in range. If it doesn’t return a row you can use the fallback value on the SQL query binding. Hope this helps.

---

<div class="post-metadata">

**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\
**Post date:** [March 22, 2011, 5:22pm UTC](https://forum.inductiveautomation.com/t/sql-help/2788/3 "2011-03-22T17:22:11Z")

</div>

Worked good. I added my “MinVal” +1 for the second argument for those who may read this down the road.

Thanks Travis ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:")