| 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?
|
|
|
|
| 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
|
|
|
|
|
|
|
|
| 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.
|
|
|
|
| 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 #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...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
