Search code examples
oracleplsqloracle-apexlov

Trying to create a "List of Values " including a table value and numbers below it

So basically say I have a table called "Device" and then one of the columns is "Quantity," what if I wanted to create a list of values that takes that number, say the quantity is 4, and the values are (quantity - 1) until !> 0, so in this case (4, 3, 2, 1)

I am using Oracle APEX and am assuming I need a dynamic LOV based on a sql query, but not sure how to get this. I've never used a for loop with PL/SQL

Thanks

Solution

  • This should do it. Make sure that where I've put /* xxx */ you include a where clause that comes up with only 1 record. Most likely, you will use the ID of the Device table here.

    SELECT     ROWNUM display_value
,          ROWNUM return_value
FROM       DUAL
CONNECT BY ROWNUM <= (SELECT Quantity FROM Device WHERE /* xxx */)
ORDER BY   ROWNUM DESC;