Skip to Main Content

Database Software

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!

Leverage Oracle Database 12c Training and Certification in One Subscription

unknown-1040115Jan 22 2018 — edited Jan 22 2018

Oracle Database Upgrade Learning Subscription--Get the details here:

As the Oracle Database moves to a continuous release model, new features will be added more rapidly than ever before. The Oracle Database Upgrade Learning Subscription has been designed to evolve as the database does so that you can keep your DBA skills up-to-date with the latest features and capabilities. Oracle experts provide insight on Oracle Database and related topics to keep you up-to-speed on all new Oracle Database features.

Stay current with new functionalities for the entire year.

  • Access to subscription content that constantly evolves as the product changes.
  • Keep pace with new product releases in real-time, with no delays.
  • Challenge yourself over a 12-month period to expand your skill set, one area at a time.
  • Deep dive into Oracle 12c new features for DBA professionals to develop subject matter expertise.



Certification exams are integrated into the subscription as well.  This allows you test along the way with built in skill checks, prepare with our technology lessons and then sit for exams to help recognize your expertise with an industry recognized Oracle certification.

  • Consume over 20 days worth of targeted lessons designed for database administrators and database architects and sit for two certification exams for one low cost.
  • Deep dive into database management, configuration, performance monitoring and Oracle 12c new features through expert-led on demand videos.
  • Engage with video content exactly when you need it by viewing topic-based learning channels.
  • Learn how to upgrade your database, while simultaneously becoming a more marketable candidate in a very competitive job market.

Through guided video training, use cases and hands-on labs, this Oracle Database training will help you develop a deep understanding of what it takes to successfully upgrade.

Learn more about Oracle Database 12c Upgrade and Certification offerings from Oracle University.

Comments

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

Frank Kulash

Hi, [Edisson Gabriel López](/ords/forums/user/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](/ords/forums/user/Frank Kulash) [L. Fernigrini](/ords/forums/user/L. Fernigrini) [Gaz in Oz](/ords/forums/user/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.pngBut 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-lopez
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:
image.pngimage.pngNOTE: Oracle version Database 19c

I remain attentive, thank you.

Frank Kulash

Hi, @edisson-gabriel-lopez
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.

1 - 9

Post Details

Added on Jan 22 2018
0 comments
932 views