Posted by Crazy Cat on 02/01/06 17:14

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).

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,

Crazy

[Back to original message]