Home » SQL & PL/SQL » SQL & PL/SQL » Problem with selecting data (9.2.0.5.0)
Problem with selecting data [message #675752] Wed, 17 April 2019 01:59 Go to next message
chrisk4@wp.pl
Messages: 7
Registered: April 2019
Junior Member
I've got archive version of Oracle: 9.2.0.5.0

And need to extract data from it.

My source table is:

ID,DATE1,DATE_FROM,DATE_TO,TYPE
171367,2002-08-05,2002-08-05,2002-11-28,N
171366,2002-08-05,2002-11-29,2002-12-31,N
171365,2002-08-05,2003-01-01,2003-12-31,N
171364,2002-08-05,2004-01-01,2004-02-29,N
171363,2002-08-05,2004-03-01,2004-06-30,O
172359,2002-08-05,2004-07-01,2005-02-02,N
173739,2002-08-05,2005-02-03,2006-10-31,N
178377,2002-08-05,2006-11-01,2006-12-31,N
178901,2002-08-05,2007-01-01,2007-10-31,N
204761,2002-08-05,2007-11-01,2010-04-01,N
214343,2002-08-05,2010-04-02,2012-07-31,N
220408,2002-08-05,2012-08-01,2015-09-30,N
237282,2002-08-05,2015-10-01,2016-03-31,N
237283,2002-08-05,2016-04-01,2016-04-30,N
244695,2002-08-05,2016-05-01,2016-05-31,N
244696,2002-08-05,2016-06-01,2016-09-30,N
237284,2002-08-05,2016-10-01,2017-01-31,N
248037,2002-08-05,2017-02-01,2017-02-28,N
249605,2002-08-05,2017-03-01,2017-09-07,N
252105,2002-08-05,2017-09-08,2018-01-31,N
255042,2002-08-05,2018-02-01,2018-03-31,N
256031,2002-08-05,2018-04-01,2018-06-30,N
256032,2002-08-05,2018-07-01,2019-01-31,N
262573,2002-08-05,2019-02-01,,N



And a need to select min date_from and max date to while type is the same in time sequence

so destination table should look like that:


ID,DATE1,DATE_FROM,DATE_TO,TYPE
171367,2002-08-05,2002-08-05,2004-02-29,N
171363,2002-08-05,2004-03-01,2004-06-30,O
172359,2002-08-05,2004-07-01,,N

Don't know if its possible at all ??

Regards

[Updated on: Thu, 18 April 2019 11:19] by Moderator

Report message to a moderator

Re: Problem with selecting data [message #675753 is a reply to message #675752] Wed, 17 April 2019 02:20 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
What do you mean by "archive version"?

And in future, please use [code] tags. You have been asked to do this before.

Report message to a moderator

Re: Problem with selecting data [message #675754 is a reply to message #675752] Wed, 17 April 2019 02:28 Go to previous messageGo to next message
chrisk4@wp.pl
Messages: 7
Registered: April 2019
Junior Member
Sorry for that...
Archive i mean old...

Regards

Report message to a moderator

Re: Problem with selecting data [message #675755 is a reply to message #675752] Wed, 17 April 2019 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Sat, 06 April 2019 16:44

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

With any SQL or PL/SQL question, please, Post a working Test case: create and insert statements for all objects so that we will be able work to get what you want.
...

[Updated on: Wed, 17 April 2019 02:48]

Report message to a moderator

Re: Problem with selecting data [message #675760 is a reply to message #675755] Wed, 17 April 2019 08:57 Go to previous messageGo to next message
chrisk4@wp.pl
Messages: 7
Registered: April 2019
Junior Member
Ok. I'll try correct this.

I've try to search both google and forum - however didn't found the solution Sad

my Oracle version is: Oracle9i Enterprise Edition Release 9.2.0.5.0
OS Windows Server 2008 R2 Enterprise

I have to prepare conversion of oracle tables and prepare it for import in another system (tables will be exported to a comma delimited files)
In below table i put extract data with employment periods (for one employer)


select [code][/code]
      zat_id ID,
      zat_data_przyj DATE1,
      zat_data_zmiany DATE_FROM,
      zat_data_do DATE_TO, 
      zat_f_rodzaj TYPE
from ek_zatrudnienie,ek_pracownicy
where zat_prc_id=prc_id
      and prc_numer=2970
order by zat_data_zmiany;

source table (select):

ID	DATE1	        DATE_FROM	DATE_TO	        TYPE
171367	2002-08-05	2002-08-05	2002-11-28	N
171366	2002-08-05	2002-11-29	2002-12-31	N
171365	2002-08-05	2003-01-01	2003-12-31	N
171364	2002-08-05	2004-01-01	2004-02-29	N
171363	2002-08-05	2004-03-01	2004-06-30	O
172359	2002-08-05	2004-07-01	2005-02-02	N
173739	2002-08-05	2005-02-03	2006-10-31	N
178377	2002-08-05	2006-11-01	2006-12-31	N
178901	2002-08-05	2007-01-01	2007-10-31	N
204761	2002-08-05	2007-11-01	2010-04-01	N
214343	2002-08-05	2010-04-02	2012-07-31	N
220408	2002-08-05	2012-08-01	2015-09-30	N
237282	2002-08-05	2015-10-01	2016-03-31	N
237283	2002-08-05	2016-04-01	2016-04-30	N
244695	2002-08-05	2016-05-01	2016-05-31	N
244696	2002-08-05	2016-06-01	2016-09-30	N
237284	2002-08-05	2016-10-01	2017-01-31	N
248037	2002-08-05	2017-02-01	2017-02-28	N
249605	2002-08-05	2017-03-01	2017-09-07	N
252105	2002-08-05	2017-09-08	2018-01-31	N
255042	2002-08-05	2018-02-01	2018-03-31	N
256031	2002-08-05	2018-04-01	2018-06-30	N
256032	2002-08-05	2018-07-01	2019-01-31	N
262573	2002-08-05	2019-02-01	null()  	N

Destination table should in column DATE_FROM - have min date (period start) in column DATE_TO max date (period end) while TYPE is the same in sequence then the same for other TYPE and again for TYPE as previous:

Destination table:


ID       DATE1       DATE_FROM     DATE_TO      TYPE
171367   2002-08-05  2002-08-05    2004-02-29   N
171363   2002-08-05  2004-03-01    2004-06-30   O
172359   2002-08-05  2004-07-01    null()       N



I've try group function:


select 
     max(zat_id) ID,
     zat_data_przyj DATE1,
     min(zat_data_zmiany) DATE_FROM,
     max(zat_data_do) DATE_TO, 
     zat_f_rodzaj TYPE
from ek_zatrudnienie,ek_pracownicy
     where zat_prc_id=prc_id
     and prc_numer=2970
group by zat_data_przyj, zat_f_rodzaj

But destination table miss one record:

ID	DATE1	        DATE_FROM	DATE_TO	        TYPE
262573	2002-08-05	2002-08-05	2019-01-31	N
171363	2002-08-05	2004-03-01	2004-06-30	O

Please give some tips
again sorry for previous post i really need help

Regards

Report message to a moderator

Re: Problem with selecting data [message #675762 is a reply to message #675760] Wed, 17 April 2019 10:03 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 17 April 2019 09:48
From your previous topic:

Michel Cadot wrote on Sat, 06 April 2019 16:44

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

With any SQL or PL/SQL question, please, Post a working Test case: create and insert statements for all objects so that we will be able work to get what you want.
...

Report message to a moderator

Previous Topic: Query running extremely slow
Next Topic: Comparing daywise data
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 28 10:43:01 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.