Hello!
We have a table (LEADS) with multiple sources, one column defines the source and each source uses or not some of the columns like this:
We want to show a report where when a source is selected, only the columns with content get displayed and the columns with null values get hidden like this:
To make it work dynamically we made the following model:
And used this Function Body returning SQL Query in an Interactive Report where P2_SOURCE is a select list with the sources and returns the SourceID
DECLARE
cols varchar2(2000);
BEGIN
IF(:P2_SOURCE IS NOT NULL) THEN
select LISTAGG(b.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY b.ID) INTO cols
from SOURCES_COLUMNS a
left join COLUMNS b
on a.COLUMN_ID = b.ID
WHERE a.SOURCE_ID = :P2_SOURCE;
RETURN 'SELECT ' || cols|| ' FROM LEADS WHERE SOURCE = :P2\_FORM';
ELSE
RETURN 'SELECT \* from LEADS';
END IF;
END;
This gives us an error when a column from the (SELECT * from LEADS) is not returned in the dynamic sql ('SELECT ' || cols|| ' FROM LEADS WHERE SOURCE = :P2_FORM')
I've come across some post where people do this without any problem by enabling the "Use Generic Column Names" option,
Related posts:
https://rutveekprajapati.blogspot.com/2021/05/how-to-create-report-with-dynamic-query.html
https://stackoverflow.com/questions/61967453/how-to-create-interactive-classic-report-with-dynamic-sql
I'm currently using APEX 21.1.7 and that option is not there for me (See Screenshot below)
Any clue on how to do this without that option or how to enable it?