"Function Body returning SQL Query" with multiple queries.

Here are a couple of things to consider when creating a report using a Function Body that returns an SQL Query as the data source and using "IF" to decide which query will be executed.

1. "ELSE" must be present.

   The query in the "ELSE" condition will be parsed, and then you will see the columns in the report region.

   

   If you don't include an "ELSE" condition, even if the other queries are correct, an error will appear, and you won't be able to save.

   

2. All columns must be present in your queries

   If you missed any column in one of the queries, the code will compile, and you'll be able to save. But when executing that particular query from the application, the missing column will cause an error in the report.

   For example, I'm removing the "PRODUCT_DESCRIPTION" column from the second query, and the code is still validated.

   

   But when I select the option to execute that query, the error shows up.

   

   If I change the option, the error disappears, and the first query is successfully executed. Only the query with the missing column will fail.

   

   Notice that when I remove the column name condition, all the columns from the table are present in the second query now, but still only those in the "ELSE" condition will be shown in the report.