Skip to Main Content
  • Questions

  • Is there another option for Next Value For

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sandy.

Asked: December 06, 2016 - 8:15 pm UTC

Last updated: December 07, 2016 - 2:19 pm UTC

Version: PL/SQL Release 11.2.0.1.0

Viewed 1000+ times

You Asked

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production




How would I go about subtracting two date fields on a changed value? I tried the code below but NEXT, NEXT VALUE, NEXT VALUE FOR is not working.

IF
{WO#} = next ({WO#}) and {Status} = 'Received'
then {WO_Date} - next({Status_Date)
else if {WO#} = next ({WO#}) and {Status} != 'Received'
then {Status_Date} - next({Status_date}) else 0
End IF



Thank you!

and Connor said...

I (think) that is transact-sql syntax you're provided ?

In Oracle, date handling is quite different.

To add days to a date, its simple numeric arithmetic, eg

sysdate = "right now"
sysdate + 1 = tomorrow at the same time
sysdate + 7 = next week, same day, same time
date1 - date2 = days between the two dates (including decimal for part of day).
trunc(datecol) = removes the time component (ie, midnight)
trunc(datecol,'MM') = takes the date back to the nearest month

Lots of possibilities there - check the SQL docs for more examples

http://docs.oracle.com/database/121/SQLQR/toc.htm

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Getting next value

Sandy Runyon, December 07, 2016 - 1:36 pm UTC

Although helpful, how do you get next value? Next, nextval, NEXT VALUE, and NEXT VALUE FOR, do not work. I need to get next value of a filed and then subtract the dates. I have the dates part but cannot figure out how to get the next value.

I appreciate the assistance!!
Sandy
Connor McDonald
December 07, 2016 - 2:19 pm UTC

NEXT VALUE seems to related to sequences ?

If so, in Oracle you have a SEQUENCE object, and then you select from it

SQL> create sequence seq;

Sequence created.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         2

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         3

SQL>
SQL> declare
  2   x int;
  3  begin
  4    x := seq.nextval;
  5  end;
  6  /

PL/SQL procedure successfully completed.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.