Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I get data from yesterday?

Hi,
I have a problem with my SQL, and it is that I need to save yesterday's data.
The expected result is as follows:
image.pngAnd with the query I currently have, the following happens:
image.pngThat retrieves the value in the same row, not in the next row, and meets yesterday's day condition.

 NVL((select IVFUSN6 from CRPDTA.F59INVEN where to_date(to_date(1900000 + COLGATE_INVENTRY.periodo, 'yyyyddd'),'DD/MM/YYYY')-1 = to_date(to_date(1900000 + IVTRDJ, 'yyyyddd'),'DD/MM/YYYY')-1 AND IVDL01 = COLGATE_INVENTRY.distribuidora),0) inv_inicial,
 NVL((select IVAG6 from CRPDTA.F59INVEN where to_date(to_date(1900000 + COLGATE_INVENTRY.periodo, 'yyyyddd'),'DD/MM/YYYY')-1 = to_date(to_date(1900000 + IVTRDJ, 'yyyyddd'),'DD/MM/YYYY')-1 AND IVDL01 = COLGATE_INVENTRY.distribuidora),0) valor_inv_inicial,

I hope you can help me, thank you.

Comments

Glen Conway

Yeah, testing on Windows, the change in behavior occurred in release 18.2

User_H3J7U

20.3 problem again.
C:\>sql /nolog
Picked up JAVA_TOOL_OPTIONS: -Dnashorn.args=--no-deprecation-warning
SQLcl: Release 20.3 Production on вт нояб. 24 00:33:55 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved.
SQL> ^C
C:\>
Pressing ctrl-c while running query aborts sql.exe, but sql is still active on the server.
C:\>java -version
Picked up JAVA_TOOL_OPTIONS: -Dnashorn.args=--no-deprecation-warning
java version "11.0.9" 2020-10-20 LTS
Java(TM) SE Runtime Environment 18.9 (build 11.0.9+7-LTS)
Java HotSpot(TM) 64-Bit Server VM 18.9 (build 11.0.9+7-LTS, mixed mode)

Billy Verreynne

Also an issue with sqlplus as I detailed in https://community.oracle.com/tech/developers/discussion/4174536/oci-behaviour-of-12c-dedicated-server-process-to-a-client-ocibreak-call#latest
No answers as to why this behaviour is now different.

1 - 3

Post Details

Added on Oct 5 2021
9 comments
1,737 views