Invalid Data for 'Numeric' when EXEC returns empty row

[Crazy Cat]

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(T STAMP), ''yyyy-Mon-dd'') as "Timestamp" ,
ROUND( NVL(SUM(SUNRGMM SCBHCP1.XASUTIL ),0) / DECODE (
NVL(SUM(SUNRGMM SCBHCP1.XASNXFR ),0),0,NULL,NVL (SUM(SUNRGMMSCB HCP1.XASNXFR),0 )
), 5)
as "PER_CPU_UT IL"
FROM NOR_GSM_COMPOSI TE_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(VISIO N, ''' +
REPLACE(@sql_st r, '''', '''''') + ''')'

EXEC (@sql_str);
Is there anyway to prevent this error?

Thanks,

Crazy

[Crazy Cat]

Crazy 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