Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

Receiving error when querying Oracle DB from SQL Server 2005

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
lovingatx
Asked:
lovingatx
1 Solution
 
imitchieCommented:
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
 
lovingatxAuthor Commented:
Needed to cast all fields within the distributed query
0
 
LenWrightCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now