I have a database in which some tables have to be joined to fetch the data. The problem is that in one table the column is nullable (nullable is set to yes or no constraint given for that column) and the select query is given as NVL(column name,0).
So will the command retrieve it normally or give an error?
Example:
Select eid,ename ,nvl(sal,0),nvl(comm,0)
from employee,sal
where sal>1500
AND
comm=500
In the above query sal
column is nullable but I am passing the query as nvl(sal,0)
so will it retrieve the records or pass an error? If an error then how do you suggest I change the query?
I have already tried and getting neither the output nor any error in sql developer.
When tried in Plsql it gives PLS-00103: Encountered the symbol "OPEN" when expecting one of the following:
:= . ( @ % ;
Update:-
cursor c_alerts is
select function_code as function_code,
nvl(latitude,0.0) as latitude,
nvl(longitude,0.0) as longitude,
nvl(gps_timestamp,0) as gps_timestamp,
nvl(violation_maxspeed, 0) as violation_maxspeed,
nvl(violation_start_time, 0)as violation_startspeed,
nvl(violation_stop_time, 0) as violation_stopspeed,
nvl(duration_sec, 0) as duration_sec,
cs_rec_insert_time as rec_insert_time,
nvl(location1, 'not found ') as location1
from t_vmu_alerts_hist
where vmu_id = lv_vmu_id
and gps_timestamp between lv_from_date and lv_to_date
union
select function_code as function_code,
nvl(latitude, 0.0) as latitude,
nvl(longitude, 0.0) as longitude,
gps as gps_timestamp,
nvl(violation_maxspeed, 0) as violation_maxspeed,
nvl(violation_startspeed, 0)as violation_startspeed,
nvl(violation_stopspeed, 0) as violation_stopspeed,
nvl(duration_sec, 0) as duration_sec,
rec_insert_time as rec_insert_time,
loc_info as location1
from p_error_vmu_alerts
where vmu_id = lv_vmu_id
and gps_timestamp between lv_from_date and lv_to_date
union
select function_code as function_code,
nvl(latitude, 0.0) as latitude,
nvl(longitude, 0.0) as longitude,
gps_timestamp as gps_timestamp,
nvl(violation_maxspeed, 0) as violation_maxspeed,
nvl(violation_startspeed, 0)as violation_startspeed,
nvl(violation_stopspeed, 0) as violation_stopspeed,
nvl(duration_sec, 0) as duration_sec,
rec_insert_time as rec_insert_time,
loc_info as location1
from p_vmu_alerts
where vmu_id = lv_vmu_id
and gps_timestamp between lv_from_date and lv_to_date;
t_alert
open c_alerts();
FETCH c_alerts BULK COLLECT
INTO t_alert;
CLOSE c_alerts;
LOG_ERROR := 13;
Best Answer
So to answer your question.
"So will the command retrieve it normally or give an error?"
Yes it will work and won't return an error.
@update
You forgot your ; after the query.