My Float had become Decimal (38, 5)

[hilaDG]

Hi All,

I have a query that refers to a value in a table that is stored as a float like this:

VIEW [dbo].[v_cash_sub] AS

SELECT po_account,
 SUM(po_quantity  ) as cash
 FROM dbo.position
  INNER JOIN dbo.security
   ON se_id = po_security
 WHERE se_cash <> 0
 GROUP BY po_account

And the SSMA tells me that this view had po_account (INT), po_quantity (float).

I enquire in a different view:

CREATE VIEW [dbo].[v_cash_test] AS
SELECT
 po_account as vc_account,
 COALESCE(cash, 0) as vc_cash_current
 
 FROM  dbo.v_cash_sub

(I simplified the problem for publishing purposes, of course there are more joins in the real life view, and because they are left outer I needed the COALESCE)
and it tells me that I have:
po_account (INT) -correct
vc_cash_current (DECIMAL (38, 5))
Has anyone got any idea why?
(Yes, I could change the float to decimal by casting, I just wanted to understand what's going on)
PS replacing the 'COALESCE' with ISNULL doesn't make much of a difference

[code green]

Not sure but does this make a difference?

COALESCE(cash, 0.00) as vc_cash_current

[hilaDG]

No, it didn't

(and it played with isnull(), just to be sure that it doesn't matter...)
The reason I need this in the first place is that Access sees a Deciaml (38, 5) and reads it as text.

[code green]

I don't understand your reply but there is always CAST()

[hilaDG]

Yes, I guess I'll have to settle with a CAST

But if you know of anything in the database definitions that could cause this change, please let me know. Thanks for your help.

[ck9663]

Check the structure of the tables on the join. The COALESCE will grab the data type with the highest precedence.

Good Luck!!!

~~ CK

[hilaDG]

Thank you all for your help

And nothing worked better then dear old cast.
I would still like to understand better the decimal date type multiplying rules. If there's any good article you'd like to share please post below. thank you all.