How can I get data from yesterday?

Edisson Gabriel López

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:

And with the query I currently have, the following happens:

That 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.

L. Fernigrini

Please post a simpler example, including some table creation and sample data, and the select sentence in text format (not as a picture)

Frank Kulash

Hi, @Edisson Gabriel López

In addition to what Fernigrini said, explain (in general terms, with specific examples) how you get the desired results from the given sample data. Since the results depend on what today is, say what that is in the example. (In my time zone, it's currently day 278 of year 2012; it may be a day off in your time zone, but I still don't see why 121209 meets the condition, but 121211 does not. For that matter, if you're only interested in a single day (e.g. "yesterday"), I don't see why 121207, 121208 and 121209 all meet the condition.)

Always post your complete Oracle version (e.g. 18.4.0.0.0).

Information about dates belongs in DATE columns, not in a NUMBER column such as periodo. This problem, and lots of others, would be simpler and more efficient if you used a DATE column. The data can be collected and entered in a coded form (like periodo) if you really want that, but when it is stored in the database it should be converted to a DATE.

When using TO_DATE (or any function) make sure the arguments have the correct data types. The first argument to TO_DATE should be a string, not a DATE or a NUMBER. So

to_date(to_date(1900000 + ...

has two errors:

1. The first argument to the outer TO_DATE call is a DATE. (TO_DATE, as the name suggests, always returns a DATE. TO_DATE (TO_DATE ... is always wrong.)
2. The first argument to the inner TO_DATE call is a NUMBER.

Frank Kulash

Hi, @Edisson Gabriel López

Never write, let alone post, unformatted code. Which is easier to read and understand: what you posted:

 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

or this:

	 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

Which do you think will be easier to you to debug and maintain?

What is the purpose of the condition above? It looks like you want to see if the day before periodo is the same as day before ivtrdj, which is just a complicated way of checking if periodo is the same day as ivtrdj. A clearer way to code that is:

where COLGATE_INVENTRY.periodo =  IVTRDJ

Gaz in Oz

flashback query, flashback database, flashback table, go back in time...

Edisson Gabriel López

@Frank Kulash @L. Fernigrini @Gaz in Oz

Hi guys, thanks for your comments and sorry for my lack of explanation(I couldn't send you test data because I was using a WITH closure which was a very large query to retrieve the information and also does an INSERT of the information it retrieves from the WITH).

The solution was to make another WITH that brings the same information and in the query I made the SELECT with the following operation: 

NVL((SELECT COLG.INV_FINAL FROM COLGATE_INITIAL COLG WHERE TO_DATE(TO_DATE(1900000 + COLGATEINV.PERIODO, 'YYYYDDD'),'DD/MM/YYYY')-2 = TO_DATE(TO_DATE(1900000 + COLG.PERIODO_YESTERDAY, 'YYYYDDD'),'DD/MM/YYYY')-1 AND COLGATEINV.DISTRIBUIDORA = COLG.DISTRIBUIDORA_ONE AND COLGATEINV.CODIGO_BODEGA = COLG.CODIGO_BODEGA),0) INV_INICIAL,
NVL((SELECT COLG.VALOR_INV_FINAL FROM COLGATE_INITIAL COLG WHERE TO_DATE(TO_DATE(1900000 + COLGATEINV.PERIODO, 'YYYYDDD'),'DD/MM/YYYY')-2 = TO_DATE(TO_DATE(1900000 + COLG.PERIODO_YESTERDAY, 'YYYYDDD'),'DD/MM/YYYY')-1 AND COLGATEINV.DISTRIBUIDORA = COLG.DISTRIBUIDORA_ONE AND COLGATEINV.CODIGO_BODEGA = COLG.CODIGO_BODEGA),0) VALOR_INV_INICIAL,

 As we can see the IVTRDJ field (121207 to 121208) retrieves the values (IVFUSN6 and IVAG6) from yesterday and places them in the fields (IVMATH01 and IVMATH02):

But now I am asked that these values must continue to be filled in (i.e. what is in the row "IVTRDJ = 121208" the values of IVMATH01 and IVMATH02 must be the same "IVTRDJ 121208 to 121210") until the values (IVFUSN6 and IVAG6) change.

IVTRDJ  IVCITM              IVMATH01  IVMATH02 IVFUSN6  IVAG6
121207	CO01606A         	0	0	7200000	-5327280
121208	CO01606A         	7200000	-5327280	0	0
121210	CO01606A         	7200000	-5327280	0	0
121211	CO01606A         	7200000	-5327280	0	0

I hope you can help me.

Warren Tolentino

maybe you are looking for lag analytic function. this examples might help.

SQL> with t as
  2   (select 1000 product_id, to_date('2007/09/25','yyyy/mm/dd') order_date, 20 quantity from dual union all
  3    select 2000 product_id, to_date('2007/09/26','yyyy/mm/dd') order_date, 30 quantity from dual union all
  4    select 1000 product_id, to_date('2007/09/27','yyyy/mm/dd') order_date, 10 quantity from dual union all
  5    select 2000 product_id, to_date('2007/09/28','yyyy/mm/dd') order_date, 25 quantity from dual union all
  6    select 2000 product_id, to_date('2007/09/29','yyyy/mm/dd') order_date, 35 quantity from dual union all
  7    select 1000 product_id, to_date('2007/09/30','yyyy/mm/dd') order_date, 05 quantity from dual)
  8  select * from t;


PRODUCT_ID ORDER_DAT   QUANTITY
---------- --------- ----------
      1000 25-SEP-07         20
      2000 26-SEP-07         30
      1000 27-SEP-07         10
      2000 28-SEP-07         25
      2000 29-SEP-07         35
      1000 30-SEP-07          5


6 rows selected.


SQL>
SQL> with t as
  2   (select 1000 product_id, to_date('2007/09/25','yyyy/mm/dd') order_date, 20 quantity from dual union all
  3    select 2000 product_id, to_date('2007/09/26','yyyy/mm/dd') order_date, 30 quantity from dual union all
  4    select 1000 product_id, to_date('2007/09/27','yyyy/mm/dd') order_date, 10 quantity from dual union all
  5    select 2000 product_id, to_date('2007/09/28','yyyy/mm/dd') order_date, 25 quantity from dual union all
  6    select 2000 product_id, to_date('2007/09/29','yyyy/mm/dd') order_date, 35 quantity from dual union all
  7    select 1000 product_id, to_date('2007/09/30','yyyy/mm/dd') order_date, 05 quantity from dual)
  8  select vt.product_id, quantity, new_order_date
  9    from (select product_id, order_date, quantity,
 10                 lag(order_date,1) over (partition by product_id order by product_id, order_date) as new_order_date
 11            from t) vt
 12    where vt.new_order_date is not null;


PRODUCT_ID   QUANTITY NEW_ORDER
---------- ---------- ---------
      1000         10 25-SEP-07
      1000          5 27-SEP-07
      2000         25 26-SEP-07
      2000         35 28-SEP-07


SQL>

Frank Kulash

Hi, @Edisson Gabriel López

It's still not clear what you want. Again, post a little sample data and the exact results you want from that data

I couldn't send you test data because I was using a WITH closure which was a very large query to retrieve the information and also does an INSERT of the information it retrieves from the WITH

Are you saying that you have a complicated query that produces results that look like what you posted in your original message, and that you want to INSERT some new rows into some table based on that complicated query's result set? If so, post two sets of CREATE TABLE and INSERT statements: one that creates a table that looks like the result set of your complicated query, and the other to create and populate the table into which you will INSERT new rows as it exists before the INSERT in question begins. The desired results will be the contents of that second table after the INSERT is finished.

Once again, post your complete Oracle version number.

Edisson Gabriel López

Hi, @Frank Kulash

Researching, I found the LAG() function that retrieves the previous record, but I have no idea how I can tell it with a WHERE sort what information I want it to fetch(example: it has to be the same ID (1600000545) of the previous record and the next one):

Result I want to achieve:

NOTE: Oracle version Database 19c

I remain attentive, thank you.

Frank Kulash

Hi, @Edisson Gabriel López

Thanks for posting the desired results and Oracle version. Don't forget to post CREATE TABLE and INSERT statements for the sample data that produces those results; then we can begin.