OraFAQ Forum: SQL & PL/SQL » dynamic column alias using sysdate

Home » SQL & PL/SQL » SQL & PL/SQL » dynamic column alias using sysdate

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dynamic column alias using sysdate [message #24748]

Tue, 04 March 2003 16:35

Scott SWANSON
Messages: 2
Registered: March 2003

Junior Member

I am trying to utilize sysdate to dynamically create a column alias ie:

select sysdate to_char(sysdate) from dual

this however returns
ORA-00923: FROM keyword not found where expected.
Anybody have any thoughts on how or if I can accomplish this without writing a procedure?

Report message to a moderator

Re: dynamic column alias using sysdate [message #24749 is a reply to message #24748]

Tue, 04 March 2003 17:22

Raj
Messages: 411
Registered: November 1998

Senior Member

Which is the alias name in this statement ?
select sysdate to_char(sysdate) from dual

aias name should follow the actual column name. like this
select to_char(sysdate) sysdate from dual

however, you cannot use keywords (sysdate) as alias name.try this way
select to_char(sysdate) "sysdate" from dual

Report message to a moderator

Re: dynamic column alias using sysdate [message #24760 is a reply to message #24748]

Wed, 05 March 2003 09:17

JOHN
Messages: 182
Registered: April 1998

Senior Member

Where do you intend to use the SQL statement ? The solution will vary from SQL Plus to PL/SQL.

Report message to a moderator

Re: dynamic column alias using sysdate [message #24763 is a reply to message #24760]

Wed, 05 March 2003 11:27

Scott SWANSON
Messages: 2
Registered: March 2003

Junior Member

I planned to use it in a view.

Report message to a moderator

Re: dynamic column alias using sysdate [message #24772 is a reply to message #24763]

Thu, 06 March 2003 02:28

JOHN
Messages: 182
Registered: April 1998

Senior Member

SQL Plus Solution :

SET FEEDBACK OFF;
SET HEAD OFF;
SET TERM OFF;

SPOOL blubber.sql
SELECT 'CREATE OR REPLACE VIEW vwDATE AS SELECT SYSDATE ' || TO_CHAR(SYSDATE,'MONdd') || ' FROM dual;' FROM dual;
SPOOL OFF;

SET TERM ON;
SET HEAD ON;
@blubber;

PL/SQL Block Solution :

DECLARE

vSQL VARCHAR2(100);

BEGIN

vSQL := 'CREATE OR REPLACE VIEW vwDATE AS SELECT SYSDATE ' || TO_CHAR(SYSDATE,'MONdd') || ' FROM dual';

EXECUTE IMMEDIATE vSQL;

END;
/

Hope this helps.

John.

Report message to a moderator

Re: dynamic column alias using sysdate [message #291234 is a reply to message #24748]

Thu, 03 January 2008 08:14

CraigW
Messages: 2
Registered: January 2008
Location: Texas

Junior Member

Sorry to retrieve this topic from the dustbin, but the answer given in this is for PL/SQL, and I wanted to know if there is a straight SQL answer for this situation.

To explain my situation, I have transaction information, and I am summing it up over six hour periods (1a - 7a - 1p - 7p - 1a ...) by step and then using decode statements to turn these into columns. I would like the column name to be the ending time for the sum. Currently I am stuck having a column returned with this information, and this is clumsy and hopefully, unnecessary.

Here's some partial code to show what I am doing:

SELECT Step, SYSDATE P1DATE, Period1, SYSDATE-MOD((SYSDATE-TRUNC(SYSDATE)+5/24),6/24) P2DATE, Period2
FROM (
SELECT Step, SUM(DECODE(Period,'1',Qty,0)) Period1, SUM(DECODE(Period,'2',Qty,0)) Period2
FROM (
SELECT DECODE(SIGN(TO_DATE(SUBSTR(Transn_Time,1,15),'YYYYMMDD HH24MISS')-(SYSDATE-MOD((SYSDATE-TRUNC(SYSDATE)+5/24),6/24))), 1, '1',
DECODE(SIGN(TO_DATE(SUBSTR(Transn_Time,1,15),'YYYYMMDD HH24MISS')-(SYSDATE-MOD((SYSDATE-TRUNC(SYSDATE)+5/24),6/24)-6/24)), 1, '2'
....
,NULL)))))) Period
....

Thank, Craig
P.S. For further clarification, at 8:12a, Period1 would be 7a -8:12a, Period2 would be 1a-7a, ...
P.P.S. I am on: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi

[Updated on: Thu, 03 January 2008 08:22]

Report message to a moderator

Re: dynamic column alias using sysdate [message #291246 is a reply to message #291234]

Thu, 03 January 2008 09:07

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Something like:

SQL> col head new_value head
SQL> select 'field-'||to_char(sysdate,'HH24') head from dual;
HEAD
--------
field-16

1 row selected.

SQL> col dummy format a10 heading &head
SQL> select dummy from dual;
field-16
----------
X

1 row selected.

Of course in reality you don't print first select result, I just put it here to show how it works.

Regards
Michel

Report message to a moderator

Re: dynamic column alias using sysdate [message #291275 is a reply to message #291246]

Thu, 03 January 2008 10:38

CraigW
Messages: 2
Registered: January 2008
Location: Texas

Junior Member

Michel, Thanks for the reply.

So that's how you would do it via multiple lines in SQL Plus.
What I need, which might not be possible, is to do it in the SQL statement.
I am pulling from an Oracle database for reporting purposes. I have already created the double embedded SQL, now I am looking to add the end date as the column name to be returned with the move totals to the user.
Does this make my request clearer?

Thanks,
Craig...

Report message to a moderator

Re: dynamic column alias using sysdate [message #291283 is a reply to message #291275]

Thu, 03 January 2008 10:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

strictly speaking, it is not possible to do it inside the query as the query has to know the column alias at compile time so before the execution and so before getting data.
But you can fake it removing the SQL*Plus heading and adding a line with yours calculated from the values.

Have a look at the following thread on AskTom where I answered of more complex question:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:595725000346503967#596541900346821269

Regards
Michel

Report message to a moderator

Previous Topic:	Pl/Sql Procedure
Next Topic:	Does anyone have a query for the Journal Entry Reserve Ledger Report?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 10:06:24 CDT 2024