SQL View keep data type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL View keep data type

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-03-24 : 12:58:41

Creating a sql view from a table with a column that has a data type of decimal 16, 6.

If I use Query analyzer and select p.exp_unit_cost from poordlin p

it returns.

34.230000

When I select the same field in a view it returns
34.23

How can I keep the traling zeros in my view?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-24 : 13:43:31

Are you querying the view in Query Analyzer as well? Or is it through your application? If it's from Query Analyzer, please show us the view code.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-03-24 : 13:49:05

I'm querying the table through Query analyzer.
When I create the view pulling the same field in, it drops the zeros.

Select exp_unit_Cost
from poordlin_sql

Using same in creating view and query analyzer.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-03-24 : 19:03:15

[CODE]create table tbl(
dec_16_6 decimal(16, 6)
)

insert into tbl values (34.230000)

select 'select from table', *
from tbl;
go
create view dbo.vw as select * from tbl;
go
select 'select from view', *
from dbo.vw

drop view dbo.vw
drop table tbl[/CODE]Not seeing the problem...

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-03-25 : 08:52:16

I'm guessing its a bug in SQL 2000.
If I try in SQL 2008 its fine.
This person I'm working with has 2000. Is there a way I could add all the decimal places back?

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-03-25 : 09:23:12

I've found my answer.

Select cast(exp_unit_cost) as Varchar

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-25 : 12:48:05

Casting a decimal as a VARCHAR seems like the wrong answer. Did you try casting it to decimal(16, 6) when you selected it in the view?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-25 : 14:33:27

quote:
Originally posted by Vack

I'm querying the table through Query analyzer.
When I create the view pulling the same field in, it drops the zeros.

Select exp_unit_Cost
from poordlin_sql

Using same in creating view and query analyzer.

In addition and explanation to what already suggested, what is the data type of "exp_unit_Cost" field in "poordlin_sql" table? Is it decimal(16,6)? If yes, then as Bustaz Kool suggested, I too don't think you should be having an issue. And if not, then as Lamprey suggested, cast/convert it to the desired datatype with in the view query.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources