Hi, I am using Oracle APEX 21.2.0. The help system says that I can write an inner select statement as a SQL Expression.
I've a requirement to retrieve a value that's dependent on another value in the main query. It's like using post-query in Oracle Forms to get a description based on an ID value. I just mentioned Oracle forms, in case, you used it before. I tried to simulate the situation on apex.oracle.com. I created an interactive grid with the following query,
select empno, deptno from emp
then right clicked a column> chose "create column" set it's source type to "SQL Expression" and wrote the following query,
(select dname from dept where deptno = :DEPTNO)
It displayed error,
Make sure that you use valid alias names for your columns.
So, I added an alias to the inner select as follows....
(select dname from dept where deptno = :DEPTNO) DNAME
And it was validated successfully.
When ran the form, it showed error "Missing right parenthesis". And here is the resulting query...
select * from(
select a.*,row_number() over (order by null) apx$rownum from(
select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
from (
select "DEPTNO","EMPNO","DNAME"
from ((
select /*+ qb_name(apex$inner) */d."DEPTNO",d."EMPNO",d."DNAME" from (
select "DEPTNO","EMPNO",((select dname from dept where deptno = :DEPTNO) DNAME)"DNAME" from (
SELECT empno, deptno from emp
) d
) d
)) i
) i where 1=1
)a
)where apx$rownum<=:p$_max_rows
I've created a simulation on apex.oracle.com which uses Oracle APEX 21.2.4. In this version, it did not ask for an alias for the inner select statement and ran successfully, but did not retrieve any data.
P.S. I know that I can add the sql expression's query to the main query as a correlated subquery, but I need to separate queries to avoid the query getting big, and I need to know why it's not working as the help system says.
Here is the credentials for the simulation...
ws= forhelp
un= forhelp
pwd= forhelppwd$
app= test
page= home