| Author |
 Topic  |
|
|
entropus
Starting Member
5 Posts |
 Posted - 12/06/2012 : 12:55:01
|
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
|
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 |
 |
|
|
entropus
Starting Member
5 Posts |
Posted - 12/06/2012 : 13:11:42
|
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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 12/06/2012 : 13:15:59
|
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) |
|
|
|
entropus
Starting Member
5 Posts |
Posted - 12/06/2012 : 13:16:53
|
No, no comments.
(Also, I removed the 'where... ' part of the procedure. Same error.) |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 12/06/2012 : 13:29:59
|
| 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). |
|
|
|
entropus
Starting Member
5 Posts |
Posted - 12/06/2012 : 13:50:28
|
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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 12/06/2012 : 15:01:06
|
| Are you still getting the same error? |
|
|
|
entropus
Starting Member
5 Posts |
Posted - 12/06/2012 : 15:29:03
|
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. |
|
|
| |
Topic |
|
|
|
Select from Openquery from Oracle, missing quot...