CODEWITHSUNDEEP
sqlopenedgeprogress-db
Katherine Pacheco
Katherine Pacheco

Reputation: 369

Open Progress if NULL equivalent?

I have the below select statement but i get error

Msg 7354, Level 16, State 1, Line 393 The OLE DB provider "STREAM" for linked server "(null)" supplied invalid metadata for column "CORP_POSTINGS". The precision exceeded the allowable maximum.

select 
OfficeID, PostDate,
Case when a.OFFICEID in (''17'',''42'',''56'',''50'',''66'',''46'') then 
a.PROVIDERNAME else ''All Providers'' end as Provider, 
CASE WHEN a.JournalName contains ''emwell'' or a.JournalName in (''Insurance 
Refunds'') Then sum(cast(ifnull(a.Amount,0.00) as decimal (12,2))) else 0 
end as ''CORP_POSTINGS''

I tried ifNULL to the amount and it didnt work. I even tried ifnull and cast to decimal. Any help is greatly appreciated...

This is being done in OPEN PROGRESS ODBC CONNECTION

Upvotes: 0

Views: 2472

Answers (1)

Bret
Bret

Reputation: 224

It looks like you have your SUM, CAST, and IFNULL out of order.

First, you'll want to SUM your amounts. Next, you'll need to CAST it to your target datatype (Progress seems to send the incorrect type back). Finally, if you found zero records, you'll get back NULL, so you'll want to IFNULL that to zero.

If your a.Amount is non-Null, it should be IFNULL( CAST( SUM(a.Amount) AS DECIMAL(12,2) ), 0.00).

If your a.Amount CAN be Null, it should be IFNULL( CAST( SUM( IFNULL(a.Amount, 0.00) ) AS DECIMAL(12,2) ), 0.00).

Upvotes: 1

Related Questions