SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select from Openquery from Oracle, missing quot...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

entropus
Starting Member

5 Posts

Posted - 12/06/2012 :  12:55:01  Show Profile  Reply with Quote
I have a problem with running a select from openquery.
I will be extremely grateful for help! I just hit the wall, can't find a way to solve this issue.

Please do not take into account field names/data types, as I had to present only a part of the whole procedure. It's quite long.
I believe the problem lies in quotation marks, etc...

The procedure compiles all right.
Each time I execute the procedure, an error occurs:

Quote:
Msg 105, Level 15, State 1, Line 53
Unclosed quotation mark after the character string 'SELECT TO_NUMBER(XYZ_1) XYZ_1,
XYZ_2,

cast (''''0'''' as number(5)) as B1,
cast(''''1753-01-01'''' as date) NULL_DATE



I am lost - where the heck should I put those missing quotation mark?


Code:


Code: [Select all] [Show/ hide]
CREATE TABLE #tmpXYZ Header (
[XYZ_1] [int] PRIMARY KEY,
[XYZ_2] [varchar](20),
[XYZ_3] [varchar](20),
-- more fields
[XYZ_N] [varchar](50)
)

declare @sqlInv nvarchar(3000)
set @sqlInv =

'insert into #tmpXYZ Header (
[XYZ_1],
[XYZ_2],
[XYZ_3],
-- more fields
[XYZ_N]

)

select
* FROM OPENQUERY(XYZ_ORACLE,
''SELECT TO_NUMBER(XYZ_1) XYZ_1,
XYZ_2,

cast (''''0'''' as number(5)) as B1,
cast(''''1753-01-01'''' as date) NULL_DATE,
-- more fields
cast ('''' '''' as varchar(20)) as A19

from XYZ.V_HEADER
where

(DATE >= ''''TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''')'''' AND
DATE <= ''''TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''')'''' AND
QWE = ''''0'''' AND
ABC = ''''13'''' ) '' )
'

exec sp_executesql @sqlInv

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  13:06:40  Show Profile  Reply with Quote
Print out the @sqlInv string and see if that makes sense. From visual observation it seems to me like you need to remove the four single quotes just before TO_DATE and the matching four single quotes just before the AND keyword.
....      
from XYZ.V_HEADER 
where

(DATE >= TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''') AND 
DATE <= TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''') AND 
QWE = ''''0'''' AND
ABC = ''''13'''' ) '' )
' 
Also, I assume @startDate and @endDate are character types - otherwise you will need to cast/convert them to character types (at least in SQL Server - don't know if Oracle will automatically convert).

Edited by - sunitabeck on 12/06/2012 13:08:03
Go to Top of Page

entropus
Starting Member

5 Posts

Posted - 12/06/2012 :  13:11:42  Show Profile  Reply with Quote
Hey Sunitabeck, thanks for the input! Sadly, does not work, same error :/

Also, I remove the 'where... ' part of the procedure. Same error.

Edited by - entropus on 12/06/2012 13:16:18
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  13:15:59  Show Profile  Reply with Quote
You don't have those comments in the actual query, do you? If you do, remove them
[XYZ_3] [varchar](20),
-- more fields	
[XYZ_N] [varchar](50)
Go to Top of Page

entropus
Starting Member

5 Posts

Posted - 12/06/2012 :  13:16:53  Show Profile  Reply with Quote
No, no comments.

(Also, I removed the 'where... ' part of the procedure. Same error.)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  13:29:59  Show Profile  Reply with Quote
Since you are not able to post the whole query (or even if you are able to), reduce the select list to just the 3 columns you posted and see if you can make that work. If not, replace the "exec sp_executesql @sqlInv" with "print @sqlInv" and look at the query string (or post it).
Go to Top of Page

entropus
Starting Member

5 Posts

Posted - 12/06/2012 :  13:50:28  Show Profile  Reply with Quote
Hey Sunitabeck, thanks a lot for help!

I did that, and got (approximately) this result:

select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')
select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')



insert into #tmpXYZ Header (
[XYZ_1],
[XYZ_2],
[XYZ_3],

[XYZ_N]

)

select
* FROM OPENQUERY(XYZ_ORACLE,
''SELECT TO_NUMBER(XYZ_1) XYZ_1,
XYZ_2,

cast (''''0'''' as number(5)) as B1,
cast(''''1753-01-01'''' as date) NULL_DATE,

cast ('''' '''' as varchar(20)) as A19

from XYZ.V_HEADER
where

(DATE >= TO_DATE(''20121107'',

/result ends with the date

Edited by - entropus on 12/06/2012 13:54:47
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  15:01:06  Show Profile  Reply with Quote
Are you still getting the same error?
Go to Top of Page

entropus
Starting Member

5 Posts

Posted - 12/06/2012 :  15:29:03  Show Profile  Reply with Quote
Well there was no error while I used "print @sqlInv", but while using "exec sp_executesql @sqlInv" i get the same error.

I changed the procedure so that the exec sp_executesql @sqlInv is not used, and this:

'insert into #tmpXYZ Header (
[XYZ_1],
[XYZ_2],
[XYZ_3],
-- more fields
[XYZ_N]

)

select
* FROM OPENQUERY(XYZ_ORACLE,
''SELECT TO_NUMBER(XYZ_1) XYZ_1,
XYZ_2,

cast (''''0'''' as number(5)) as B1,
cast(''''1753-01-01'''' as date) NULL_DATE,
-- more fields
cast ('''' '''' as varchar(20)) as A19

from XYZ.V_HEADER
where

(DATE >= ''''TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''')'''' AND
DATE <= ''''TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''')'''' AND
QWE = ''''0'''' AND
ABC = ''''13'''' ) '' )
'

is executed directly. Sadly, I get an error: "ORA-01858: a non-numeric character was found where a numeric was expected".

However, I checked all the fields and they seem to be OK regarding data types.

Also, another procedure almost identical to this one is run, and works fine.
No need to "- otherwise you will need to cast/convert them to character types " I believe.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000