typical query [message #365356] |
Thu, 11 December 2008 13:04  |
sekharsomu
Messages: 72 Registered: December 2008
|
Member |
|
|
i have 2 columns in a tables as follows
WK ID
1 NULL
3 1
9 NULL
10 NULL
11 10
12 10
13 11
14 11
15 11
16 11
17 12
18 12
19 NULL
20 19
21 19
22 20
23 20
24 20
25 21
26 21
27 21
now the out put i require is based on my id say 11 i should fetch all the corresponding wk ie 13,14,15,16 i shuld do that even for null
so my table shuld look like
wx id
null 1 9 10 19
1 3
10 11 12
11 13 14 15 16
12 17 18
19 20 21
20 22 23 24
21 25 26 27
so how do i do this ??
please help me urgent
and they wanted it in a sql query
|
|
|
|
Re: typical query [message #365367 is a reply to message #365358] |
Thu, 11 December 2008 14:06   |
sekharsomu
Messages: 72 Registered: December 2008
|
Member |
|
|
its is not because i am lazy to try i post i post because i dont get it. This is my own version of getting it
if possible please give me a ver which doesnt have any functions
THANQ FOR ALL THE SUGGESTION
NEXT TIME I TRY TO FIT INTO THEM
skarri>ed
Wrote file afiedt.buf
1 SELECT sw,
2 LTRIM(MAX( SYS_CONNECT_BY_PATH ( id, ',')),',') EMP_STRING
3 FROM
4 (SELECT id,sw,
5 row_number() OVER ( PARTITION BY sw ORDER BY rownum) rn
6 FROM maxy)
7 CONNECT BY sw = PRIOR sw
8 AND rn = PRIOR rn+1
9 START WITH rn =1
10 GROUP BY sw
11* ORDER BY sw
12 ;
SW EMP_STRING
---------- -------------------------------------------------------------------------------------
1 3
10 11,12
11 13,14,15,16
12 17,18
19 20,21
20 22,23,24
21 25,26,27
1
|
|
|
Re: typical query [message #365370 is a reply to message #365367] |
Thu, 11 December 2008 14:16   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | NEXT TIME I TRY TO FIT INTO THEM
|
Why didn't you do it in this very post and format it and don't use IM speak and post your Oracle version and post a test case and do not post in UPPER case?
Start doing this and then we will see.
Regards
Michel
[Updated on: Thu, 11 December 2008 14:18] Report message to a moderator
|
|
|
Re: typical query [message #365371 is a reply to message #365370] |
Thu, 11 December 2008 14:21   |
sekharsomu
Messages: 72 Registered: December 2008
|
Member |
|
|
see i tired to edit it but even when i give a gap between my two columns they are getting close after submission and about im language i will not use it any more i am trying to maintain a friendly relation with you why are you so serious at me.
and i think the above post is in oracle version
[Updated on: Thu, 11 December 2008 14:30] Report message to a moderator
|
|
|
Re: typical query [message #365642 is a reply to message #365371] |
Thu, 11 December 2008 14:39   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Placing items in [CODE] and [/CODE] tags will format your code.
wx id
null 1 9 10 19
1 3
10 11 12
11 13 14 15 16
vs.
wx id
null 1 9 10 19
1 3
10 11 12
11 13 14 15 16
|
|
|
|
Re: typical query [message #366221 is a reply to message #365643] |
Fri, 12 December 2008 03:16   |
sekharsomu
Messages: 72 Registered: December 2008
|
Member |
|
|
Hi,
i discovered a new problem with code please observe the follow code
skarri>SELECT * FROM MAXY
2 ;
ID SW
---------- ----------
1
3 1
9
10
11 10
12 10
13 11
14 11
15 11
16 11
17 12
ID SW
---------- ----------
18 12
19
20 19
21 19
22 20
23 20
24 20
25 21
26 21
27 21
21 rows selected.
skarri>ed
Wrote file afiedt.buf
1 SELECT sw,
2 LTRIM(MAX( SYS_CONNECT_BY_PATH ( id, ',')),',') NO_STRING
3 FROM
4 (SELECT id,sw,
5 row_number() OVER ( PARTITION BY sw ORDER BY rownum) rn
6 FROM maxy)
7 CONNECT BY sw = PRIOR sw
8 AND rn = PRIOR rn+1
9 START WITH rn =1
10 GROUP BY sw
11* ORDER BY sw
12 ;
SW NO_STRING
---------------------------------------------------------------------------------------------------
1 3
10 11,12
11 13,14,15,16
12 17,18
19 20,21
20 22,23,24
21 25,26,27
1
8 rows selected.
the problem here is in the output every thing is represented correctly except for null it is represent for null as only one NO_STRING value 1 but actually it has to be 1,9,10,19 now how do i do this
|
|
|
|
Re: typical query [message #366250 is a reply to message #366221] |
Fri, 12 December 2008 05:11   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
You just need to add this condition in your connect by clause
connect by ( (sw = prior sw) or (sw is null and prior sw is null))
See if that helps.
Regards
Raj
|
|
|
|