Re: How to pass date constant to Oracle via openquery

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You try using executing this a temp table.

In other words, 1.) query runs table and creates temp table in SQL then 2.)
Simple select * from temp table to feed into Oracle table. 3.) drop table.

I know this adds an additional step but worth a try.




"sTeve" <steve_shapiro@xxxxxxxxxxxxxxxx> wrote in message
news:d355m1p6lcv81d1tbenh8htjpr5n81br8e@xxxxxxxxxx
>I am trying to pass a query through SQL2k to Oracle as follows:
>
> SELECT * INTO dbo.Person FROM OPENQUERY(People, 'select
> Name,
> DECODE(Birth_Date,Null,To_Date('01-01-1900','DD-MM-YYYY'),Birth_Date)
> AS Birth_Date from HRISMGR.EMPLOYEE')
>
> If the birth_Date field is null, I want to substitute in the value Jan
> 1, 1900.
>
> SQL server doesn't like the single quotes around the dates, and if I
> use double single quotes (''), Oracle doesn't like it.
>
> The question is: How do I pass a date constant?
>
> This arose because simply querying on the date field causes a 'Error
> converting data type DBTYPE_DBTIMESTAMP to datetime'
>
> thanx,
>
> Steve Shapiro
> University of Oregon


.

Relevant Pages

  • Re: How to Create Local Temporary Table
    ... As Daniel pointed out, many times, it is locking that forces one to look at temp tables and Oracle does not share this problem. ... With the improvements in Oracle 9i and 10g and what Oracle has added to its SQL feature set, one can most often get by with just a single SQL statement, which is more efficient than writing data to a table and then reading it back again. ... If it places the group's minds at ease DB2 supports neither VARRAY nor local temps. ...
    (comp.databases.oracle.server)
  • Re: Informix vs Oracle vs DB2. SQL Query optimization.
    ... but I think you're confused because you don't know Oracle. ... the query chooses to limit TAB_A by the col_id. ... initially we have the collection as geo1. ... case of 270,000 rows in the collection, and 60,000 rows in the temp ...
    (comp.databases.informix)
  • Re: How to Create Local Temporary Table
    ... hold a result set from one query to be used in another query. ... With the improvements in Oracle 9i and 10g and what Oracle has added to its SQL feature set, one can most often get by with just a single SQL statement, which is more efficient than writing data to a table and then reading it back again. ... I can see a developer storing data into a temp table on the first pass and then going through the result set for a second pass. ... Amongst other things PL/SQL provides BULK COLLECT INTO and FOR ALL to speed up processing of VARRAY. ...
    (comp.databases.oracle.server)
  • Re: SELECT DISTINCT slow, how can I speed up
    ... We have staff using Access 2003 as a front end to the Oracle tables ... for the purposes of ad hoc queries. ... come back within a second of the SQL starting to run. ... only 20-50 rows come back and the query takes 28 seconds and there is ...
    (comp.databases.oracle.server)
  • Re: SQL -> Oracle
    ... > 2- extracted all my SQL queries in one single file (that was the tedious ... > 3- now whenever I need new query. ... > - Sybase and SQL server prefix their parameters with '@' ... > - Firebird and Oracle have selectable stored procedures so you use them ...
    (borland.public.delphi.non-technical)