# Null values in namedQuery

**URL:** <https://forum.inductiveautomation.com/t/null-values-in-namedquery/16946>\
**Category:** General Discussion\
**Tags:** namedqueries\
**Created:** [February 9, 2018, 10:34pm UTC](https://forum.inductiveautomation.com/t/null-values-in-namedquery/16946 "2018-02-09T22:34:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![martinel3746](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@martinel3746](https://forum.inductiveautomation.com/u/martinel3746)\
**Post date:** [February 9, 2018, 10:34pm UTC](https://forum.inductiveautomation.com/t/null-values-in-namedquery/16946/1 "2018-02-09T22:34:00Z")

</div>

I have a named UPDATE Query to do an SQL INSERT.  
It works great when all parameters have values but, I get errors when passing NULL values to columns that allow Nulls.

Columns in the table include:  
…  
DATA\_TYPE, CHAR, NULLABLE=No  
NUMERIC\_VALUE, NUMBER, NULLABLE= **Yes**  
DATE\_VALUE, DATE, NULLABLE= **Yes**  
…

Depending on the value of DATA\_TYPE (either ‘D’ or ‘N’), one of those parameters will be NULL.  
Even though the database allows the columns to have NULLs, the NamedQuery throws exceptions when either parameter is passed a NULL value:  
 java.sql.SQLSyntaxErrorException: ORA-01722: invalid number  
 java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER  
For NULL Number or NULL Date respectively.

I was able to work around the invalid number by changing the **parameter Data Type** to _string_ and changing the SQL query to recast the string as a number with:  
 **to\_number(:numberValue)**

I’m still working on the string TO\_DATE conversions to handle a NULL date.

Are there better ways of handling NULL parameters with Named Queries?  
Is that a possible future enhancement?

---

<div class="post-metadata">

**Author:** ![martinel3746](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@martinel3746](https://forum.inductiveautomation.com/u/martinel3746)\
**Post date:** [February 12, 2018, 10:03pm UTC](https://forum.inductiveautomation.com/t/null-values-in-namedquery/16946/2 "2018-02-12T22:03:34Z")

</div>

Here’s what finally worked for the date:

Change the parameter type from DateTime to String, then change the query for the date field to be:  
 (SELECT CASE WHEN :dateValue IS NULL THEN TO\_DATE(NULL) ELSE TO\_DATE(:dateValue,‘YYYY-MM-DD HH24:MI:SS’) END FROM DUAL),

This works well as long as the datestring is formatted correctly before calling the NamedQuery.  
It moves responsibility for date formatting to the caller instead of letting the NamedQuery manage the date as a date but, functional consistency is king and this fits the bill.  
_Note: This is Oracle syntax; syntax for other databases will be different._

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [December 31, 2020, 5:15pm UTC](https://forum.inductiveautomation.com/t/null-values-in-namedquery/16946/3 "2020-12-31T17:15:37Z")

</div>

Thanks, that was helpful for me.

I was dealing with a situation where I wanted a named query to default to return unfiltered results if a date range was not entered and I was having similar results.