has an empty result set I get the following error -- Invalid Data for
'Numeric' when EXEC returns empty row. However if I call the query
without using REPLACE (which I'm forced to do, because openquery does
not allow variables), I get just an empty result set. Whenever the
underlying query returns a non-empty result set, the code works without
error (regardless of wether there are nulls in the numeric column).
set @.switch ='5707550'
set @.start_date = '01-JAN-2006'
set @.end_date = '27-JAN-2006'
set @.month = 1
set @.year = 2006
set @.sql_str='
SELECT * FROM
(select MSC_KEY,
to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",
ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (
NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)
), 5)
as "PER_CPU_UTIL"
FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view v
WHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +
@.switch + ')
and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @.start_date + '
00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') and
to_date(''' + @.end_date + ' 23:59:00'', ''DD-MON-YYYY
HH24:MI:SS'')
group by MSC_KEY, trunc(tstamp)
)
WHERE rownum < 10000'
SET @.sql_str = N'select * from OPENQUERY(VISION, ''' +
REPLACE(@.sql_str, '''', ''') + ''')'
EXEC (@.sql_str);
Is there anyway to prevent this error?
Thanks,
CrazyCrazy Cat wrote:
> Hi, whenever the underlying query being called by EXEC in the following
> has an empty result set I get the following error -- Invalid Data for
> 'Numeric' when EXEC returns empty row. However if I call the query
> without using REPLACE (which I'm forced to do, because openquery does
> not allow variables), I get just an empty result set. Whenever the
> underlying query returns a non-empty result set, the code works without
> error (regardless of wether there are nulls in the numeric column).
code deleted to save space ...
> Is there anyway to prevent this error?
> Thanks,
> Crazy
Found the problem -- apparently one of the keys was of type numeric and
I wasn't converting it to varchar before selecting it -- funny it
worked when the result set was non-empty.
Thanks,
Crazy
