OPENQUERY where statement

• Matt Nickerson

  SSC Enthusiast

  Points: 185

  More actions

  August 23, 2005 at 1:45 pm

  #90788

  Hello all.

  I am having trouble with an OPENQUERY statement and hope someone has an answer. My problem lies in the WHERE statement. The passthrough is coming from a MS SQL Server and the Openquery is from a 9i data source. The line I am having a problem with is in red.  I appreciate any help I can get!! Thanks.

  Select *

  Insert Into WorkOrders.dbo.WOS

  From OPENQUERY (ORALINK,

  'SELECT

  PRIN_WOS,

  WO_TYP_WOC,

  SERV_CDE_BIL_WOS,

  SERV_CDE_DTE_WOS,

  SERV_CDE_WOS,

  SERV_DSC_WOS,

  SERV_NET_CHG_WOS,

  SERV_EXT_BEF_WOS,

  SERV_EXT_AFT_WOS,

  WO_KEY_WOS

  FROM

  WOS_SERV_CODE,WOC_BASE_COMP

  WHERE

  COMPL_DTE_WOC > Trunc(sysdate)-1 <----How do I pass this criterea or any date criterea in an OPENQUERY WHERE statement?

  WO_KEY_WOS = WO_KEY_WOC

  AND TIME_FLG_WOC <> ''N''

  AND SERV_NET_CHG_WOS <> ''0''')

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  August 23, 2005 at 4:51 pm

  #584427

  format the date using the convert command...

  EG

  declare @x datetime

  set @x = getDate()

  convert(varchar(50), @x, 120) 

  120 = ODBC,  121 = ODBC + milliseconds,  126 = ISO (used in XML)

  The date would need to be enclosed in quotes so it is interpreted as a string which I imagine Oracle would convert to a date.

  You'll also need to use dynamic sql to execute the openQuery statement as it only takes constant strings as an argument - no variables or string concatenation allowed

  One other thing to bear in mind - I had trouble once talking to Oracle from SQL via linked servers - there are differences using the Microsoft provider vs the Oracle provider... Do a search on here for the differences - I cannot remember them at the moment

  Cheers,  Ian

• escobal_lor

  SSC Enthusiast

  Points: 161

  More actions

  May 5, 2011 at 1:06 am

  #1320228

  Hi,

  How it will be translated to Grashopper t-sql statement?

  Thanks.