Parameters in Oracle Open Query 1

[MikeAuz1979]

Hi,

Using SQL Server 2005 I'm trying to run an openquery statement that pulls data from an Oracle DB but with some date parameters and I'm getting a syntax error {Code Below}.

I would just use an oracle linked server using a T-sql statement but I'm getting errors with datetimes converting into SQL speak. (There's some future dates in Oracle like 03/03/3333 that is freaking SQL out.)

Anyone have any ideas?

Thanks for any help
Mike

DECLARE @SDATE DATETIME
DECLARE @EDATE DATETIME
DECLARE @SQLx VARCHAR(200)

Set @SDATE = '1 apr 2008'
Set @EDATE = '9 apr 2008'

Truncate Table BI_Datamart.tblCPMTemplatesRawExtract

Set @SQLx = 
'select *
from OPENQUERY ( DWH_NAT_PR ,
'INSERT INTO tblCPMTemplatesRawExtract (item_desc,prefix,rng_code,ref_no,state_code,qty_sold)
SELECT ci.item_desc, stcu.prefix, rscu.rng_code,ci.ref_no,
stvtu.state_code,SUM (wsos.qty_sold)

FROM WW.CORP_ITEM ci,
WW.RNG_SCU rscu,
WW.ST_CONSUMER_UNIT stcu,
WW.ST_VNDR_TRD_UNIT stvtu,
WW.WKY_ST_OP_SLS wsos

WHERE ci.ref_no = rscu.ref_no
AND ci.ref_no = stcu.ref_no
AND rscu.state_code = wsos.state_code
AND rscu.div_no = wsos.div_no
AND rscu.ref_no = wsos.ref_no
AND stvtu.state_code = rscu.state_code
AND stvtu.ref_no = rscu.ref_no
AND stvtu.state_code = stcu.state_code
AND wsos.qty_sold > 10
AND wsos.week_end_date >= '''' + @SDATE + ''''
AND wsos.week_end_date <= '''' + @EDATE + ''''
AND stvtu.delist_date > sysdate
GROUP BY ci.item_desc,
         stcu.prefix,
         rscu.rng_code,
         ci.ref_no,
         stvtu.state_code
ORDER BY ci.item_desc ASC')'

 

[ESquared]

First, I didn't think you could do an insert statement with openquery. For one thing, insert doesn't usually return any rows. SQL 2005 has special syntax to do this, but I don't think Oracle does. And I just tested doing an insert in an openquery while connected to a SQL Server remote server, and it doesn't work. The correct syntax is:

INSERT OPENQUERY(Server, 'SELECT Column1, Column2 FROM TableA') SELECT Expression1, Expression2 FROM TableB
--or
... FROM OPENQUERY( ... )
--or
INSERT OPENQUERY(Server, 'SELECT * FROM TableA') (Column1, Column2) SELECT Expression1, Expression2 FROM <DataSource>

It might help to think of openquery as opening a data source rather than a static rowset. It's just like using a table name: you can update it, insert to it, join with it, and so on. The fact that the query itself can be multipart and have a where clause doesn't change any of that. See how above I can put the column list of the insert in the openquery itself or in the outer query, either one? If you run a trace on the remote server you'll see that the "SELECT *" part is never actually run, though it is used in SQL Server as part of sp_cursoropen and sp_cursor, but the final issued statement is in fact a plain INSERT similar to the one you thought you were submitting in the first place, only it does one INSERT per row using literal values.

Second, Oracle uses different date-literal syntax. I can't remember at the time exactly what that is but it's something like '17-Mar-2008' with a todate conversion function of some kind. I see you're using '1 apr 2008' but I wonder if your query is really correct. So what you really need is to take your inner query to the Oracle forum and get it working right (with no variable substitution, use date literals). THEN come back and try to build a good openquery statement with it. This is not the right forum for fixing the inner query. Get that right, then come back for more help using it if you need.

Third, SQL Server has no problem with '03/03/3333' in a datetime, it will go up to 12/31/9999 no problem. What it DOES have a problem with is dates earlier than Jan 1, 1753. That's the typical source of datetime problems moving data from Oracle to SQL Server. You can use a CASE statement to alter any 1601 dates (or whatever year it is that's the minimum for datetime in Oracle) to NULL or an appropriate other indicator value.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[MikeAuz1979]

Thanks E,

I took what you said, did some looking up and some guessing an came up with the following which works well for me.

declare @SDate     datetime
set     @SDate =   '2008-04-01T00:00:00'
declare @EDate     datetime
set     @EDate =   '2008-04-09T00:00:00'
declare @SQLString varchar(Max)

set @SQLString = 'SELECT ci.item_desc, stcu.prefix, rscu.rng_code,ci.ref_no,
stvtu.state_code,SUM (wsos.qty_sold)

FROM WW.COR_ITEM ci,
WW.RN_SCU rscu,
WW.ST_CONSUME_UNIT stcu,
WW.ST_VNDR_TR_UNIT stvtu,
WW.WKY_ST_O_SLS wsos

WHERE ci.ref_no = rscu.ref_no
AND ci.ref_no = stcu.ref_no
AND rscu.state_code = wsos.state_code
AND rscu.div_no = wsos.div_no
AND rscu.ref_no = wsos.ref_no
AND stvtu.state_code = rscu.state_code
AND stvtu.ref_no = rscu.ref_no
AND stvtu.state_code = stcu.state_code
AND wsos.qty_sold > 10
AND wsos.week_end_date >= to_date(''' + convert(varchar(30),@SDate,120) + ''', ''YYYY-MM-DDHH24:MI:SS'')
AND wsos.week_end_date <= to_date(''' + convert(varchar(30),@EDate,120) + ''', ''YYYY-MM-DDHH24:MI:SS'')
AND stvtu.delist_date > sysdate
GROUP BY ci.item_desc,
         stcu.prefix,
         rscu.rng_code,
         ci.ref_no,
         stvtu.state_code
ORDER BY ci.item_desc ASC'

set @SQLString = 'Select * 
from OPENQUERY ( DWH_NAT_PR , ''' + REPLACE(@SQLString, '''', '''''') + ''')'

print   @SQLString
EXEC    (@SQLString)

 

[ShankarJ]

Hi Mike,

FWIW, I use dates as {d'yyyy-mm-dd'} without any problems on Oracle databases.

Regards

 

[ESquared]

ODBC functions/expressions work too.