?
Solved

MS SQL select statement to Oracle, using alias

Posted on 2011-10-12
4
Medium Priority
?
546 Views
Last Modified: 2012-05-12
Hi Experts

Im using SQL server 2005 and have a linked server to an Oracle db, I am converting a query to use open query so get faster results.
the query I have is

SELECT RIMS_INSTRUMENT.INST_NUM,
                     RIMS_INSTRUMENT.[NAME],
                     RIMS_INSTRUMENT.INST_TYPE_CODE,
                     RIMS_FUTR.LAST_EXERCISE_DATE
            FROM RIMS_TRS..RIMS.INSTRUMENT AS RIMS_INSTRUMENT
            LEFT JOIN RIMS_TRS..RIMS.FUTR_OPTN_SERIES AS RIMS_FUTR ON RIMS_INSTRUMENT.INST_NUM = RIMS_FUTR.INST_NUM
            WHERE RIMS_INSTRUMENT.INST_TYPE_CODE='EQTY' Or (RIMS_INSTRUMENT.INST_TYPE_CODE='FUTR';

when I run the query the above query using Oracle I get the error message  returned message "ORA-00933: SQL command not properly ended - Is it becuase Alias work in a different way in Orace?
0
Comment
Question by:MrDavidThorn
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 36954533
missed ) at end

try this
SELECT RIMS_INSTRUMENT.INST_NUM,
                     RIMS_INSTRUMENT.[NAME],
                     RIMS_INSTRUMENT.INST_TYPE_CODE,
                     RIMS_FUTR.LAST_EXERCISE_DATE
            FROM RIMS_TRS..RIMS.INSTRUMENT AS RIMS_INSTRUMENT
            LEFT JOIN RIMS_TRS..RIMS.FUTR_OPTN_SERIES AS RIMS_FUTR ON RIMS_INSTRUMENT.INST_NUM = RIMS_FUTR.INST_NUM
            WHERE RIMS_INSTRUMENT.INST_TYPE_CODE='EQTY' Or (RIMS_INSTRUMENT.INST_TYPE_CODE='FUTR');

OR

SELECT RIMS_INSTRUMENT.INST_NUM,
                     RIMS_INSTRUMENT.[NAME],
                     RIMS_INSTRUMENT.INST_TYPE_CODE,
                     RIMS_FUTR.LAST_EXERCISE_DATE
            FROM RIMS_TRS..RIMS.INSTRUMENT AS RIMS_INSTRUMENT
            LEFT JOIN RIMS_TRS..RIMS.FUTR_OPTN_SERIES AS RIMS_FUTR ON RIMS_INSTRUMENT.INST_NUM = RIMS_FUTR.INST_NUM
            WHERE RIMS_INSTRUMENT.INST_TYPE_CODE='EQTY' Or RIMS_INSTRUMENT.INST_TYPE_CODE='FUTR';

0
 
LVL 3

Expert Comment

by:BartVx
ID: 36954535
There is an open bracket at the beginning of the last line that is not closed.
0
 

Author Comment

by:MrDavidThorn
ID: 36954541
sorry this is the actual query I am running

select * FROM OPENQUERY
(RIMS_TRS,'SELECT TEST2.INST_NUM FROM RIMS.INSTRUMENT AS TEST LEFT JOIN RIMS.FUTR_OPTN_SERIES AS TEST2 ON TEST.INST_NUM = TEST2.INST_NUM')

0
 

Author Comment

by:MrDavidThorn
ID: 36954550
think I found the problem Oracle doesnt like me using the AS statement for somereason
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question