1 2 Previous Next 15 Replies Latest reply: Apr 9, 2013 3:32 AM by user134954 RSS

    Computation or Process ?

    user134954
      Hi there,

      I have the following values in my variable :P4_Linie

      -9:TEST1:TEST2:TEST3

      Now I know how to convert the ":" into "," and how to replace the -9. The -9 is the "all selector" of a checkbox pluging meaning if all checkboxes are checked the value -9 is given before the checkboxes data so sometimes it is there and sometimes not.

      I know how to do this in a select statement:
      SELECT *  FROM mv_auswertung WHERE INSTR (
                REPLACE (:P4_LINIE, '-9', '')
                ':'||Linie||':') > 0
      Now I get TEST1,TEST2,TEST3 and now I can use this in a select into query.

      But I need apex to do this via PL/SQL process or computation so the item is given to the next page in my items to submit.

      I need to submit TEST1,TEST2,TEST3 for my IR. Hoping you can help.

      Thx in advance

      Thorsten
        • 1. Re: Computation or Process ?
          Nicolette
          Thorsten

          Replace is also a pl/sql function.
          If the new value needs to be submitted you would need a page item to hold that value.

          The computation would then belong to the new item and be
          return REPLACE (:P4_LINIE, '-9', '');
          In a procedure it would be
          :P4_new_item := REPLACE (:P4_LINIE, '-9', '');
          If you have apex 4.2 then you can define the new item to be submitted, on the refresh of a region, is a region setting.

          Just remember that the value of a page item is always a string.
          So something like this will not work
          select *
          from   some_table
          where  some_column in(:p4_new_item) 
          Nicolette
          • 2. Re: Computation or Process ?
            Howard (... in Training)
            Thorsten,

            Hmm. I have a few ideas, as well.

            Do you want the value of :P4_LINIE actually changed to the new value without the "-9:" on page 4 or do you just want the new/changed value available to the new page?

            (Related question) Do you want the computation to happen before page 4 is displayed or just before transferring to the new page?

            Do you have a Submit button to get to the next page; what's the mechanism for transferring to the new page?

            There are two (or more) ways to do this.
            -- 1) Merely reference/use :P4_LINIE on the new page? But I think we have to ensure :P4_LINIE is saved in the session state for this to work.
            -- 2) Pass :P4_LINIE to :Px_LINIE on the transfer to page x.

            But I need to know how you (want to) transfer to the new page.

            Howard
            • 3. Re: Computation or Process ?
              user134954
              Hi Howard,

              I need to transfer the selected items so I can use them in a "select into" query.

              -9 is the "all selector" and should be cut if "all" is selected.

              Just check:

              http://apex.oracle.com/pls/apex/f?p=39514:2:0::NO:::

              If I continue to the next page the value selected should be used in an interactive report via URL.

              So my URL should not be like :

              f?p=119:6:9998365278211::NO::P35_TEST:-9:TESTVALUE1:TESTVALUE2

              but like:

              f?p=119:6:9998365278211::NO::P35_TEST:TESTVALUE1,TESTVALUE2
              • 4. Re: Computation or Process ?
                user134954
                I am close now. If i try a computation return colon seperated values

                Select distinct TEST
                from TESTSTABLE where TEST
                in (SELECT TEST
                  FROM TESTTABLE
                 WHERE INSTR (
                          ',' || TRANSLATE (REPLACE (:P35_TEST, '-9', ''), ':', ',') || ',',
                          ','||TEST||',') > 0)
                 
                The URL now cuts the -9 BUT the values in the URL are still shown as

                TEST1:TEST2 not as TEST1,TEST2
                • 5. Re: Computation or Process ?
                  Howard (... in Training)
                  I always start with a simple-minded approach. (When one has a simple mind, ....)

                  Corrections needed. A couple moments please.

                  Howard

                  Edited by: Howard (... in Training) on Mar 28, 2013 9:24 AM
                  • 6. Re: Computation or Process ?
                    ascheffer
                    Something like
                    select distinct test
                    from testtable
                    where instr( replace( P4_LINIE, '-9' ) || ':', ':' || test || ':' ) > 0 
                    • 7. Re: Computation or Process ?
                      Howard (... in Training)
                      Let's try this again:

                      How about:
                      This assumes you set up a button to do the transfer:
                      Under Action When Button Clicked, select "Page in this Application", and put Page 35.
                      Under "Set These Items" put P35_LINIE (Note: No colons, ampersands, or periods in the item name here.)
                      Under "With These Values" put &P4_LINIE. (Note: use & and . here.)

                      I shy away from coding parameters -- all but the simplest cases -- in URLs.

                      Howard
                      • 8. Re: Computation or Process ?
                        user134954
                        Anton,

                        this will just give me the first value of the string. If its TEST1:TEST2:TEST3

                        the result will only be TEST1
                        • 9. Re: Computation or Process ?
                          user134954
                          Howard,

                          the Plugin uses : as delimiter. So the values will be look like TEST1:TEST2. I want them to be TEST1,TEST2

                          BTW:

                          I now got the values in my URL....


                          my URL shows :P35_TEST:TESTVALUE1:TESTVALUE2

                          But if try
                          Select :P35_TEST from dual 
                          I only get TESTVALUE1 ....

                          Dont know why.
                          • 10. Re: Computation or Process ?
                            Howard (... in Training)
                            In the URL, the first ":" terminates the value passed to :P35_ITEM. So all it parses out is "TESTVALUE1".

                            Howard
                            • 11. Re: Computation or Process ?
                              Howard (... in Training)
                              If we don't pass the value to page 35 but instead only reference the value from page 4, we won't have a URL problem. But for your processing on page 35, do you need the ":" replaced by "," anyway? I assume the answer is yes.

                              Howard
                              • 12. Re: Computation or Process ?
                                user134954
                                Yes. The final thing should be an interactive report with many variables in the url.

                                Thats the only way to save the IR. So the variables are set as IR-Filters.

                                Because of that, the URL should hold all variables with a ",".

                                I have already tried to reference the variables and this works. But I need them in my URL.
                                • 13. Re: Computation or Process ?
                                  Howard (... in Training)
                                  ??? Okay. This shows how to remove the "9" and replaces other ":" by ",". What else do you need?
                                  select REPLACE( REPLACE('-9:TEST1:TEST2:TEST3', '-9:', ''), ':', ',') from dual; 
                                  Howard
                                  • 14. Re: Computation or Process ?
                                    Nicolette
                                    Thorsten

                                    You mean like [url http://apex.oracle.com/pls/apex/f?p=VANBAREN_FORUM_TRY_OUT:CHECKBOX1&c=VANBAREN]this.

                                    This method uses a hidden item to store the string with comma's.
                                    A computation that replaces the *:* with *,* in the checkbox item and sets the result in the hidden item.
                                    And a branch that sets the items on the recivieving page and submits the hidden item.

                                    Try out the [url http://apex.oracle.com/pls/apex/f?p=VANBAREN_FORUM_TRY_OUT:CHECKBOX1&c=VANBAREN]demo and see the consequence of not checking all the options.

                                    Nicolette
                                    1 2 Previous Next