Skip to Main Content
Forums

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

Frank Kulash
Answer

Hi,
Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).
See: https://community.oracle.com/tech/apps-infra/kb/articles/4-how-to-ask-questions-in-developer-community-spaces
If col1, col2, col3, ... are NUMBER columns (or any kind of NUMBER expressions) you can find how many of them that are positive numbers like this:

CASE WHEN col1 > 0 THEN 1 ELSE 0 END +
CASE WHEN col2 > 0 THEN 1 ELSE 0 END +
CASE WHEN col3 > 0 THEN 1 ELSE 0 END ...

If all the expressions are 0 or greater (i.e. no NULLs or negative values) then you can use:

SIGN (col1) +
SIGN (col2) +
SIGN (col3) ...

You can reduce the amount of code by unpivoting the columns, but that will make the query slower. Which is more important in this case: concise code or performance?

Marked as Answer by Sainaa · Jan 27 2021
1 - 1

Post Details

Added on Oct 5 2021
#oracle, #sql, #sqldeveloper
9 comments
1,736 views
4 mentions