CODEWITHSUNDEEP
sqloracle-databaseoracle-apexoracle-apex-5.1
Naq_23
Naq_23

Reputation: 61

Select sub queries within a select

insert into time_test(Difference) values 
(select ((select actual from time_test where id = :p13_id)  - 
(select 
(monday+tuesday+wednesday+thursday+friday) from time_test where id= 
:p13_id 
)) from time_test where id= :p13_id)

Difference is a column in time_test which is null, and :p13_id is a page item for Oracle Apex.

I know I need to wrap it in nvl or some function like that but I don't know how.

Upvotes: 0

Views: 127

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

It looks like you're actually trying to do an update, not an insert:

update time_test
set difference = actual - (monday+tuesday+wednesday+thursday+friday)
where id = :p13_id

If any of the 'day' columns might be null then you can use nvl() or coalesce() to default them to zero so they don't break the calculation:

update time_test
set difference = actual - coalesce(monday, 0) - coalesce(tuesday, 0)
  - coalesce(wednesday, 0) - coalesce(thursday, 0) - coalesce(friday, 0)
where id = :p13_id

You could also do coalesce(actual, 0) but it might make more sense to leave the difference null if that is not set. It depends what you want to see in that case.

In this case the nvl() and coalesce() functions are equivalent. If the first argument - e.g. monday - is null then the second argument is substituted. So nvl(monday, 0) will give you the actual value of monday if it is not null, but will give you zero if it is null. You will get the same effect from coalesce(), but that allows a list of multiple expressions to be evaluated and will return the first non-null value from the list.

Another approach to this is to make difference a virtual column that is calculated on the fly, or calculate it in a view over the table; either would remove the duplicate data storage and the need to maintain the value yourself. And if you did definitely want a physical column you could set it from a trigger so automate the maintenance in case any of the other columns are updated outside your Apex application. But a virtual column is probably simpler and neater.

Upvotes: 1

Related Questions