Forum Stats

  • 3,784,126 Users
  • 2,254,894 Discussions
  • 7,880,699 Comments

Discussions

Categories

How can I get data from yesterday?

Edisson Gabriel López
Edisson Gabriel López Member Posts: 47 Green Ribbon
in SQL & PL/SQL

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

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

image.png

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.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,704 Silver Crown

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

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond

    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.
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond

    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
    · Share on TwitterShare on Facebook
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown

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

    · Share on TwitterShare on Facebook
  • Edisson Gabriel López
    Edisson Gabriel López Member Posts: 47 Green Ribbon
    edited Oct 6, 2021 4:14PM

    @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):

    image.png

    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.

    · Share on TwitterShare on Facebook
  • Warren Tolentino
    Warren Tolentino Member Posts: 5,528 Silver Trophy

    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>


    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond

    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.

    · Share on TwitterShare on Facebook
  • Edisson Gabriel López
    Edisson Gabriel López Member Posts: 47 Green Ribbon
    edited Oct 7, 2021 4:23PM

    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:

    image.png
    image.png

    NOTE: Oracle version Database 19c


    I remain attentive, thank you.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond

    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.

    · Share on TwitterShare on Facebook