1

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;
  • the problem is not related to your query, can you post the pl/sql? – Munchi Sep 25 '14 at 12:26
  • It's probably the open @ your cursor – Munchi Sep 25 '14 at 12:51
  • @Munchi check the update – user285oo6 Sep 26 '14 at 5:46
1

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.

  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Colin 't Hart Sep 26 '14 at 8:59
  • My bad, updated the answer – Munchi Sep 26 '14 at 11:11
  • adding ; doesn't solve it instead displays more number of errors including the same PLS-00103: Encountered the symbol "OPEN" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "OPEN" to continue. – user285oo6 Sep 26 '14 at 11:19
  • @Munchi i plan to migrate this question to SO as it has involved a lot of coding now – user285oo6 Sep 26 '14 at 11:21
  • errr the issue is the "t_alert" there. But oke. – Munchi Sep 26 '14 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.