Home » Developer & Programmer » Forms » Cursor For Loop Auto Increment
| Cursor For Loop Auto Increment [message #670956] |
Tue, 07 August 2018 06:09  |
 |
sr8464
Messages: 82
Registered: February 2017
Location: India
|
Member |
|
|
Check line #5 i want 1 is to increment by 2 .. n
BEGIN
DECLARE
CURSOR c1 IS
SELECT ROWNUM AS rn, a.code || ' - ' || a.code_type AS xyz
FROM code_mast a, type_mast b
WHERE a.code = (SELECT Substr(temp, 1, 2) -- want 1 is increment by 2
FROM (SELECT Replace(b.code, ' ') AS temp
FROM type_mast b
WHERE b.code = :code));
BEGIN
Clear_list('List_Item');
FOR i IN c1 LOOP
Add_list_element('List_Item', i.rn, i.xyz, i.xyz);
END LOOP;
END;
END;
Or any other method to get result
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Cursor For Loop Auto Increment [message #670968 is a reply to message #670960] |
Tue, 07 August 2018 14:24  |
|
sr8464
Messages: 82
Registered: February 2017
Location: India
|
Member |
|
|
Table Structure...
Table1 code_mast Table2 type_mast
Coumn1 code Column2 code_type Column1 user Column2 code
Value1 A Value2 AUTO Value1 ANIL Value2 A B C .... N
B Value2 BOOK Value1 DAVID Value2 B A
C Value2 CAMERA Value1 ROCKY Value2 C
....N
Rights to access in ListItem
BEGIN
DECLARE
CURSOR c1 IS
SELECT ROWNUM AS rn, a.code || ' - ' || a.code_type AS xyz
FROM code_mast a, type_mast b
WHERE a.code = (SELECT Substr(temp, 1, 2) -- want 1 is increment by [color=red]1[/color]
FROM (SELECT Replace(b.code, ' ') AS temp
FROM type_mast b
WHERE b.code = [color=red]:user[/color])); -- changed from :code
BEGIN
Clear_list('List_Item');
FOR i IN c1 LOOP
Add_list_element('List_Item', i.rn, i.xyz, i.xyz);
END LOOP;
END;
END;
Result for ANIL A - AUTO Result for DAVID B - BOOK Result for ROCKY C - CAMERA
B - BOOK A - AUTO
C - CAMERA
Now i am getting A - AUTO B - BOOK C - CAMERA
Here I am getting Correct result.. But when multiple type_mast.code then ?
BEGIN
DECLARE
CURSOR c1 IS
SELECT ROWNUM AS rn, a.code || ' - ' || a.code_type AS xyz
FROM code_mast a, type_mast b
WHERE a.code = (SELECT Substr(temp, [color=red]1[/color], 2)
FROM (SELECT Replace(b.code, ' ') AS temp
FROM type_mast b
WHERE b.code = [color=red]:user[/color]))
OR a.code = (SELECT Substr(temp, [color=red]2[/color], 2)
FROM (SELECT Replace(b.code, ' ') AS temp
FROM type_mast b
WHERE b.code = [color=red]:user[/color]))
OR a.code = (SELECT Substr(temp, [color=red]3[/color], 2)
FROM (SELECT Replace(b.code, ' ') AS temp
FROM type_mast b
WHERE b.code = [color=red]:user[/color]));
BEGIN
Clear_list('List_Item');
FOR i IN c1 LOOP
Add_list_element('List_Item', i.rn, i.xyz, i.xyz);
END LOOP;
END;
END;
|
|
|
|
|
|
| Re: Cursor For Loop Auto Increment [message #670971 is a reply to message #670969] |
Wed, 08 August 2018 00:29 |
|
sr8464
Messages: 82
Registered: February 2017
Location: India
|
Member |
|
|
Test Case-- (Column name Changed User to Users)
CREATE TABLE CODE_MAST(
CODE VARCHAR(2) NOT NULL,
CODE_TYPE VARCHAR(20) NOT NULL);
CREATE TABLE TYPE_MAST(
USERS VARCHAR(20) NOT NULL,
CODE VARCHAR(50) NOT NULL);
INSERT INTO CODE_MAST VALUES ('A','AUTO');
INSERT INTO CODE_MAST VALUES ('B','BOOK');
INSERT INTO CODE_MAST VALUES ('C','CAMERA');
INSERT INTO TYPE_MAST VALUES ('ANIL','A B C');
INSERT INTO TYPE_MAST VALUES ('DAVID','B A');
INSERT INTO TYPE_MAST VALUES ('ROCKY','C');
COMMIT;
Result for ANIL A - AUTO Result for DAVID B - BOOK Result for ROCKY C - CAMERA
B - BOOK A - AUTO
C - CAMERA
Now i am getting A - AUTO B - BOOK C - CAMERA[Updated on: Wed, 08 August 2018 00:33] Report message to a moderator |
|
|
|
| Re: Cursor For Loop Auto Increment [message #670973 is a reply to message #670971] |
Wed, 08 August 2018 01:04 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 split as (
3 select users, column_value nb, regexp_substr(code, '[^ ]+', 1, column_value) code
4 from type_mast,
5 table(cast(multiset(select level from dual
6 connect by level <= regexp_count(code,' ')+1)
7 as sys.odciNumberList))
8 )
9 select 'Result for '||s.users results, s.nb, s.code||' - '||c.code_type xyz
10 from split s, code_mast c
11 where c.code = s.code
12 order by 1, 2
13 /
RESULTS NB XYZ
------------------------------- ---------- ---------------------------------------------------
Result for ANIL 1 A - AUTO
Result for ANIL 2 B - BOOK
Result for ANIL 3 C - CAMERA
Result for DAVID 1 B - BOOK
Result for DAVID 2 A - AUTO
Result for ROCKY 1 C - CAMERA [Updated on: Wed, 08 August 2018 01:06] Report message to a moderator |
|
|
|
| Re: Cursor For Loop Auto Increment [message #670975 is a reply to message #670973] |
Wed, 08 August 2018 01:33 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
And with the same format as your output:
SQL> with
2 split as (
3 select users, column_value nb, regexp_substr(code, '[^ ]+', 1, column_value) code
4 from type_mast,
5 table(cast(multiset(select level from dual
6 connect by level <= regexp_count(code,' ')+1)
7 as sys.odciNumberList))
8 ),
9 data as (
10 select decode(nb, 1, 'Result for '||s.users, rpad(' ',length(s.users)+11))||
11 ' '||s.code||' - '||c.code_type xyz, s.nb code_nb,
12 dense_rank() over (order by s.users) user_nb
13 from split s, code_mast c
14 where c.code = s.code
15 )
16 select max(decode(user_nb, 1, xyz)) user1,
17 max(decode(user_nb, 2, xyz)) user2,
18 max(decode(user_nb, 3, xyz)) user3
19 from data
20 group by code_nb
21 order by code_nb
22 /
USER1 USER2 USER3
------------------------------ ------------------------------ ------------------------------
Result for ANIL A - AUTO Result for DAVID B - BOOK Result for ROCKY C - CAMERA
B - BOOK A - AUTO
C - CAMERA
3 rows selected. |
|
|
|
| Re: Cursor For Loop Auto Increment [message #670976 is a reply to message #670975] |
Wed, 08 August 2018 01:36 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Or with the PIVOT clause:
SQL> with
2 split as (
3 select users, column_value nb, regexp_substr(code, '[^ ]+', 1, column_value) code
4 from type_mast,
5 table(cast(multiset(select level from dual
6 connect by level <= regexp_count(code,' ')+1)
7 as sys.odciNumberList))
8 ),
9 data as (
10 select decode(nb, 1, 'Result for '||s.users, rpad(' ',length(s.users)+11))||
11 ' '||s.code||' - '||c.code_type xyz, s.nb code_nb,
12 dense_rank() over (order by s.users) user_nb
13 from split s, code_mast c
14 where c.code = s.code
15 )
16 select user1, user2, user3
17 from data
18 pivot (max(xyz) for user_nb in (1 "USER1", 2 "USER2", 3 "USER3"))
19 order by code_nb
20 /
USER1 USER2 USER3
------------------------------ ------------------------------ ------------------------------
Result for ANIL A - AUTO Result for DAVID B - BOOK Result for ROCKY C - CAMERA
B - BOOK A - AUTO
C - CAMERA
3 rows selected. |
|
|
|
|
|
| Re: Cursor For Loop Auto Increment [message #670986 is a reply to message #670985] |
Wed, 08 August 2018 05:49 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I used "Oracle version: 11.2.0.4.170418" for this demo. Please post the version with at least 4 decimals.
Nothing in what I posted requires any privilege (but the ones to query the tables of course).
If you used the test case you posted you should have the same result.
Use SQL*Plus and post the result of (using COL command to format the columns so we can see each row on one line):
select * from v$version;
select * from type_mast;
select * from code_mast;
select users, column_value nb, regexp_substr(code, '[^ ]+', 1, column_value) code
from type_mast,
table(cast(multiset(select level from dual
connect by level <= regexp_count(code,' ')+1)
as sys.odciNumberList))
order by 1, 2
/
with
split as (
select users, column_value nb, regexp_substr(code, '[^ ]+', 1, column_value) code
from type_mast,
table(cast(multiset(select level from dual
connect by level <= regexp_count(code,' ')+1)
as sys.odciNumberList))
)
select decode(nb, 1, 'Result for '||s.users, rpad(' ',length(s.users)+11))||
' '||s.code||' - '||c.code_type xyz, s.nb code_nb,
dense_rank() over (order by s.users) user_nb
from split s, code_mast c
where c.code = s.code
order by 1
/
with
split as (
select users, column_value nb, regexp_substr(code, '[^ ]+', 1, column_value) code
from type_mast,
table(cast(multiset(select level from dual
connect by level <= regexp_count(code,' ')+1)
as sys.odciNumberList))
),
data as (
select decode(nb, 1, 'Result for '||s.users, rpad(' ',length(s.users)+11))||
' '||s.code||' - '||c.code_type xyz, s.nb code_nb,
dense_rank() over (order by s.users) user_nb
from split s, code_mast c
where c.code = s.code
)
select user1, user2, user3
from data
pivot (max(xyz) for user_nb in (1 "USER1", 2 "USER2", 3 "USER3"))
order by code_nb
/ |
|
|
|
|
|
|
|
| Re: Cursor For Loop Auto Increment [message #670990 is a reply to message #670989] |
Wed, 08 August 2018 08:06 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You can also put the subqueries in WITH clause inside FROM one:
SQL> select max(decode(user_nb, 1, xyz)) user1,
2 max(decode(user_nb, 2, xyz)) user2,
3 max(decode(user_nb, 3, xyz)) user3
4 from ( select decode(nb, 1, 'Result for '||s.users, rpad(' ',length(s.users)+11))||
5 ' '||s.code||' - '||c.code_type xyz, s.nb code_nb,
6 dense_rank() over (order by s.users) user_nb
7 from ( select users, column_value nb, regexp_substr(code, '[^ ]+', 1, column_value) code
8 from type_mast,
9 table(cast(multiset(select level from dual
10 connect by level <= regexp_count(code,' ')+1)
11 as sys.odciNumberList)) ) s,
12 code_mast c
13 where c.code = s.code )
14 group by code_nb
15 order by code_nb
16 /
USER1 USER2 USER3
------------------------------ ------------------------------ ------------------------------
Result for ANIL A - AUTO Result for DAVID B - BOOK Result for ROCKY C - CAMERA
B - BOOK A - AUTO
C - CAMERA |
|
|
|
|
|
|
|
| Re: Cursor For Loop Auto Increment [message #670995 is a reply to message #670992] |
Wed, 08 August 2018 13:19 |
|
sr8464
Messages: 82
Registered: February 2017
Location: India
|
Member |
|
|
When using this in Form 6 it says... identifier "REGEXP_COUNT" must be declared
BEGIN
DECLARE
CURSOR c1 IS
select a.code || ' - ' || a.code_type xyz
from code_mast a, type_mast b
WHERE b.users = 'ANIL'
AND a.code IN
(select regexp_substr(code, '[^ ]+', 1, column_value)
from type_mast,
table(cast(multiset
(select level
from dual
connect by level <= regexp_count(code, ' ') + 1) as
sys.odciNumberList))
WHERE users = 'ANIL');
BEGIN
Clear_list('List_Item');
FOR i IN c1 LOOP
Add_list_element('List_Item', i.rn, i.xyz, i.xyz);
END LOOP;
END;
END; |
|
|
|
| Re: Cursor For Loop Auto Increment [message #670996 is a reply to message #670995] |
Wed, 08 August 2018 13:27 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You can replace regexp_count like this:
SQL> select a.code || ' - ' || a.code_type xyz
2 from code_mast a, type_mast b
3 WHERE b.users = 'ANIL'
4 AND a.code IN
5 (select regexp_substr(code, '[^ ]+', 1, column_value)
6 from type_mast,
7 table(cast(multiset
8 (select level
9 from dual
10 connect by level <= length(code)-length(replace(code,' ','')) + 1) as
11 sys.odciNumberList))
12 WHERE users = 'ANIL')
13 /
XYZ
-------------------------
A - AUTO
B - BOOK
C - CAMERA |
|
|
|
| Re: Cursor For Loop Auto Increment [message #670998 is a reply to message #670996] |
Thu, 09 August 2018 00:28 |
|
sr8464
Messages: 82
Registered: February 2017
Location: India
|
Member |
|
|
Internal Error [22914]
BEGIN
DECLARE
CURSOR c1 IS
SELECT ROWNUM rn, a.code || ' - ' || a.code_type xyz
FROM code_mast a, type_mast b
WHERE b.users = 'ANIL'
AND a.code IN
(SELECT Regexp_substr(b.code, '[^ ]+', 1, column_value)
FROM TABLE(Cast(MULTISET
(SELECT LEVEL
FROM dual
CONNECT BY LEVEL <=
Length(b.code) -
Length(Replace(b.code, ' ', '')) + 1) AS
sys.ODCINUMBERLIST))
WHERE b.users = 'ANIL');
BEGIN
Clear_list('l_entity');
FOR i IN c1 LOOP
Add_list_element('l_entity', i.rn, i.xyz, i.xyz);
END LOOP;
END;
END; |
|
|
|
|
|
| Re: Cursor For Loop Auto Increment [message #671008 is a reply to message #670999] |
Thu, 09 August 2018 05:40 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Yes - there's all sorts of SQL keywords and functions that forms 6i (you really need to upgrade) won't recognize.
Moving the select to a view stops the forms parser from seeing all the things it doesn't understand.
And don't tell us you're not using forms when you are very obviously using forms - that's just silly.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 07:17:36 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
