Need Equivalent Oracle syntax in SQl 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Equivalent Oracle syntax in SQl 2005

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2009-06-26 : 16:34:07

hi all,
i have this syntax in Oracle and looking for its equivalent in SQl 2005.
c.ID IN (SELECT A.FIELD1 FROM TMP_TABLE A)

Just looking for records with c.ID that matches A.field1 in TMP_TABLE
Can you please help ?

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-26 : 16:41:50

This looks already ok.
Do have tried it?

No, you're never too old to Yak'n'Roll if you're too young to die.

korssane
Posting Yak Master

104 Posts

Posted - 2009-06-26 : 16:48:55

Hey thanks for your quick reply..
i am doing openquery through Oracle dB and i am having error "
OLE DB provider "MSDAORA" for linked server "LINKED_ORA" returned message "ORA-00942: table or view does not exist
An error occurred while preparing the query "SELECT"

i have excuted successfully the query withour this last instruction..

should i use again the openquery for this select statement ?

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-26 : 17:05:06

Can you show the complete query please?

No, you're never too old to Yak'n'Roll if you're too young to die.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-26 : 23:21:29

u can use in or exists or inner join
as
exists (select * from tmp_table where fieldid = c.id)
or inner join tmp_table t on t.fieldid = c.id

korssane
Posting Yak Master

104 Posts

Posted - 2009-06-29 : 08:59:18

thanks ,
will try it and get back to you.

korssane
Posting Yak Master

104 Posts

Posted - 2009-06-29 : 10:37:48

here is my open query code :
Please note that all works except when i add the last statement " AND EXISTS (SELECT A.KEY1 from TMP_Table A where cx.TRD = A.KEY1)"

any suggestions will be appreciated..
thanks

SELECT * FROM OPENQUERY(LINKED_ORA,
'SELECT
cx.TRD,
fx.RE_ID,
fx.EV,
fx.NE,
fx.CR,
cx.RE_GR,
cx.RE_DA,
cx.KN_IS_ID,
cx.KN_IS_BE
FROM F_EV@Oracledb fx,
C_TR@Oracledb cx
WHERE
cx.TRD = fx.KEY1
AND lower(cx.C1) NOT LIKE ''%blablab%''
AND lower(cx.C1) NOT LIKE ''%blablab%''
AND (fx.EV LIKE ''%blablab%''
OR fx.EV LIKE ''%blablab%''
OR fx.EV LIKE ''%blablab%''
OR fx.EV = ''%blablab%''
OR fx.EV = ''%blablab%''
OR fx.EV = ''%blablab%''
)
AND lower(fx.FNMAE) like ''%blablab%''
AND EXISTS (SELECT A.KEY1 from TMP_Table A where cx.TRD = A.KEY1)

');

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-29 : 11:29:48

Well just a shot in the dark but:

Are you sure that a table with name TMP_TABLE actually *does exist* on the target server?

Try


SELECT * FROM OPENQUERY(LINKED_ORA,'SELECT * FROM TMP_Table');

To see if the table does actually exist.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

korssane
Posting Yak Master

104 Posts

Posted - 2009-06-29 : 11:46:20

Hi
the table exsists in the SQL 2005 where i am running my query. the thing is i am running an open query that talks directly to Oracle and trying to call this local table..
PLease let me know if this is not clear

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-29 : 11:59:32

well then the table doesn't exist on the remote database server which is why it is throwing the error.

You could remove the EXISTS clause entirely from the OPENQUERY and store the result set in an intermediate temp table. Then do a simple query against the temp table with the EXISTS clause. Probably not the best / most efficient way but it would probably work.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

korssane
Posting Yak Master

104 Posts

Posted - 2009-06-29 : 12:07:40

yes that why i mentionned that the TMP table is local and not remote one..
thanks i was not sure if this combination works or not..
I wil try your suggestion Charlie and get back to you..
thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources