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.

How to dynamically set the label

312291Sep 24 2004 — edited Sep 24 2004

Is it possible to dynamically set the label of an item based on a value in a database table?

For e.g., there is an item with a name P1_CATEGORY. In a lookup table, the label for P1_CATEGORY could be mapped as either "Category" or "Section" or "Type" depending upon different situations. So, at run time, can the label be dynamically displayed with one of those three values depending upon the situation? If so, how can this be done?

Thanx in advance.

189821

SQL>WITH t AS (
  2          SELECT 'Budweiser' BEER_NAME, 17 QUANTITY, 1 PRIORITY FROM DUAL UNION ALL
  3          SELECT 'Budweiser', 16, 2 FROM DUAL UNION ALL
  4          SELECT 'Corona', 22, 1 FROM DUAL UNION ALL
  5          SELECT 'Heineken', 9, 1 FROM DUAL UNION ALL
  6          SELECT 'Heineken', 11, 3 FROM DUAL UNION ALL
  7          SELECT 'Guinness', 15, 2 FROM DUAL UNION ALL
  8          SELECT 'Corona', 2, 2 FROM DUAL UNION ALL
  9          SELECT 'Heineken', 13, 2 FROM DUAL),
 10       q AS
 11       (SELECT MAX(quantity) AS maxq
 12          FROM t)
 13  SELECT   BEER_NAME, LEAST(quantity + 10 - l * 10, 10) AS case_quantity, PRIORITY
 14      FROM t,
 15           (SELECT     LEVEL AS l
 16                  FROM q
 17            CONNECT BY LEVEL <= CEIL(maxq / 10))
 18     WHERE l <= CEIL(QUANTITY / 10)
 19  ORDER BY 1, 3, 2 DESC;

BEER_NAME CASE_QUANTITY   PRIORITY
--------- ------------- ----------
Budweiser            10          1
Budweiser             7          1
Budweiser            10          2
Budweiser             6          2
Corona               10          1
Corona               10          1
Corona                2          1
Corona                2          2
Guinness             10          2
Guinness              5          2
Heineken              9          1
Heineken             10          2
Heineken              3          2
Heineken             10          3
Heineken              1          3

Urs

Flake

Thanks buddy,

You got it. With your permission, I slightly replaced the query as follows:

WITH t AS (
  select * from beers),
        q AS
        (SELECT MAX(quantity) AS maxq
           FROM t)
   SELECT   BEER_NAME, LEAST(quantity + 10 - l * 10, 10) AS case_quantity, PRIORITY
       FROM t,
            (SELECT     LEVEL AS l
                   FROM q
             CONNECT BY LEVEL <= CEIL(maxq / 10))
      WHERE l <= CEIL(QUANTITY / 10)
   ORDER BY 1, 3, 2 DESC;

Thanks for taking pain to write a query.

Regards,
...

Aketi Jyuuzou

I like model clause :D

WITH t AS (
SELECT 'Budweiser' BEER_NAME, 17 QUANTITY, 1 PRIORITY FROM DUAL UNION ALL
SELECT 'Budweiser', 16, 2 FROM DUAL UNION ALL
SELECT 'Corona', 22, 1 FROM DUAL UNION ALL
SELECT 'Heineken', 9, 1 FROM DUAL UNION ALL
SELECT 'Heineken', 11, 3 FROM DUAL UNION ALL
SELECT 'Guinness', 15, 2 FROM DUAL UNION ALL
SELECT 'Corona', 2, 2 FROM DUAL UNION ALL
SELECT 'Heineken', 13, 2 FROM DUAL)
select BEER_NAME,newQuan as QUANTITY,PRIORITY
  from t
 model return updated rows
partition by(RowNum as PID,BEER_NAME,PRIORITY)
dimension by(0 as soeji)
measures(QUANTITY,0 as newQuan)
rules ITERATE (999) UNTIL ((ITERATION_NUMBER+1)*10 > QUANTITY[0])
(newQuan[ITERATION_NUMBER] = Least(10,QUANTITY[0]-10*ITERATION_NUMBER))
ORDER BY BEER_NAME, PRIORITY, newQuan DESC;

BEER_NAME  QUANTITY  PRIORITY
---------  --------  --------
Budweiser        10         1
Budweiser         7         1
Budweiser        10         2
Budweiser         6         2
Corona           10         1
Corona           10         1
Corona            2         1
Corona            2         2
Guinness         10         2
Guinness          5         2
Heineken          9         1
Heineken         10         2
Heineken          3         2
Heineken         10         3
Heineken          1         3

Aketi Jyuuzou

I like recursive with clause better than model clause :-)
Hahaha I used PostgreSQL8.4 because I do not have Oracle11gR2 :8}

WITH recursive tmp AS (
SELECT 'Budweiser' BEER_NAME, 17 QUANTITY, 1 PRIORITY UNION ALL
SELECT 'Budweiser', 16, 2 UNION ALL
SELECT 'Corona', 22, 1 UNION ALL
SELECT 'Heineken', 9, 1 UNION ALL
SELECT 'Heineken', 11, 3 UNION ALL
SELECT 'Guinness', 15, 2 UNION ALL
SELECT 'Corona', 2, 2 UNION ALL
SELECT 'Heineken', 13, 2),
rec(BEER_NAME,PRIORITY,QUANTITY,rest) as(
select BEER_NAME,PRIORITY,Least(10,QUANTITY),
QUANTITY-Least(10,QUANTITY) from tmp
union all
select BEER_NAME,PRIORITY,Least(10,rest),
rest-Least(10,rest)
  from rec
 where rest > 0)
select*from rec
ORDER BY BEER_NAME, PRIORITY, QUANTITY DESC,rest desc;

 beer_name | priority | quantity | rest
-----------+----------+----------+------
 Budweiser |        1 |       10 |    7
 Budweiser |        1 |        7 |    0
 Budweiser |        2 |       10 |    6
 Budweiser |        2 |        6 |    0
 Corona    |        1 |       10 |   12
 Corona    |        1 |       10 |    2
 Corona    |        1 |        2 |    0
 Corona    |        2 |        2 |    0
 Guinness  |        2 |       10 |    5
 Guinness  |        2 |        5 |    0
 Heineken  |        1 |        9 |    0
 Heineken  |        2 |       10 |    3
 Heineken  |        2 |        3 |    0
 Heineken  |        3 |       10 |    1
 Heineken  |        3 |        1 |    0

Flake

Guys,

Great, and thanks for participation. Same thing is done in different ways and teaching many ways to do the same thing is in itself an opportunity to learn for the rest of us and also a preparation for next contingency. It's also an opportunity to showcase one's talent and receive appreciation.

Thanks,
...

839702

metzguar wrote:

<PRE class=jive-pre><CODE class="jive-code jive-java">SQL>WITH t AS ( 2 SELECT 'Budweiser' BEER_NAME, 17 QUANTITY, 1 PRIORITY FROM DUAL UNION ALL 3 SELECT 'Budweiser', 16, 2 FROM DUAL UNION ALL 4 SELECT 'Corona', 22, 1 FROM DUAL UNION ALL 5 SELECT 'Heineken', 9, 1 FROM DUAL UNION ALL 6 SELECT 'Heineken', 11, 3 FROM DUAL UNION ALL 7 SELECT 'Guinness', 15, 2 FROM DUAL UNION ALL 8 SELECT 'Corona', 2, 2 FROM DUAL UNION ALL 9 SELECT 'Heineken', 13, 2 FROM DUAL), 10 q AS 11 (SELECT MAX(quantity) AS maxq 12 FROM t) 13 SELECT BEER_NAME, LEAST(quantity + 10 - l * 10, 10) AS case_quantity, PRIORITY 14 FROM t, 15 (SELECT LEVEL AS l 16 FROM q 17 CONNECT BY LEVEL <= CEIL(maxq / 10)) 18 WHERE l <= CEIL(QUANTITY / 10) 19 ORDER BY 1, 3, 2 DESC; BEER_NAME CASE_QUANTITY PRIORITY--------- ------------- ----------Budweiser 10 1Budweiser 7 1Budweiser 10 2Budweiser 6 2Corona 10 1Corona 10 1Corona 2 1Corona 2 2Guinness 10 2Guinness 5 2Heineken 9 1Heineken 10 2Heineken 3 2Heineken 10 3Heineken 1 3</CODE></PRE>

Urs

Now I'll try it, I'm puzzled long, Thanks for your reply!

1 - 6

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 22 2004

Added on Sep 24 2004

#apex-discussions

4 comments

360 views

APEX

How to dynamically set the label

Comments

Post Details