3 Replies Latest reply: Jun 20, 2014 9:37 AM by Mike Kutz RSS

    Run through apex_application.g_f(i)(ii) Error

    pleNn

      Heyho,

       

       

      I try to run through my arrays with two loops like the follwoing:

       

      FOR I IN 1 .. APEX_APPLICATION.G_F01.COUNT

      LOOP

       

      FOR II IN 1 .. 5

      LOOP

       

      PROJEKTARRAY := 'apex_application.g_f0'||(II+1);

       

      CNT := 0;

      Select Count(*) INTO CNT FROM USERPROJEKT WHERE USERPROJEKT.USER_ID = apex_application.g_f01(i) and (ii) = USERPROJEKT.PROJEKT_ID;

       

      IF CNT = 0 AND PROJEKTARRAY||(i) = 1 Then

                 insert into USERPROJEKT(ID, USER_ID, PROJEKT_ID) VALUES (33, apex_application.g_f01(i),ii);

       

      ....

       

      I always getting an error when I try to run that if-clause...how is the syntax to get my right array?

       

      pleNn

        • 1. Re: Run through apex_application.g_f(i)(ii) Error
          Mike Kutz

          Note 1:

          PROJEKTARRAY is a STRING, not a variable

          You are actually trying to compare the string ('apex_aplication.g_f01' || i)   to the numerical value 1.

           

          Note 2

          select count(*) into cnt ..
          if cnt = 0 ...
          then
            insert ...
          end if;
          

          This needs to be rewritten as a MERGE statement

           

          Note 3

          The Oracle database loves to work on SETS of data.

          Your best bet will be to save everything into an APEX_COLLECTION and do a single SQL statement;

          (For the non-APEX sql developers, APEX_COLLECTIONS can be viewed as a generic table web-session level GTT.  desc APEX_COLLECTIONS to see what I mean.)

           

          apex_collection.create_or_truncate_collection( 'PROCESS_THIS' );
          apex_collection.add_members( 'PROCESS_THIS',
             ,c001 => apex_application_global.g_f01
             ,c002 => apex_application_global.g_f02
             ,c003 => apex_application_global.g_f03
             ,c004 => apex_application_global.g_f04
             ,c005 => apex_application_global.g_f05
          );
          -- place MERGE statement here
          

           

          In order to help you with the MERGE statement:

          We'll need more information on your USERPROJEKT table (ie CREATE TABLE statement) and some example data (ie INSERT statements)

          Also, an example input from your Tabular Form would be nice.

          Re: 2. How do I ask a question on the forums?

           

          Don't forget .. all 4-digits of your Database version.

           

          MK

          • 2. Re: Run through apex_application.g_f(i)(ii) Error
            pleNn

            thanks mike :-),

             

            How do I run through my collection then? Would like do do like :

             

            apex_collection.get_member(i) or something..

             

            In order to help you with the MERGE statement:

            We'll need more information on your USERPROJEKT table (ie CREATE TABLE statement) and some example data (ie INSERT statements)

            Also, an example input from your Tabular Form would be nice.

             

            Never heard of merge statement before :-)

             

            The follwoing tables im using :

             

            USERPROJEKT(ID,USER_ID,PROJEKT_ID)

            PROJEKT(ID,NAME)

            USER(ID, Firstname,Lastname , etc..)

             

            Now I set up an report in Apex like the follwoing:

             

            ID(Hidden)    Accountname     Status     Project 1     Project 2      Project 3     .... 

            (f01)                                               (f02)          (f03)           (f04)               (f05)

            -------------------------------------------------------------------------------------------------------

            1                        User 1              Active            0               0                    1

             

            2                         User 2             Inactive          1               1                    0

             

            Now I checked out my report and put the data in the f0..-Arrays, so now I want to update the table Userprojekt-Table wether the Project (0 or 1 in the Report(checkboxes)) is set or unset.

            I want to go through each line and update my values for each User.

             

            Database Version: 11g2 / Apex 4.2

             

            pleNn

            • 3. Re: Run through apex_application.g_f(i)(ii) Error
              Mike Kutz

              You have not yet provided enough information:  Re: 2. How do I ask a question on the forums?

               

              How the data from USER,PROJEKT, and USERPROJEXT tables map to your Report is very unclear.

              I recommend you provide the SQL statement that generates the data.

               

              As such, you must include the INSERT statements for the tables.

              (hint:

                we are volunteers.  we're helping you out for free.

                the less work we have to do, the happier we are to help you.

                the CREATE TABLE statements with constraints (pk/fk/checks)

                actually help us understand your data model.

              )

               

              I highly recommend you include some data that does not show up in the Report.

               

              A "before" and "after" display of USERPROJEXT is needed so that we can test out our MERGE code.

               

              Finally, a detailed explanation of what you want to happen (and what you want to NOT happen)

              needs to be included.

               

              so far...

              (this is more for other volunteers)

              From what I am guessing what you want, I'm pretty sure it can be done with a MERGE statement after you put the data into an APEX_COLLECTION.

               

              According to the data you provided, the APEX_COLLECTIONS table will look like this:

              with simulated_apex_collections( collection_name, seq_id, C001, C002, C003, C004, C005, C006 ) as
              ( select 'PROCESS_THIS', 1
                       ,'1', 'User 1', 'Active',   '0', '0', '1' from dual union all
                select 'PROCESS_THIS', 2
                       ,'2', 'User 2', 'Inactive', '1', '1', '1' from dual
              )
              select * from simulated_apex_collections where collection_name='PROCESS_THIS';
              

               

               

              MK