Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
pl/sql Procedure

kn_sivaraman
Member Posts: 79
HI,
i have table like this.
CREATE TABLE pr_temp(pr_no NUMBER,f_flg NUMBER(1));
INSERT INTO pr_temp VALUES(11,0);
INSERT INTO pr_temp VALUES(12,1);
INSERT INTO pr_temp VALUES(13,0);
INSERT INTO pr_temp VALUES(14,0);
INSERT INTO pr_temp VALUES(15,0);
INSERT INTO pr_temp VALUES(16,1);
INSERT INTO pr_temp VALUES(17,0);
INSERT INTO pr_temp VALUES(18,0);
INSERT INTO pr_temp VALUES(19,1);
INSERT INTO pr_temp VALUES(20,0);
INSERT INTO pr_temp VALUES(21,0);
INSERT INTO pr_temp VALUES(22,0);
INSERT INTO pr_temp VALUES(23,1);
INSERT INTO pr_temp VALUES(24,1);
INSERT INTO pr_temp VALUES(25,1);
0-success
1-failure
SELECT * FROM pr_temp WHERE f_flg=0 ORDER BY pr_no;
output FOR success;
-------------------
11-15;17-18;20-22;
(ie)11 TO 15 datas are success records,.....
SELECT * FROM pr_temp WHERE f_flg=1 ORDER BY pr_no;
output FOR failure;
------------------
12;16;19;23-25;
(ie)23 TO 25 datas are failure records,.....
i need a pl/SQL PROCEDURE.Please help me.
by
siva
i have table like this.
CREATE TABLE pr_temp(pr_no NUMBER,f_flg NUMBER(1));
INSERT INTO pr_temp VALUES(11,0);
INSERT INTO pr_temp VALUES(12,1);
INSERT INTO pr_temp VALUES(13,0);
INSERT INTO pr_temp VALUES(14,0);
INSERT INTO pr_temp VALUES(15,0);
INSERT INTO pr_temp VALUES(16,1);
INSERT INTO pr_temp VALUES(17,0);
INSERT INTO pr_temp VALUES(18,0);
INSERT INTO pr_temp VALUES(19,1);
INSERT INTO pr_temp VALUES(20,0);
INSERT INTO pr_temp VALUES(21,0);
INSERT INTO pr_temp VALUES(22,0);
INSERT INTO pr_temp VALUES(23,1);
INSERT INTO pr_temp VALUES(24,1);
INSERT INTO pr_temp VALUES(25,1);
0-success
1-failure
SELECT * FROM pr_temp WHERE f_flg=0 ORDER BY pr_no;
output FOR success;
-------------------
11-15;17-18;20-22;
(ie)11 TO 15 datas are success records,.....
SELECT * FROM pr_temp WHERE f_flg=1 ORDER BY pr_no;
output FOR failure;
------------------
12;16;19;23-25;
(ie)23 TO 25 datas are failure records,.....
i need a pl/SQL PROCEDURE.Please help me.
by
siva
Answers
-
hi
could you write at least what this procedure should do?
Regards,
Jarek -
(ie)11 TO 15 datas are success records,.....¿What about 12 then?
Shouldn't it be:
11;13-15;17-18;20-22; instead?
edit
I think you could use Aketi's 'Tabibitosan-method' here.
See these examples: 1007478
Edited by: hoek on Dec 30, 2009 1:02 PM -
Hoek is right, the o/p you posted is not coherent wiht input and rules. Anyway this should solve:
SQL> with t as ( 2 select F_FLG, decode(min(min),max,to_char(max),min(min)||'-'||max) range, 3 row_number() over(partition by f_flg order by max) rn from ( 4 select F_FLG, connect_by_root pr_no min, pr_no max 5 from pr_temp 6 where connect_by_isleaf=1 7 connect by pr_no = prior pr_no+1 and F_FLG = prior f_flg) 8 group by F_FLG, max) 9 select F_FLG, ltrim(sys_connect_by_path(range,';'),';') ranges 10 from t 11 where connect_by_isleaf=1 12 connect by rn = prior rn+1 and f_flg=prior f_flg 13 start with rn=1; F_FLG RANGES ---------- ------------------------------ 0 11;13-15;17-18;20-22 1 12;16;19;23-25
Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/29/estrarre-i-dati-in-formato-xml-da-sql/] -
Hi, Siva,
See the link Hoek supplied for how to grou the rows into sets witht the same f_flg and consecutive pr_nos.
Once you have them assigned to groups, use GROUP BY to get the lowest and highest in each group, and use your favorite string aggregation technique to get the results from all groups in a single string.
For example:VARIABLE target_f_flg NUMBER EXEC :target_f_flg := 1; WITH got_grp_num AS ( SELECT pr_no , pr_no - ROW_NUMBER () OVER (ORDER BY pr_no) AS grp_num FROM pr_temp WHERE f_flg = :target_f_flg ) , got_agg AS ( SELECT MIN (pr_no) AS min_pr_no , MAX (pr_no) AS max_pr_no , ROW_NUMBER () OVER (ORDER BY grp_num) AS grp_seq FROM got_grp_num GROUP BY grp_num ) SELECT LTRIM ( SYS_CONNECT_BY_PATH ( min_pr_no || CASE WHEN min_pr_no != max_pr_no THEN '-' || max_pr_no END , ';' ) , ';' ) AS result_txt FROM got_agg WHERE CONNECT_BY_ISLEAF = 1 START WITH grp_seq = 1 CONNECT BY grp_seq = PRIOR grp_seq + 1 ;
In Express Edition 10.2.0.1.0, the CONNECT BY is often stopping after 2 levels.
I'll test n another version as soon as I can.
Edited by: Frank Kulash on Dec 31, 2009 12:08 PM
Works fine in Enterprise Edition 11. -
Another one, just for fun:
SQL> select ltrim(sys_connect_by_path(pr_no, ';'), ';')||';' pr_no 2 , f_flg 3 from ( select f_flg 4 , case 5 when min(pr_no) = max(pr_no) 6 then to_char(min(pr_no)) 7 else min(pr_no)||'-'||max(pr_no) 8 end pr_no 9 , row_number() over ( partition by f_flg order by min(pr_no) ) rn 10 from ( 11 select f_flg 12 , pr_no 13 , pr_no-row_number() over( partition by f_flg order by f_flg, pr_no ) rn 14 from ( select pr_no 15 , f_flg 16 from pr_temp 17 order by f_flg, pr_no 18 ) 19 ) 20 group by f_flg, rn 21 order by f_flg 22 ) 23 where connect_by_isleaf=1 24 start with rn=1 25 connect by rn = prior rn+1 26 and f_flg = prior f_flg; PR_NO F_FLG ------------------------- ---------- 11;13-15;17-18;20-22; 0 12;16;19;23-25; 1
What DB-version are you on? -
I like Tabibitosan method B-)
col str for a35 select f_flg,substr(sys_connect_by_path(str,';'),2) as str from (select f_flg,Row_Number() over(partition by f_flg order by dis) as rn, case count(*) when 1 then min(to_char(pr_no)) else min(to_char(pr_no)) || '-' || max(to_char(pr_no)) end as str from (select pr_no,f_flg, pr_no-Row_Number() over(partition by f_flg order by pr_no) as dis from pr_temp) group by f_flg,dis) where connect_by_IsLeaf = 1 start with rn=1 connect by prior f_flg = f_flg and prior rn = rn-1; F_FLG STR ----- -------------------- 0 11;13-15;17-18;20-22 1 12;16;19;23-25
This discussion has been closed.