DB2 Query issue with Hibernate when checking for values

Question

I have this query, using Hibernate 4 and DB2, running a native query:

select * from TABLE1 t where (:param1 is null or t.NAME = :param1)

That throws exception that null is invalid in the context (since SQL becomes select * from TABLE1 t where (null is null or t.NAME = null))

So I tried:

select * from TABLE1 t where (COALESCE(:param1,'!') = '!' or t.NAME = :param1)

That throws exception when I pass value "JUSTIN" -

"THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER  IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE"

Which appears to be cause by the fact that DB2 checks the length of ":param1" against "!".

Is there a way in DB2 to pass dynamic parameters?

I'm starting to hate DB2....

justanu · Accepted Answer · 2020-03-30 13:08:16Z

1

Turns out this works with Hibernate:

select * from TABLE1 t where NVL(:param1, t.NAME) = t.NAME;

answered Mar 30, 2020 at 13:08

justanu

814 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1