Home » Developer & Programmer » Forms » Cursor For Loop Auto Increment
Cursor For Loop Auto Increment [message #670956] Tue, 07 August 2018 06:09 Go to next message
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 #670958 is a reply to message #670956] Tue, 07 August 2018 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to have a look at "row generator".

Re: Cursor For Loop Auto Increment [message #670959 is a reply to message #670958] Tue, 07 August 2018 06:48 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Any link for that
Re: Cursor For Loop Auto Increment [message #670960 is a reply to message #670959] Tue, 07 August 2018 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry, related topic in the forum seems to be missing:
http://www.orafaq.com/forum/t/204927/

Re: Cursor For Loop Auto Increment [message #670964 is a reply to message #670960] Tue, 07 August 2018 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It seems the topic is lost for the moment.
You can have examples of row generators using "row generator" in the "Search" link below the banner above.

Re: Cursor For Loop Auto Increment [message #670968 is a reply to message #670960] Tue, 07 August 2018 14:24 Go to previous messageGo to next message
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
Old code Correction
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 to be, and what i getting
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;
Checked some "row generator" also, can i use Procedure or Function here if Yes Please Guide me...
Re: Cursor For Loop Auto Increment [message #670969 is a reply to message #670968] Tue, 07 August 2018 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data and show you a solution.

Re: Cursor For Loop Auto Increment [message #670971 is a reply to message #670969] Wed, 08 August 2018 00:29 Go to previous messageGo to next message
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 to be, and what i getting
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
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 #670985 is a reply to message #670975] Wed, 08 August 2018 04:59 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
/foru/forum/fa/13887/0/
Tried all but not getting data, in previous i used with clause when searched for "row generator"

I Using Oracle11g Db

i think problem in Db User Rights or in Oracle11g, i have no idea

[Updated on: Wed, 08 August 2018 05:12]

Report message to a moderator

Re: Cursor For Loop Auto Increment [message #670986 is a reply to message #670985] Wed, 08 August 2018 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
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 #670988 is a reply to message #670986] Wed, 08 August 2018 06:32 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

This One works for me.
select DISTINCT a.code || ' - ' || a.code_type AS XYZ
  from code_mast a,
       type_mast b,
       table(cast(multiset
                  (select DISTINCT level
                     from dual
                   connect by level <= regexp_count(b.code, ' ') + 1) as
                  sys.odciNumberList))
 where userS = 'ANIL'
 order by 1;
Thanks....

But i dont know why With Clause not Working...

Once again Thank you very much.
Re: Cursor For Loop Auto Increment [message #670989 is a reply to message #670988] Wed, 08 August 2018 07:53 Go to previous messageGo to next message
cookiemonster
Messages: 13199
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your version of forms may not recognize WITH as valid syntax.
If you created a view based on the query forms should let you use that with no issues.
Re: Cursor For Loop Auto Increment [message #670990 is a reply to message #670989] Wed, 08 August 2018 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
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 #670992 is a reply to message #670990] Wed, 08 August 2018 10:09 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Now showing data...

Here it is what i wanted...
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');
--Result is
--xyz---
B - BOOK
C - CAMERA
A - AUTO

Thank you...
Re: Cursor For Loop Auto Increment [message #670993 is a reply to message #670989] Wed, 08 August 2018 10:14 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Hi cookiemonster

i am not using it in Forms, by the way my Query is resolved

Thank you very much. I appreciate your reply...

[Updated on: Wed, 08 August 2018 10:16]

Report message to a moderator

Re: Cursor For Loop Auto Increment [message #670995 is a reply to message #670992] Wed, 08 August 2018 13:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #670999 is a reply to message #670998] Thu, 09 August 2018 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry can't help on Forms error.
Generally the solution, as cookiemonster mentioned, is to create a view based on the query text and query this view in Forms.

Re: Cursor For Loop Auto Increment [message #671008 is a reply to message #670999] Thu, 09 August 2018 05:40 Go to previous messageGo to next message
cookiemonster
Messages: 13199
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.
Re: Cursor For Loop Auto Increment [message #671009 is a reply to message #670989] Thu, 09 August 2018 05:55 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Used View in the end... Smile
Re: Cursor For Loop Auto Increment [message #671011 is a reply to message #670999] Thu, 09 August 2018 05:58 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
This topic helps alot Thank you.. Used View in the end...
Re: Cursor For Loop Auto Increment [message #671012 is a reply to message #671008] Thu, 09 August 2018 06:07 Go to previous message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
cookiemonster wrote on Thu, 09 August 2018 16:10
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.
now Clear all doubt...
Quote:
And don't tell us you're not using forms when you are very obviously using forms - that's just silly.
Actually at that time i am not used those in Form, when used Stuck.. Smile

Thank You...
Previous Topic: Set Property for Form Menu_Module
Next Topic: how to change tab canvas direction in 10g
Goto Forum:
  


Current Time: Wed Aug 15 23:20:25 CDT 2018