Solved

Receiving error when querying Oracle DB from SQL Server 2005

Posted on 2007-12-06
3
721 Views
Last Modified: 2013-12-07
When I run the query below, I receive an error stating that Invalid Data for type numeric.  The query is being executed from SQL 2005 against a linked server to an Oracle DB.  If I select just the first record, it returns without issue.  However, when I run it against the entire dataset, I get the error.  From what I've read, this can occur if an integer is stored in a numeric field.  How can I get around this?  I'm not familiar with PLSQL.


select top 10 
cast([LE] as varchar(100))
,cast([DIV] as varchar(100))
,cast([LOCPROG] as varchar(100))
,cast([CC] as varchar(100))
,cast([ACCT] as varchar(100))
,cast([PROJ] as varchar(100))
,cast([PER_YEAR] as varchar(100))
,cast([PER_NUM] as varchar(100))
,cast([ACCT_DESCRIPTION] as varchar(100))
,cast([CSIGN] as varchar(100))
,cast([PERACT] as varchar(100))
,cast([PERIOD_STATUS] as varchar(100)) 
From OPENQUERY(gsiprod,
'select 
g.segment1 LE,
g.segment2 DIV,
g.segment3 LOCprog,
g.segment4 CC,
g.segment5 ACCT,
g.segment6 PROJ,
b.period_year Per_Year,
b.period_num Per_Num,
acct.description acct_description,
decode(substr(acct.compiled_value_attributes,5,1),''R'',-1,1) csign,
SUM(NVL(b.period_net_dr,0) - nvl(b.period_net_cr,0)) PerAct,
decode(s.closing_status,''C'',''Closed'',''P'',''Closed'',''Open'') period_status
from
gl.gl_period_statuses s,
(select * from apps.fnd_flex_values_vl where flex_value_set_id = 1002591) acct,
gl.gl_code_combinations g,
gl.gl_balances b,
report.calendar_445 cal
where 1=1
and s.set_of_books_id = b.set_of_books_id
and s.application_id = 101
and s.period_name = b.period_name
and acct.flex_value (+) = g.segment5
and g.code_combination_id = b.code_combination_id
and b.actual_flag = ''A'' 
and nvl(b.translated_flag,''USD'') = ''USD'' 
and (NVL(b.period_net_dr,0) - nvl(b.period_net_cr,0)) != 0
and g.segment5 between ''30000'' AND ''99999'' --Income Statement Accounts
and b.period_year in (cal.fiscalyear, cal.fiscalyear - 1, cal.fiscalyear - 2)
and cal.actual_date = trunc(sysdate) - 7
GROUP BY
g.segment1 ,
g.segment2 ,
g.segment3 ,
g.segment4 ,
g.segment5 ,
g.segment6 ,
b.period_year ,
b.period_num ,
acct.description,
decode(substr(acct.compiled_value_attributes,5,1),''R'',-1,1) ,
decode(s.closing_status,''C'',''Closed'',''P'',''Closed'',''Open'') ')

Open in new window

0
Comment
Question by:lovingatx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20422405
Check that the these columns are not NUMERIC, they are being nvl-ed or compared to CHAR types

and b.actual_flag = ''A''
and nvl(b.translated_flag,''USD'') = ''USD''
and g.segment5 between ''30000'' AND ''99999'' --Income Statement Accounts
0
 

Author Closing Comment

by:lovingatx
ID: 31413211
Needed to cast all fields within the distributed query
0
 
LVL 2

Expert Comment

by:LenWright
ID: 20855789
This problem seems to be caused by the 64 bit oracle driver. Pretty sure it's okay on 32 bit, but I'm struggling with this in a 64 bit world. Also, this only seems to occur with numeric values that end with a zero. I'm gonna upgrade the driver from Version 10.2 to 11.1 next week to see if the problem has been solved.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question