This content has been marked as final. Show 3 replies
If you really need to do that, you'd need to generate the SQL statement dynamically. That would also mean that any code that tries to fetch the data from the cursor created from this SQL statement would need to know how to fetch data without knowing the names of the columns in the result set at compile time. If you're using PL/SQL to fetch the data, that implies that you'd have to use dynamic SQL to fetch the data as well.
Generally, this is not something I would recommend trying to do. It's an awful lot of complexity to add to the system for a minor cosmetic benefit.
As Justin said, the column name must be hard-coded into the query. Hard-codeing a duynamic value requires dynamic SQL.
If you're using SQL*Plus, which has substitution variables, then it's not very hard.
Using the SQL*Plus COLUMN ... HEADING command, you can have SQL*Plus display another heading in place of the one hard-coded into the query.
-- Define substitution variable last_month COLUMN last_month_col NEW_VALUE last_month SELECT TO_CHAR ( ADD_MONTHS (SYSDATE, -1) , 'MON-YYYY' ) AS last_month_col FROM dual; -- Last_month can now be used anywhere in the query: select msi.segment1, sum(CASE WHEN mdh.period_start_date > trunc(add_months(sysdate,-1)) THEN mdh.sales_order_demand ELSE 0 END) AS "&last_month" from ...
Technically, this is not dynamic SQL, but it's slightly more work:
COLUMN last_month_col NEW_VALUE last_month SELECT deptno , dname AS "&last_month" FROM scott.dept; COLUMN month_usage_1 HEADING "&last_month" select msi.segment1, sum(CASE WHEN mdh.period_start_date > trunc(add_months(sysdate,-1)) THEN mdh.sales_order_demand ELSE 0 END) AS month_usage_1 from ...