PL/SQL Functions in column alias

[Jitwad]

Hello.

Just wondering if this can be done.....

basic example:

SELECT item, SUM(quantity) AS SUBST('200209', 5)
FROM blah
WHERE blah....etc.

Can I use a function (such as SUBSTR) in the alias for a column? The reason I ask is I must produce a report from an Oracle DB in PL/SQL where a series of column headers must be dynamically altered depending on the date the report is generated.

Thnx

 

[rcurva]

Your example can't be done.

My suggestion is, create a separate query to create your header. For example;

SET HEADING OFF
--Header query
SELECT 'ITEM', to_char(sysdate,'YYYYMMDD')
FROM dual;
--Facts query
SELECT item, SUM(quantity) AS SUBST('200209', 5)
FROM blah
WHERE blah....etc.

But ofcourse, you need to adjust the headers to be in-line with the data.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

What tool do you use? In Reports you may use & in lables. In sql*plus you may use COLUMN command to change column HEADING.

 

[Jitwad]

Running Oracle 8 DB nad editing with Golden (Benthic Software) and PLEdit 32 for the PL/SQL.

Will try teh sugggestion by RCURVA.

Thnx for the replies.