I have Oracle database table with three columns i.e Id,RTOName,VehicleCode. my table looks like below
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
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 ofvCode
is null you get the records only withnull
values.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 invehiclecode
and also with null then we need to change the query like,