APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

APEX_ITEM Add/Delete Row and make button clickable

hiddenonearthJan 25 2021

Hello,
for a task I need to create a flexible number of apex_items. For this I am using the APEX_ITEM API. Currently I can define the number of Items to be shown. Unfortunately, I didn't find a way to add or delete items one by one.
Furthermore, one of my items is being shown as a button link, where the user should have the option to click on it with a confirmation message to open a new web application. I can create the buttons, but not the functionality.
What I tried was following:
SELECT LEVEL AS item_no,
APEX_ITEM.TEXT(p_idx => 1, p_attributes => NULL) as Criteria, // shown as link button
APEX_ITEM.TEXT(p_idx => 2, p_attributes => NULL) as CriteriaOK, // shown as link button
APEX_ITEM.TEXT(p_idx => 3, p_attributes => NULL) as CriteriaNotOK,
APEX_ITEM.SELECT_LIST(4, null, '1;1,2;2,3;3,4;4') as frequency,
APEX_ITEM.DATE_POPUP2(p_idx => 5, p_attributes => 'DD.MM.YYYY') as date,
APEX_ITEM.text(p_idx => 6, p_attributes => NULL) as time
from dual
connect by level <= to_number(:P12_NUMBER_ITEMS); // Example with number 4
Now I want to have a two buttons or something similar to add or delete rows on each level.

For the CriteriaOK and CriteriaNotOk items I changed the type of the columns to link and changed the link properties to (Example CriteriaOK)
Link: javascript:void(null);
Link text: <span class="t-Icon fa fa-check" aria-hidden="true"></span>
Link attributes: data-id="#CriteriaOK#" class="add t-Button t-Button--success t-Button--simple t-Button--stretch"
Then I created a dynamic action to open the second tab when a button is pressed, but it doesn't fire since the link url is declared as javascript:void(null). Since the user has to confirm the action I can't put the web url from the application to the link attribute of the column. Also the links get unchecked when i press another link button.
Finally, I need to display the entries of the apex_items after the save process in the same way.
Any idea on how to solve this problems?
Thanks. Any help is appreciated.

lmconsite

Please, try this:

WITH t1 AS
(SELECT 101 AS ID
, 1 AS a
, NULL AS b
, 5 AS c
FROM DUAL
UNION ALL
SELECT 102
, NULL
, 2
, 5
FROM DUAL
UNION ALL
SELECT 101
, 3
, 5
, 7
FROM DUAL
UNION ALL
SELECT 101
, 1
, NULL
, NULL
FROM DUAL)
, t2 AS
(SELECT 101 AS ID
, 1 AS x
FROM DUAL
UNION ALL
SELECT 101
, 5
FROM DUAL
UNION ALL
SELECT 102
, 5
FROM DUAL
UNION ALL
SELECT 102
, 2
FROM DUAL)
SELECT *
FROM t1
WHERE t1.ID = 101
AND EXISTS
(SELECT *
FROM t2
WHERE t1.ID = t2.ID
AND t1.a = t2.x OR t1.a IS NULL
AND t1.b = t2.x OR t1.b IS NULL
AND t1.c = t2.x OR t1.c IS NULL);

Regards,

Luis

Aketi Jyuuzou

Hi lmescher.
Unfortunately, I think that your query is wrong.
for instance

WITH t1 AS
     (SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
      SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
      SELECT 101, 5,99,99 FROM DUAL UNION ALL
      SELECT 101, 1, NULL, NULL FROM DUAL)
   , t2 AS
     (SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
      SELECT 101 , 5 FROM DUAL UNION ALL
      SELECT 101 , 3 FROM DUAL UNION ALL
      SELECT 101 , 5 FROM DUAL UNION ALL
      SELECT 101 , 7 FROM DUAL)
SELECT *
FROM t1
WHERE t1.ID = 101
AND EXISTS
(SELECT *
FROM t2
WHERE t1.ID = t2.ID
AND t1.ColA = t2.ColX OR t1.ColA IS NULL
AND t1.ColB = t2.ColX OR t1.ColB IS NULL
AND t1.ColC = t2.ColX OR t1.ColC IS NULL);

 ID  ColA  ColB  ColC
---  ----  ----  ----
101     1  null     5
101     3     5     7
101     5    99    99
101     1  null  null

If you has mistake for combination of "and" and "or",
Query becomes below.
But ResultSet remains wrong ResultSet.

WITH t1 AS
     (SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
      SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
      SELECT 101, 5,99,99 FROM DUAL UNION ALL
      SELECT 101, 1, NULL, NULL FROM DUAL)
   , t2 AS
     (SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
      SELECT 101 , 5 FROM DUAL UNION ALL
      SELECT 101 , 3 FROM DUAL UNION ALL
      SELECT 101 , 5 FROM DUAL UNION ALL
      SELECT 101 , 7 FROM DUAL)
SELECT *
FROM t1
WHERE t1.ID = 101
AND EXISTS
(SELECT *
FROM t2
WHERE t1.ID = t2.ID
  AND (t1.ColA = t2.ColX OR t1.ColA IS NULL)
  AND (t1.ColB = t2.ColX OR t1.ColB IS NULL)
  AND (t1.ColC = t2.ColX OR t1.ColC IS NULL));

 ID  ColA  ColB  ColC
---  ----  ----  ----
101     1  null  null

Aketi Jyuuzou

This is an interesting question.

WITH t1 AS
     (SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
      SELECT 102, NULL, 2, 5 FROM DUAL UNION ALL
      SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
      SELECT 101, 5,99,99 FROM DUAL UNION ALL
      SELECT 101, 1, NULL, NULL FROM DUAL)
   , t2 AS
     (SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
      SELECT 101 , 5 FROM DUAL UNION ALL
      SELECT 102 , 5 FROM DUAL UNION ALL
      SELECT 102 , 2 FROM DUAL UNION ALL
      SELECT 101 , 3 FROM DUAL UNION ALL
      SELECT 101 , 5 FROM DUAL UNION ALL
      SELECT 101 , 7 FROM DUAL)
select *
  from t1 a
 where ID = 101
   and exists(select 1
                from t2 b
               where b.ID = a.ID
              having (a.ColA is null or max(case when b.ColX = a.ColA then 1 else 0 end) = 1)
                 and (a.ColB is null or max(case when b.ColX = a.ColB then 1 else 0 end) = 1)
                 and (a.ColC is null or max(case when b.ColX = a.ColC then 1 else 0 end) = 1));

 ID  ColA  ColB  ColC
---  ----  ----  ----
101     1  null     5
101     3     5     7
101     1  null  null

Furthermore,
We can use below alternative solution.
Below alternative solution is used Boolean arithmetic (http://www.allaboutcircuits.com/vol_4/chpt_7/2.html)

WITH t1 AS
     (SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
      SELECT 102, NULL, 2, 5 FROM DUAL UNION ALL
      SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
      SELECT 101, 5,99,99 FROM DUAL UNION ALL
      SELECT 101, 1, NULL, NULL FROM DUAL)
   , t2 AS
     (SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
      SELECT 101 , 5 FROM DUAL UNION ALL
      SELECT 102 , 5 FROM DUAL UNION ALL
      SELECT 102 , 2 FROM DUAL UNION ALL
      SELECT 101 , 3 FROM DUAL UNION ALL
      SELECT 101 , 5 FROM DUAL UNION ALL
      SELECT 101 , 7 FROM DUAL)
select *
  from t1 a
 where ID = 101
   and exists(select 1
                from t2 b
               where b.ID = a.ID
              having max(case when b.ColX = a.ColA or a.ColA is null then 1 else 0 end)
                   * max(case when b.ColX = a.ColB or a.ColB is null then 1 else 0 end)
                   * max(case when b.ColX = a.ColC or a.ColC is null then 1 else 0 end) = 1);

This thread is dealt logic which is "max(case when P(X) then 1 else 0 end) = 1" is for some X:P(X).
And this thread deals alike question.
2067650

I mentioned alike logic in this thread.
2040085

I recommend these articles.
http://www.dbazine.com/ofinterest/oi-articles/celko5
http://www.dbazine.com/ofinterest/oi-articles/celko18

lmconsite

Aketi,

I stand corrected.

Regards,

Luis

pshah2k

Thanks a lot for your solution. It works like a charm. I can now add one more thing that I should try to leran.

I also want to thank others that responded.

1 - 5

Added on Jan 25 2021

#apex-discussions

6 comments

2,958 views

2 mentions

APEX

APEX_ITEM Add/Delete Row and make button clickable

Comments

Post Details