refering to a sql 2005 db while using oracle

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

refering to a sql 2005 db while using oracle

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2009-06-29 : 11:19:35

Hi All,
i am using an Open query to pull some fields form an Oracle DB.
My problem is i have to compare 1 field pulled from oracle Db using the Openquery statement with an exsisting table in SQL server 2005.
Any help on how to referthis..
Thanks
here is my code where "TMP_Table is the "sql-2005 table".

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 11:25:17

seems like this


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%'''
)t
AND EXISTS (SELECT A.KEY1 from TMP_Table A where t.TRD = A.KEY1)

korssane
Posting Yak Master

104 Posts

Posted - 2009-06-29 : 11:43:51

Thanks visakh16,
but did not catch what has changed..? can you point me to the error please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 11:50:17

have made change in blue. see earlier suggestion

korssane
Posting Yak Master

104 Posts

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

sorry ,
getting returned message "ORA-00942: table or view does not exist..
i confirmed that "TMP_Table" exsists in my sql 2005 serverr as a table..
the thing i am not sure of is ..is this right to call a local table "TMP_Table" while running an open query to oracle dvb ?

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources