Q
Problem solve Get help with specific problems with your technologies, process and projects.

Sybase and SQL Server programmer having trouble with Oracle SQL

I have been a Sybase and SQL Server programmer for years, and I have recently started working in Oracle. I am having...

difficulty with the following SQL. I keep getting the error "SQL command not properly ended." Do you know what I am doing wrong?

select  a.user_id, NVL(l.master_id,0) as master_id,
NVL(l.status,'') as status,
NVL(l.period_end_date,'') as period_end_date,
u.personnel_id,business,'E' as type,
lname+','+fname+' '+NVL(minitial,'') as username,
leave_balance  
from PS_COK_APPROVER a,
     PS_COK_USER u LEFT [OUTER] 
     JOIN PS_COK_LASTMASTER l on a.user_id = l.user_id
where a.user_id = u.user_id
and a.role = 0
and a.approver_id = 'shulmda'
and l.period_end_date = '11/30/2002'
and u.business+' '+u.groupname = 'US298 CoKinetic Natl Offce' 

I see a couple of possible problems:

  1. The concatenation operator for Oracle is a double pipe, ||, not a plus, +, sign.
  2. To use the join syntax you're using you must be running Oracle 9i (you didn't mention the version).
After all that, here's how I'd try changing it (for 9i):
select a.user_id,
       NVL(l.master_id,0) as master_id,
       NVL(l.status,'') as status,
       NVL(l.period_end_date,'') as period_end_date,
       u.personnel_id,business,'E' as type,
       lname||','||fname||' '||NVL(minitial,'') as username,
       leave_balance
  from PS_COK_APPROVER a,
       PS_COK_USER u LEFT OUTER JOIN PS_COK_LASTMASTER l on a.user_id =
l.user_id
 where a.user_id = u.user_id
   and a.role = 0
   and a.approver_id = 'shulmda'
   and l.period_end_date = '11/30/2002'
   and u.business||' '||u.groupname = 'US298 CoKinetic Natl Offce'  ;
Here's an 8i version:
select a.user_id,
       NVL(l.master_id,0) as master_id,
       NVL(l.status,'') as status,
       NVL(l.period_end_date,'') as period_end_date,
       u.personnel_id,business,'E' as type,
       lname||','||fname||' '||NVL(minitial,'') as username,
       leave_balance
  from PS_COK_APPROVER a,
       PS_COK_USER u,
       PS_COK_LASTMASTER l
 where a.user_id = u.user_id
   and a.user_id = l.user_id(+)
   and a.role = 0
   and a.approver_id = 'shulmda'
   and l.period_end_date = '11/30/2002'
   and u.business||' '||u.groupname = 'US298 CoKinetic Natl Offce'  ;
If you continue to have trouble, feel free to write back with more details about exactly what you expect the statement to do and we can attempt to assist further.

For More Information


This was last published in March 2003

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close