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
)
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.
Worked good. I added my “MinVal” +1 for the second argument for those who may read this down the road.
Thanks Travis 