Home » SQL & PL/SQL » SQL & PL/SQL » dynamic column alias using sysdate
dynamic column alias using sysdate [message #24748] Tue, 04 March 2003 16:35 Go to next message
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?
Re: dynamic column alias using sysdate [message #24749 is a reply to message #24748] Tue, 04 March 2003 17:22 Go to previous messageGo to next message
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
Re: dynamic column alias using sysdate [message #24760 is a reply to message #24748] Wed, 05 March 2003 09:17 Go to previous messageGo to next message
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.
Re: dynamic column alias using sysdate [message #24763 is a reply to message #24760] Wed, 05 March 2003 11:27 Go to previous messageGo to next message
Scott SWANSON
Messages: 2
Registered: March 2003
Junior Member
I planned to use it in a view.
Re: dynamic column alias using sysdate [message #24772 is a reply to message #24763] Thu, 06 March 2003 02:28 Go to previous messageGo to next message
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.
Re: dynamic column alias using sysdate [message #291234 is a reply to message #24748] Thu, 03 January 2008 08:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
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
Re: dynamic column alias using sysdate [message #291275 is a reply to message #291246] Thu, 03 January 2008 10:38 Go to previous messageGo to next message
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...
Re: dynamic column alias using sysdate [message #291283 is a reply to message #291275] Thu, 03 January 2008 10:59 Go to previous message
Michel Cadot
Messages: 64111
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
Previous Topic: Pl/Sql Procedure
Next Topic: Does anyone have a query for the Journal Entry Reserve Ledger Report?
Goto Forum:
  


Current Time: Mon Dec 05 21:28:46 CST 2016

Total time taken to generate the page: 0.25608 seconds