Hi, is it possible to do something like a cascading variable where variable 1 filters down a select list for variable 2?
e.g. setting an activity year, or a specific department
EDIT: I reread the manual and found that on page 100 it indeed stated you can do items in a list based on another list. I am having trouble getting it to work in my own query as it just uses each line of the query as a new item rather than running the query. (Please note: The provided example works perfectly fine, it's just my own code that fails)
Is there a list of quirks or specific syntax it needs to follow exactly to work?
Edit - testing list:
No common table expressions
No whitespace after the opening quote mark but before the first word
A subquery in a subquery where the final subquery used the previous bind var resulted in
User: Error executing SELECT statement for list:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
(not sure about this one)
Make sure to use fetch first 1000 row only if you suspect big query, otherwise program will freeze
testing prefix: i don't think you can use it to create reusable code; only theoretical use case for that anyway was if you don't have view privilege (e.g. ROLE-based access to read certain table but create view requires direct access grant)
e.g. setting an activity year, or a specific department
EDIT: I reread the manual and found that on page 100 it indeed stated you can do items in a list based on another list. I am having trouble getting it to work in my own query as it just uses each line of the query as a new item rather than running the query. (Please note: The provided example works perfectly fine, it's just my own code that fails)
Code:
select * from all_tab_columns
where owner = &<name="Owner"
type="string"
list="select username from all_users order by username">
and table_name = &<name="Table"
type="string"
list="select table_name from all_tables
where owner = :owner
order by table_name">
order by column_idIs there a list of quirks or specific syntax it needs to follow exactly to work?
Edit - testing list:
No common table expressions
No whitespace after the opening quote mark but before the first word
A subquery in a subquery where the final subquery used the previous bind var resulted in
User: Error executing SELECT statement for list:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
(not sure about this one)
Make sure to use fetch first 1000 row only if you suspect big query, otherwise program will freeze
testing prefix: i don't think you can use it to create reusable code; only theoretical use case for that anyway was if you don't have view privilege (e.g. ROLE-based access to read certain table but create view requires direct access grant)
Last edited:
