Home » SQL & PL/SQL » SQL & PL/SQL » challenging one (merged)
challenging one (merged) [message #428672] Thu, 29 October 2009 07:39 Go to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

Hi to all,

Here i have the challenging one.Can any one help me on this.


I have given the table structure and insert script. and i mentioned the out put required in the below.


Can any one help me in the query to get the out put.Condition is I should not go for any procedure are creation of new function.


CREATE TABLE EXAMP
(
ID NUMBER,
DATE1 DATE,
DATE2 DATE
);

SET DEFINE OFF;
Insert into SRM.EXAMP
(ID, DATE1, DATE2)
Values
(1, TO_DATE('01/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SRM.EXAMP
(ID, DATE1, DATE2)
Values
(2, TO_DATE('01/01/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SRM.EXAMP
(ID, DATE1, DATE2)
Values
(3, TO_DATE('01/01/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SRM.EXAMP
(ID, DATE1, DATE2)
Values
(4, TO_DATE('01/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SRM.EXAMP
(ID, DATE1, DATE2)
Values
(5, TO_DATE('01/01/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;


Required output
--------------


id date1 date2
-- ------- ------
1 1/1/2000 1/1/2002
2 1/1/2002 1/1/2005
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009
3 1/1/2003 1/1/2004

Thanks
Kishore
Re: challenging one (merged) [message #428675 is a reply to message #428672] Thu, 29 October 2009 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is challenging in your post?
Why have you this result from your data?
Explain!

By the way, read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel
Re: challenging one (merged) [message #428676 is a reply to message #428672] Thu, 29 October 2009 07:48 Go to previous messageGo to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

Hi

after the insert script i mentioned the required output..

Thanks
-----
Kishore
Re: challenging one (merged) [message #428679 is a reply to message #428672] Thu, 29 October 2009 08:04 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

SQL> ed
Wrote file afiedt.buf

  1  select 1 a, id , to_char(date1,'mm/dd/yyy'),to_char(date2,'mm/dd/yyy') from examp where id !=3
  2  union all
  3  select 2 a, id , to_char(date1,'mm/dd/yyy'),to_char(date2,'mm/dd/yyy') from examp where id = 3
  4* order by a
  5  /

         A         ID TO_CHAR(D TO_CHAR(D
---------- ---------- --------- ---------
         1          1 01/01/000 01/01/002
         1          2 01/01/002 01/01/005
         1          4 01/01/005 01/01/007
         1          5 01/01/007 01/01/009
         2          3 01/01/003 01/01/004

SQL> 

@kish_ulike
Make modification as per your requirement. How can this be so challenging one?? Did you try before posting your requirement??

[Updated on: Thu, 29 October 2009 08:12]

Report message to a moderator

Re: challenging one (merged) [message #428682 is a reply to message #428672] Thu, 29 October 2009 08:15 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just to clarify:
Do you want the data returned in ID order apart from the record with ID=3 which should come last?
Which would be what Its_me_ved guessed you meant.
If so, why not just say so?
Re: challenging one (merged) [message #428685 is a reply to message #428682] Thu, 29 October 2009 08:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Actually, Its_me_ved's query does NOT order the id column. It only happens to return the rows in this order this particular time.

It should have an additional order by id.
Re: challenging one (merged) [message #428686 is a reply to message #428685] Thu, 29 October 2009 08:24 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir,
I have added a dummy column to make the order as per the requirement.And make it ordered based on this dummy
Thanks
Ved

[Updated on: Thu, 29 October 2009 08:25]

Report message to a moderator

Re: challenging one (merged) [message #428687 is a reply to message #428686] Thu, 29 October 2009 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Its_me_ved wrote on Thu, 29 October 2009 13:24
Sir,
I have added a dummy column to make the order as per the requirement.And make it ordered based on this dummy
Thanks
Ved


If the order's supposed to be as I suggested then yours isn't guaranteed to do the job as Frank said. You'd need to order by a, id.

This can be done without the dummy column but I'm waiting for the OP to actually confirm what he wants before posting the correct way.
Re: challenging one (merged) [message #428689 is a reply to message #428687] Thu, 29 October 2009 08:31 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks for making the correction
It should be
SQL> ed
Wrote file afiedt.buf

  1  select 1 a, id , to_char(date1,'mm/dd/yyy'),to_char(date2,'mm/dd/yyy') from examp where id !=3
  2  union all
  3  select 2 a, id , to_char(date1,'mm/dd/yyy'),to_char(date2,'mm/dd/yyy') from examp where id = 3
  4* order by a,id
  5  /

         A         ID TO_CHAR(D TO_CHAR(D
---------- ---------- --------- ---------
         1          1 01/01/000 01/01/002
         1          2 01/01/002 01/01/005
         1          4 01/01/005 01/01/007
         1          5 01/01/007 01/01/009
         2          3 01/01/003 01/01/004

SQL> 


Re: challenging one (merged) [message #428692 is a reply to message #428689] Thu, 29 October 2009 08:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As cookiemonster indicated, it is not necessary to use a dummy column, nor to enter the table twice.

with data as
( select 1 id
  ,      'John' ename
  from   dual
  union all
  select 2 id
  ,      'Jack' ename
  from   dual
  union all
  select 3 id
  ,      'Jill' ename
  from   dual
  union all
  select 4 id
  ,      'Anne' ename
  from   dual
  union all
  select 5 id
  ,      'Huub' ename
  from   dual
)
select id
,      ename
from   data
order by decode(id, 3, 2, 1), id 


But, as cookiemonster also pointed out, we don't know the OP's requirements because he refuses to share it with us.
Re: challenging one (merged) [message #428693 is a reply to message #428676] Thu, 29 October 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kish_ulike wrote on Thu, 29 October 2009 13:48
Hi

after the insert script i mentioned the required output..

Thanks
-----
Kishore

No order until you specify one. That's all. Full stop.
If you are unable to give an order rule with words, you are unable to find a SQL that gives in this (not-defined) order.

Regards
Michel

Re: challenging one (merged) [message #428694 is a reply to message #428692] Thu, 29 October 2009 08:51 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks Frank Sir...This is really a good aproach

[Updated on: Thu, 29 October 2009 08:51]

Report message to a moderator

Re: challenging one (merged) [message #428769 is a reply to message #428689] Thu, 29 October 2009 23:03 Go to previous messageGo to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

Thank you very much with this answer.

But i can not use this query n number of rows.because here in where condition we are using the id =3 .If the table contains n number of rows to get the output like this how can i approach..
can any one help me on this please.........
Re: challenging one (merged) [message #428786 is a reply to message #428769] Fri, 30 October 2009 01:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Explain your requirements. Explain WHY your expected results are what they are. What rules are there to follow.
Then show us how you tried to use the examples given to solve it and where you are stuck
Re: challenging one (merged) [message #428794 is a reply to message #428769] Fri, 30 October 2009 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kish_ulike wrote on Fri, 30 October 2009 05:03
Thank you very much with this answer.

But i can not use this query n number of rows.because here in where condition we are using the id =3 .If the table contains n number of rows to get the output like this how can i approach..
can any one help me on this please.........

Michel Cadot wrote on Thu, 29 October 2009 14:48
...
If you are unable to give an order rule with words, you are unable to find a SQL that gives in this (not-defined) order.

Regards
Michel

Re: challenging one (merged) [message #428800 is a reply to message #428672] Fri, 30 October 2009 02:26 Go to previous messageGo to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

First i would like to sorry to you frank that i am not able to respond quickly....

Here my requirement is in the table date1 and date2 are the two columns.

condition 1:-

The out put should be like

date1 is to be act as start date and date2 will act as end date
for the next row what ever the date2 end date(i mean date2 column) will be act as start date(date1 column) in the second row.

Like that it should loop and if the date2 and date1 not matching cases will come at the end.


Again thank you very much for your replies....thanks alot



Kishore
Re: challenging one (merged) [message #428807 is a reply to message #428800] Fri, 30 October 2009 02:47 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
kish_ulike wrote on Fri, 30 October 2009 02:26


condition 1:-

The out put should be like

date1 is to be act as start date and date2 will act as end date
for the next row what ever the date2 end date(i mean date2 column) will be act as start date(date1 column) in the second row.

Like that it should loop and if the date2 and date1 not matching cases will come at the end.




Kishore


Please post a test case ( Create and Insert statement)
and also explaing the output well.

Its quite difficult to understand your requirement logic only seeing the expected result output.

And the answer you have got does not match your requirement what you are telling now.

So it would be better if you post a test case and write your requirement well.

Regards,
Ved


Re: challenging one (merged) [message #428808 is a reply to message #428800] Fri, 30 October 2009 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Investigate CONNECT BY.
Your requirements are not deterministic, what happen if there is an addition row 1/1/2001-1/1/2002 (anywhere)? Where should it be returned?

You have to think about a complete test case with all possible cases and post it.

Regards
Michel

[Updated on: Fri, 30 October 2009 02:48]

Report message to a moderator

Re: challenging one (merged) [message #428825 is a reply to message #428672] Fri, 30 October 2009 04:01 Go to previous messageGo to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

HI every body..

I am happy with the query provided and getting out put like this
A ID TO_CHAR(D TO_CHAR(D
---------- ---------- --------- ---------
1 1 01/01/000 01/01/002
1 2 01/01/002 01/01/005
1 4 01/01/005 01/01/007
1 5 01/01/007 01/01/009
2 3 01/01/003 01/01/004

If i am going to update the column1

update examp set date2=TO_DATE('01/01/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS') where id=1

and if i give the select query
select * from examp;

ID DATE1 DATE2

1 1/1/2000 1/1/2003
2 1/1/2002 1/1/2005
3 1/1/2003 1/1/2004
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009


and my required out put as i told in the condition one

it should be appear like

ID DATE1 DATE2
1 1/1/2000 1/1/2003
3 1/1/2003 1/1/2004
2 1/1/2002 1/1/2005
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009


This was one of the interview question asked ...so i am not able to produce the answer so i kept this question in front of you all..


Now is my requirement is clear?
Re: challenging one (merged) [message #428826 is a reply to message #428825] Fri, 30 October 2009 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No it is not, you didn't answer my question, you didn't post what I requested.

Regards
Michel
Re: challenging one (merged) [message #428831 is a reply to message #428672] Fri, 30 October 2009 04:54 Go to previous messageGo to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

hi michel

1 1/1/2000 1/1/2002
2 1/1/2002 1/1/2005
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009
6 1/1/2001 1/1/2002
2 1/1/2002 1/1/2005
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009
3 1/1/2003 1/1/2004

this is ans for your question
Re: challenging one (merged) [message #428835 is a reply to message #428825] Fri, 30 October 2009 05:11 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:



I am happy with the query provided and getting out put like this
A ID TO_CHAR(D TO_CHAR(D
---------- ---------- --------- ---------
1 1 01/01/000 01/01/002
1 2 01/01/002 01/01/005
1 4 01/01/005 01/01/007
1 5 01/01/007 01/01/009
2 3 01/01/003 01/01/004


I was not happy with the solution I provided though!!
It was Frank Sir who pointed out the feasible way...

Thanks

[Updated on: Fri, 30 October 2009 05:12]

Report message to a moderator

Re: challenging one (merged) [message #428837 is a reply to message #428831] Fri, 30 October 2009 05:13 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
kish_ulike wrote on Fri, 30 October 2009 04:54
hi michel

1 1/1/2000 1/1/2002
2 1/1/2002 1/1/2005
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009
6 1/1/2001 1/1/2002
2 1/1/2002 1/1/2005
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009
3 1/1/2003 1/1/2004

this is ans for your question


Quote:

No it is not, you didn't answer my question, you didn't post what I requested.

Regards
Michel
Re: challenging one (merged) [message #428838 is a reply to message #428837] Fri, 30 October 2009 05:14 Go to previous message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Its_me_ved wrote on Fri, 30 October 2009 11:13
kish_ulike wrote on Fri, 30 October 2009 04:54
hi michel

1 1/1/2000 1/1/2002
2 1/1/2002 1/1/2005
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009
6 1/1/2001 1/1/2002
2 1/1/2002 1/1/2005
4 1/1/2005 1/1/2007
5 1/1/2007 1/1/2009
3 1/1/2003 1/1/2004

this is ans for your question


Quote:

No it is not, you didn't answer my question, you didn't post what I requested.

Regards
Michel

Indeed it is my answer. Smile

I repeat the question and request:

Quote:
What happen if there is an addition row 1/1/2001-1/1/2002 (anywhere)? Where should it be returned?

You have to think about a complete test case with all possible cases and post it.
Previous Topic: debugging resource busy error ORA-00054
Next Topic: How do I make use of the type that I had created in if then code?
Goto Forum:
  


Current Time: Sat Oct 01 05:56:55 CDT 2016

Total time taken to generate the page: 0.10034 seconds