Discussions
Categories
- 385K All Categories
- 2.5K Data
- 580 Big Data Appliance
- 2K Data Science
- 452K Databases
- 222.3K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 32 Multilingual Engine
- 573 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 575 SQLcl
- 4K SQL Developer Data Modeler
- 187.7K SQL & PL/SQL
- 21.5K SQL Developer
- 10 Data Integration
- 10 GoldenGate
- 297.6K Development
- 3 Application Development
- 18 Developer Projects
- 140 Programming Languages
- 294.3K Development Tools
- 118 DevOps
- 3.1K QA/Testing
- 646.3K Java
- 30 Java Learning Subscription
- 37K Database Connectivity
- 185 Java Community Process
- 107 Java 25
- 22.1K Java APIs
- 138.3K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 21 Java Essentials
- 172 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 575 LiveLabs
- 41 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 191 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 240 Portuguese
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:
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.
Answers
-
Please post a simpler example, including some table creation and sample data, and the select sentence in text format (not as a picture)
-
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:
- 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.) - The first argument to the inner TO_DATE call is a NUMBER.
- The first argument to the outer TO_DATE call is a DATE. (TO_DATE, as the name suggests, always returns a DATE.
-
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
-
flashback query, flashback database, flashback table, go back in time...
-
@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.
-
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> -
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.
-
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.
-
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.
