Home » SQL & PL/SQL » SQL & PL/SQL » how can i used connect by prior function. (oracle10g)
| how can i used connect by prior function. [message #619107] |
Fri, 18 July 2014 02:33  |
 |
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
|
Member |
|
|
Write a SQL query...
Create dummy/temp table with 3 columns
Populate the data for the 3 columns
Query output should return value similar to the ConsecutiveStop_No.
CR_CD - DATE/TS - StationaryTime - ConsecutiveStop_NO (Query Output)
A1 - T0 - 0 - 0
A1 - T1 - 5m - 1
A1 - T2 - 10m - 2
A1 - T3 - 0 - 0
A1 - T4 - 5m - 1
A1 - T5 - 10m - 2
A1 - T6 - 15m - 3
A1 - T7 - 0 - 0
Mod Edit: Added code tags to preserve tabular format.
[Updated on: Fri, 18 July 2014 02:45] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: how can i used connect by prior function. [message #619114 is a reply to message #619113] |
Fri, 18 July 2014 02:50  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Amit, this looks like it's a homework assignment, so you're going to have to do a lot of the work yourself. People on here can definitely help guide you, but they won't (and shouldn't do the work for you). I would also suggest that using a recursive query is not the way to solve what appears to be your issue. I would suggest that the row_number analytic function would suit better.
[Updated on: Fri, 18 July 2014 02:51] Report message to a moderator |
|
|
|
|
|
| Re: how can i used connect by prior function. [message #619136 is a reply to message #619116] |
Fri, 18 July 2014 05:50 |
|
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
|
Member |
|
|
Insert into jain18 (CR_CD,DATETS,STATIONARYTIME) values ('1',to_timestamp('01-JAN-91','DD-MON-RR HH.MI.SSXFF AM'),'0');
Insert into jain18 (CR_CD,DATETS,STATIONARYTIME) values ('2',to_timestamp('15-JAN-91','DD-MON-RR HH.MI.SSXFF AM'),'5m');
Insert into jain18 (CR_CD,DATETS,STATIONARYTIME) values ('3',to_timestamp('18-JAN-91','DD-MON-RR HH.MI.SSXFF AM'),'10m');
Insert into jain18 (CR_CD,DATETS,STATIONARYTIME) values ('4',to_timestamp('20-JAN-91','DD-MON-RR HH.MI.SSXFF AM'),'15m');
|
|
|
|
| Re: how can i used connect by prior function. [message #619140 is a reply to message #619136] |
Fri, 18 July 2014 06:29 |
|
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
|
Member |
|
|
hi All,
create table jain18(CR_CD VARCHAR2(10), DATETS DATE, STATIONARYTIME VARCHAR2(100));
I have completed this assignment with cursor but my manager want in sql form (oracle).
just give me hint becouse upto my knowledge connect by prior is used for two tables.
if you have any query regarding my post you can ask me any no. of times.
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Thanks,
amit jain
[Updated on: Fri, 18 July 2014 07:31] Report message to a moderator |
|
|
|
| Re: how can i used connect by prior function. [message #619144 is a reply to message #619140] |
Fri, 18 July 2014 06:53 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
SQL> column stationarytime format a15;
SQL> SELECT cr_cd, datets, stationarytime, row_number() OVER(ORDER BY cr_cd) -1 rn FROM jain18;
CR_CD DATETS STATIONARYTIME RN
---------- ----------- --------------- ----------
1 1/1/1991 0 0
2 1/15/1991 5m 1
3 1/18/1991 10m 2
4 1/20/1991 15m 3
If you have CR_CD as different groups, then add PARTITION BY CR_CD in the window, it would list down the rank starting with 0 to each stationarytime for each group of cr_cd.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how can i used connect by prior function. [message #619177 is a reply to message #619165] |
Fri, 18 July 2014 13:31 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here's one option. It uses ORDER BY DATE_TS frequently; in your example, it is a string so this "solution" works for given sample data set; otherwise, ORDER BY should be modified. I believe that DATE_TS - in reality - is of a DATE data type so ORDER BY should work properly.
SQL> with test as
2 (select 'A1' cr_cd, 'TO' date_ts, '0' stationary_time from dual union all -- 0
3 select 'A1' cr_cd, 'T1' date_ts, '5m' stationary_time from dual union all -- 1
4 select 'A1' cr_cd, 'T2' date_ts, '10m' stationary_time from dual union all -- 2
5 select 'A1' cr_cd, 'T3' date_ts, '0' stationary_time from dual union all -- 0
6 select 'A1' cr_cd, 'T4' date_ts, '5m' stationary_time from dual union all -- 1
7 select 'A1' cr_cd, 'T5' date_ts, '10m' stationary_time from dual union all -- 2
8 select 'A1' cr_cd, 'T6' date_ts, '15m' stationary_time from dual union all -- 3
9 select 'A1' cr_cd, 'T7' date_ts, '0' stationary_time from dual -- 0
10 ),
11 -- Prepare groups; a group starts (i.e. SOG = 1) when STATIONARY_TIME = '0'
12 group_start as
13 (select cr_cd,
14 date_ts,
15 stationary_time,
16 case when stationary_time = '0' then 1
17 else 0
18 end sog
19 from test
20 ),
21 -- Create groups
22 groups as
23 (select cr_cd,
24 date_ts,
25 stationary_time,
26 sum(sog) over (order by date_ts) group_no
27 from group_start
28 )
29 -- Finally, the result
30 select cr_cd,
31 date_ts,
32 stationary_time,
33 row_number() over (partition by group_no order by date_ts) - 1 cons_stop_no
34 from groups
35 order by date_ts;
CR DA STA CONS_STOP_NO
-- -- --- ------------
A1 TO 0 0
A1 T1 5m 1
A1 T2 10m 2
A1 T3 0 0
A1 T4 5m 1
A1 T5 10m 2
A1 T6 15m 3
A1 T7 0 0
8 rows selected.
SQL>
In order to understand what it does, run every part of it step by step (i.e. in first iteration, leave only TEST and GROUP_START; in the second one add GROUPS).
|
|
|
|
| Re: how can i used connect by prior function. [message #619181 is a reply to message #619177] |
Fri, 18 July 2014 13:46 |
 |
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Shorter:
SQL> with test as
2 (select 'A1' cr_cd, 'T0' date_ts, '0' stationary_time from dual union all -- 0
3 select 'A1' cr_cd, 'T1' date_ts, '5m' stationary_time from dual union all -- 1
4 select 'A1' cr_cd, 'T2' date_ts, '10m' stationary_time from dual union all -- 2
5 select 'A1' cr_cd, 'T3' date_ts, '0' stationary_time from dual union all -- 0
6 select 'A1' cr_cd, 'T4' date_ts, '5m' stationary_time from dual union all -- 1
7 select 'A1' cr_cd, 'T5' date_ts, '10m' stationary_time from dual union all -- 2
8 select 'A1' cr_cd, 'T6' date_ts, '15m' stationary_time from dual union all -- 3
9 select 'A1' cr_cd, 'T7' date_ts, '0' stationary_time from dual -- 0
10 ),
11 data as (
12 select cr_cd, date_ts, stationary_time,
13 count(decode(stationary_time, '0', 1)) over (partition by cr_cd order by date_ts) cnt
14 from test
15 )
16 select cr_cd, date_ts, stationary_time,
17 row_number() over (partition by cr_cd, cnt order by date_ts)-1 cons_stop_no
18 from data
19 order by cr_cd, date_ts
20 /
CR DA STA CONS_STOP_NO
-- -- --- ------------
A1 T0 0 0
A1 T1 5m 1
A1 T2 10m 2
A1 T3 0 0
A1 T4 5m 1
A1 T5 10m 2
A1 T6 15m 3
A1 T7 0 0
|
|
|
|
| Re: how can i used connect by prior function. [message #619203 is a reply to message #619107] |
Sat, 19 July 2014 05:09 |
 |
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
Execute following codes one by one 
CREATE TABLE TEMP
(STATIONAY_TIME VARCHAR2(10));
INSERT INTO TEMP VALUES('0')
INSERT INTO TEMP VALUES('5m')
INSERT INTO TEMP VALUES('10m')
INSERT INTO TEMP VALUES('0')
INSERT INTO TEMP VALUES('5m')
INSERT INTO TEMP VALUES('10m')
INSERT INTO TEMP VALUES('15m')
INSERT INTO TEMP VALUES('0')
SELECT REPLACE (stationay_time, 'm') / 5 consecutivestop_no
FROM temp
or you can use
select decode(stationay_time, '0','0','5m','1','10m','2','15m','3') from temp
output
-
Attachment: temp.JPG
(Size: 18.86KB, Downloaded 993 times)
[Updated on: Sat, 19 July 2014 05:31] by Moderator Report message to a moderator |
|
|
|
| Re: how can i used connect by prior function. [message #619204 is a reply to message #619203] |
Sat, 19 July 2014 05:30 |
|
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You assume that data are ALWAYS 0, 5m, 10m, 15m, in this order and no other value, nothing says that.
In addition, you assume there is only one CR_CD value, if this was the case the column should not exist.
You could also assume that the result is always this one and use:
select decode(rownum, 1,0, 2,1, 3,2, 4,0, 5,1, 6,1, 7,2, 8,3, 9,0) consecutivestop_no from temp;
|
|
|
|
| Re: how can i used connect by prior function. [message #619205 is a reply to message #619203] |
Sat, 19 July 2014 05:35 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Are you serious? What if the values are not multiples of 5? For example, show how would it work for STATIONARY_TIME has values like 7m, 13m, 17m and 23m.
Your first approach will never work. And your second approach is like hardcoding all distinct column values inside decode.
|
|
|
|
| Re: how can i used connect by prior function. [message #619207 is a reply to message #619181] |
Sat, 19 July 2014 07:26 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 18 July 2014 14:46
Shorter:
And shorter:
with test as (
select 'A1' cr_cd, 'T0' date_ts, '0' stationary_time from dual union all
select 'A1' cr_cd, 'T1' date_ts, '5m' stationary_time from dual union all
select 'A1' cr_cd, 'T2' date_ts, '10m' stationary_time from dual union all
select 'A1' cr_cd, 'T3' date_ts, '0' stationary_time from dual union all
select 'A1' cr_cd, 'T4' date_ts, '5m' stationary_time from dual union all
select 'A1' cr_cd, 'T5' date_ts, '10m' stationary_time from dual union all
select 'A1' cr_cd, 'T6' date_ts, '15m' stationary_time from dual union all
select 'A1' cr_cd, 'T7' date_ts, '0' stationary_time from dual
)
select cr_cd,
date_ts,
stationary_time,
ltrim(date_ts,'T') - last_value(decode(stationary_time,'0',ltrim(date_ts,'T')) ignore nulls)
over(partition by cr_cd order by date_ts) cons_stop_no
from test
order by cr_cd,
date_ts
/
CR DA STA CONS_STOP_NO
-- -- --- ------------
A1 T0 0 0
A1 T1 5m 1
A1 T2 10m 2
A1 T3 0 0
A1 T4 5m 1
A1 T5 10m 2
A1 T6 15m 3
A1 T7 0 0
8 rows selected.
SQL>
SY.
|
|
|
|
| Re: how can i used connect by prior function. [message #619208 is a reply to message #619207] |
Sat, 19 July 2014 07:53 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And answering the actual question on using PRIOR (and therefore using hierarchical query:
with test as (
select 'A1' cr_cd, 'T0' date_ts, '0' stationary_time from dual union all
select 'A1' cr_cd, 'T1' date_ts, '5m' stationary_time from dual union all
select 'A1' cr_cd, 'T2' date_ts, '10m' stationary_time from dual union all
select 'A1' cr_cd, 'T3' date_ts, '0' stationary_time from dual union all
select 'A1' cr_cd, 'T4' date_ts, '5m' stationary_time from dual union all
select 'A1' cr_cd, 'T5' date_ts, '10m' stationary_time from dual union all
select 'A1' cr_cd, 'T6' date_ts, '15m' stationary_time from dual union all
select 'A1' cr_cd, 'T7' date_ts, '0' stationary_time from dual
)
select cr_cd,
date_ts,
stationary_time,
level cons_stop_no
from test
start with stationary_time = '0'
connect by cr_cd = prior cr_cd
and ltrim(date_ts,'T') = prior ltrim(date_ts,'T') + 1
and stationary_time != '0'
order by cr_cd,
date_ts
/
CR DA STA CONS_STOP_NO
-- -- --- ------------
A1 T0 0 1
A1 T1 5m 2
A1 T2 10m 3
A1 T3 0 1
A1 T4 5m 2
A1 T5 10m 3
A1 T6 15m 4
A1 T7 0 1
8 rows selected.
SQL>
SY.
|
|
|
|
| Re: how can i used connect by prior function. [message #619209 is a reply to message #619208] |
Sat, 19 July 2014 08:56 |
|
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And assuming nothing about the data:
SQL> with test as
2 (select 'A1' cr_cd, 'T0' date_ts, '0' stationary_time from dual union all -- 0
3 select 'A1' cr_cd, 'T1' date_ts, '5m' stationary_time from dual union all -- 1
4 select 'A1' cr_cd, 'T2' date_ts, '10m' stationary_time from dual union all -- 2
5 select 'A1' cr_cd, 'T3' date_ts, '0' stationary_time from dual union all -- 0
6 select 'A1' cr_cd, 'T4' date_ts, '5m' stationary_time from dual union all -- 1
7 select 'A1' cr_cd, 'T5' date_ts, '10m' stationary_time from dual union all -- 2
8 select 'A1' cr_cd, 'T6' date_ts, '15m' stationary_time from dual union all -- 3
9 select 'A1' cr_cd, 'T7' date_ts, '0' stationary_time from dual -- 0
10 ),
11 data as (
12 select cr_cd, date_ts, stationary_time,
13 row_number() over (partition by cr_cd order by date_ts) rn
14 from test
15 )
16 select cr_cd, date_ts, stationary_time,
17 level-1 cons_stop_no
18 from data
19 connect by prior cr_cd = cr_cd and prior rn = rn - 1 and stationary_time != '0'
20 start with stationary_time = '0'
21 order by cr_cd, date_ts
22 /
CR DA STA CONS_STOP_NO
-- -- --- ------------
A1 T0 0 0
A1 T1 5m 1
A1 T2 10m 2
A1 T3 0 0
A1 T4 5m 1
A1 T5 10m 2
A1 T6 15m 3
A1 T7 0 0
|
|
|
|
| Re: how can i used connect by prior function. [message #619254 is a reply to message #619209] |
Mon, 21 July 2014 01:13 |
|
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
|
Member |
|
|
hello sir,
forgive me for not giving proper information of my requirment.
StationaryTime value is not hardcoded value it's not nessesary it's only 0,5m,10m,15m.
it can be any thing.....means 5m,7m,8m,9m but condition is that when StationaryTime is 0 then our ouput means ConsecutiveStop_NO (Query Output) is should start from zero only.
when i try to write content of attachment, it is not showing in proper way.
Please find the attachment.
Thanks,
Amit Jain
-
Attachment: a2.txt
(Size: 0.63KB, Downloaded 941 times)
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 01:56:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
