This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Apr 9, 2013 1:32 AM by user134954 RSS

Computation or Process ?

user134954 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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) Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Something like
    select distinct test
    from testtable
    where instr( replace( P4_LINIE, '-9' ) || ':', ':' || test || ':' ) > 0 
  • 7. Re: Computation or Process ?
    Howard (... in Training) Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Pro
    Currently Being Moderated
    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) Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Pro
    Currently Being Moderated
    ??? 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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points