I am trying to create a Help system for my Apex 5 application which, based upon the passed application id and page number, queries a small database table and extracts the URL to redirect the user to for the help information. My Dynamic action is working correctly for all of the pages listed in the database table however I wanted there to be a default URL specified for pages that currently did not have a help page defined, i.e. no corresponding row in the table.
After some searching I found the COALESCE verb and created the following SQL statement which when I tested it inside SQL Developer worked flawlessly :-
SELECT COALESCE((
SELECT URL
FROM HELP_URLS
WHERE APP_ID = '137'
AND PAGE_NUM = '9'
), 'https://myCompany.com/wiki/index.php/Under_Construction')
FROM DUAL
When I set the PAGE_NUM value to a value that was in the database table the appropriate URL was returned and when I specified a PAGE_NUM that was not in the table I got the 'Under_Construction' URL returned.
The problem is that when I attempted to use the same SQL command, with bind parameters instead of fixed values, the following error was reported when I pressed the validate button :-
Column name "COALESCE((SELECTURLFROMHELP_URLSWHEREAPP_ID='137'ANDPAGE_NUM='9'),
'HTTPS://MYCOMPANY.COM/WIKI/INDEX.PHP/UNDER_CONSTRUCTION')" is invalid for the
SQL query. Make sure that you use unique alias names for your columns.
Please would you tell me how, in a dynamic action for a page, I can either get the above SQL command to work or an alternative way of getting the same effect.
Database Version : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Apex Version : 5.0.3.00.03
PL/SQL Version : Release 12.1.0.1.0 - Production