Home » SQL & PL/SQL » SQL & PL/SQL » error in union with order by clause
error in union with order by clause [message #344657] Sun, 31 August 2008 13:23 Go to next message
NIckman
Messages: 64
Registered: May 2007
Member
hi
pl see SQL
i got this error.PL SEE SQL.
ORA-00923: FROM keyword not found where expected
on line 4
select name as empname||'
pl help.

THX,M

Quote:
select empname,empnumber,starttime,snum
from
(
select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where
ename = 'joh%'
and
start_TimeStamp9 > TO_DATE('2007-01-15 00:00:01','YYYY-MM-DD HH24:MI:SS')
and end_TimeStamp11 < TO_DATE('2007-01-18 22:59:59','YYYY-MM-DD HH24:MI:SS')
union
select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where
ename = 'joh%'
and
start_TimeStamp9 > TO_DATE('2007-01-15 00:00:01','YYYY-MM-DD HH24:MI:SS')
and end_TimeStamp11 < TO_DATE('2007-01-18 22:59:59','YYYY-MM-DD HH24:MI:SS')
union
select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where
ename = 'kris%'
and
start_TimeStamp9 > TO_DATE('2007-01-15 00:00:01','YYYY-MM-DD HH24:MI:SS')
and end_TimeStamp11 < TO_DATE('2007-01-18 22:59:59','YYYY-MM-DD HH24:MI:SS')
union
select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where
ename = 'wal%'
and
start_TimeStamp9 > TO_DATE('2007-01-15 00:00:01','YYYY-MM-DD HH24:MI:SS')
and end_TimeStamp11 < TO_DATE('2007-01-18 22:59:59','YYYY-MM-DD HH24:MI:SS')
)
order by starttime;
Re: error in union with order by clause [message #344658 is a reply to message #344657] Sun, 31 August 2008 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your post, post your version (4 decimals), use SQL*Plus and copy and paste your session.

Regards
Michel
Re: error in union with order by clause [message #344660 is a reply to message #344658] Sun, 31 August 2008 14:25 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
i do not know what you are saying.
i formatted the post.
select empname,empnumber,starttime,snum
from 
(
select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where 
ename = 'joh%'
and
start_TimeStamp9 > TO_DATE('2007-01-15 00:00:01','YYYY-MM-DD HH24:MI:SS')
and end_TimeStamp11 < TO_DATE('2007-01-18 22:59:59','YYYY-MM-DD HH24:MI:SS')

union 

select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where 
ename = 'joh%'
and
start_TimeStamp9 > TO_DATE('2007-01-15 00:00:01','YYYY-MM-DD HH24:MI:SS')
and end_TimeStamp11 < TO_DATE('2007-01-18 22:59:59','YYYY-MM-DD HH24:MI:SS')

union

select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where 
ename = 'kris%'
and
start_TimeStamp9 > TO_DATE('2007-01-15 00:00:01','YYYY-MM-DD HH24:MI:SS')
and end_TimeStamp11 < TO_DATE('2007-01-18 22:59:59','YYYY-MM-DD HH24:MI:SS')

union

select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where 
ename = 'wal%'
and
start_TimeStamp9 > TO_DATE('2007-01-15 00:00:01','YYYY-MM-DD HH24:MI:SS')
and end_TimeStamp11 < TO_DATE('2007-01-18 22:59:59','YYYY-MM-DD HH24:MI:SS')
)
order by starttime;
Re: error in union with order by clause [message #344661 is a reply to message #344660] Sun, 31 August 2008 14:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

i do not know what you are saying.



Then maybe you should work on your reading skills. Seems clear to me:
Quote:

post your version (4 decimals), use SQL*Plus and copy and paste your session



Re: error in union with order by clause [message #344662 is a reply to message #344661] Sun, 31 August 2008 15:12 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
I am not using sql plus
what is version 4 decimals
thx,N.

Re: error in union with order by clause [message #344676 is a reply to message #344662] Sun, 31 August 2008 23:00 Go to previous messageGo to next message
ttparavindh
Messages: 22
Registered: December 2006
Location: Bangalore,India
Junior Member

FYI:

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


Best regards.
Re: error in union with order by clause [message #344677 is a reply to message #344660] Sun, 31 August 2008 23:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
NIckman wrote on Sun, 31 August 2008 21:25

select empname,empnumber,starttime,snum
from 
(
select name as empname||','||
enumber as empnumber||','||
new_time(to_date(start_TimeStamp,'MM/DD/YY HH24:MI:SS'),'gmt','edt')||','||
ssn as snum
from emp
where 
ename = 'joh%'


What is this concatenation?
What you are trying to do is concatenate a comma to the column-alias. Not good.
you should concatenate the columns in your outer select, with comma's in between (I guess; you have not explained in the least bit what it is you are after)

Secondly: the equals operator (=) should not be used in combination with wildcards.
where ename = 'joh%'
will only return, well, what it says: rows with an ename exactly equal to 'joh%'
Re: error in union with order by clause [message #344710 is a reply to message #344660] Mon, 01 September 2008 01:40 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: materialized view question
Next Topic: Partition
Goto Forum:
  


Current Time: Thu Dec 08 12:46:18 CST 2016

Total time taken to generate the page: 0.22160 seconds