float to decimal without rounding

select 'Customer_Greeting_Section_total'=
Case when
Customer_Greeting_Section_total is null then 0.00
else left (ROUND(Customer_Greeting_Section_Total , 2, 1),4)
end
from DTSTEMP1
The col is defined as float
When I run this I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.Hi
It is not clear why you are using left, as this a string function as doing
this may be meaningless. Removing the left function will (probably) stop the
error. See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL an
d
example data will help to solve your problem. Posting your desired results
from the query is also useful.
John
"Disney" wrote:

> select 'Customer_Greeting_Section_total'=
> Case when
> Customer_Greeting_Section_total is null then 0.00
> else left (ROUND(Customer_Greeting_Section_Total , 2, 1),4)
> end
> from DTSTEMP1
> The col is defined as float
> When I run this I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.sql

FK "this or that" case best practice

If I have a table that can either have a FK to one table or another, what is
the best way to design it?
eg.
create table template
(
id int not null primary key,
description varchar(50) not null,
roleId int null references role(roleId),
userId int null references user(userId)
)
At the moment, because either a role or a user can own the template, I
haveset both columns to allow null, but I do not like this approach. Would
intermediate tables be better even though it is a 1:1 relationship?
create table template
(
id int not null primary key,
description varchar(50) not null,
)
create table templateuser
(
templateId int null references template(id) primary key,
userId int null references user(userId)
)
create table templaterole
(
templateId int null references template(id) primary key,
roleId int null references role(roleId)
)
Thanks
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--David,
One solution could be enforcing the RI using triggers.
AMB
"David J Rose" wrote:

> If I have a table that can either have a FK to one table or another, what
is
> the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||I think it is easier to go with one table, because then you can enforce with
a check constraint that a template is related to exactly a user or a role
(and not both or none), something which you can't do as easy if you use the
2 extra tables. The check constraint would be:
CONSTRAINT CK_template__either_role_or_user
CHECK((roleId IS NULL AND userID IS NOT NULL) OR (roleId IS NOT NULL AND
userID IS NULL))
Jacco Schalkwijk
SQL Server MVP
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:425d0d17$1_1@.127.0.0.1...
> If I have a table that can either have a FK to one table or another, what
> is the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||What is wrong with having two tables, one for users, one for roles. You
don't HAVE to have a row in both user and role, so this design makes sense.
I would put a FK to both tables, and probably set them to DELETE CASCADE.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:425d0d17$1_1@.127.0.0.1...
> If I have a table that can either have a FK to one table or another, what
> is the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--

FK

I know that in the case of a primary key (non-clustered)
the index part of the foreign key must be stored
separately from the table. Is this true if you have a
foreign key on a table? Is there data stored externally
to the table for a foreign key?
Also: uou don't have to defrag or reindex a foreign key
do you?A foreign key is just a rule. SQL Server doesn't have to store anything
additional as you add the FK constraint. SQL Server will, however, make sure
that your data adheres to your rules, so a check is performed as you modify
data. Creating an index on the foreign key column can be very beneficial to
assist for this validation. Not to mention that you often perform joins over
primary key - foreign key relationships. And remember that a FK column can
be a good candidate for the clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"GXX" <anonymous@.discussions.microsoft.com> wrote in message
news:442201c4020f$c3ca8150$a601280a@.phx.gbl...
> I know that in the case of a primary key (non-clustered)
> the index part of the foreign key must be stored
> separately from the table. Is this true if you have a
> foreign key on a table? Is there data stored externally
> to the table for a foreign key?
> Also: uou don't have to defrag or reindex a foreign key
> do you?

FK

I know that in the case of a primary key (non-clustered)
the index part of the foreign key must be stored
separately from the table. Is this true if you have a
foreign key on a table? Is there data stored externally
to the table for a foreign key?
Also: uou don't have to defrag or reindex a foreign key
do you?A foreign key is just a rule. SQL Server doesn't have to store anything
additional as you add the FK constraint. SQL Server will, however, make sure
that your data adheres to your rules, so a check is performed as you modify
data. Creating an index on the foreign key column can be very beneficial to
assist for this validation. Not to mention that you often perform joins over
primary key - foreign key relationships. And remember that a FK column can
be a good candidate for the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"GXX" <anonymous@.discussions.microsoft.com> wrote in message
news:442201c4020f$c3ca8150$a601280a@.phx.gbl...
> I know that in the case of a primary key (non-clustered)
> the index part of the foreign key must be stored
> separately from the table. Is this true if you have a
> foreign key on a table? Is there data stored externally
> to the table for a foreign key?
> Also: uou don't have to defrag or reindex a foreign key
> do you?