oracle-databasejpanativequeryhibernate-native-query

how to get Null records from oracle database if column is Number type and nullable

I have Oracle database table with three columns i.e Id,RTOName,VehicleCode. my table looks like below

enter image description here

RTOName is the varchar2 type and VehicleCode is NUMBER(2,0) and is nullable field. So I have the data like below and I want to fetch the records with Some VehicleCode and with null value. The table design like this already done so changing that will impact a lot in my application. I have a JPA Native Query that I used like this and I want to fetch the records with null values.

Query query = createNativeQuery("select RTOName,VehicleCode from tbl_vehiclecodes WHERE VehicleCode=#vCode");
query.setParameter("vCode", vehicleCode);

From above Query I will get only Non null valued record. Eg. for vCode parameter 61 I will get Marathalli,61. If my vCode is null I have a problem and I wont get any record. How to achieve this in Native Query? I know that we can use IS NULL in the Query in where clause. Since I have some numbers here In my case how to solve this? Any help

Thanks

Solution

  • We can use OR here,

    Following query will give you records with matched records for parameter vCode along with rows having null and in case of vCode is null you get the records only with null values.

    Query query = createNativeQuery("select RTOName,VehicleCode from tbl_vehiclecodes WHERE (VehicleCode is null or VehicleCode=#vCode)");

    Edit: considering the doubts from @Ranagal

    If you want like in case of null value passed to vCode you want all the records having value in vehiclecode and also with null then we need to change the query like,

    Query query = createNativeQuery("select RTOName,VehicleCode from tbl_vehiclecodes WHERE (VehicleCode is null or VehicleCode=coalesce(#vCode,VehicleCode))");