Help with update forms: defined

Showing posts with label defined. Show all posts

Friday, March 23, 2012

HELP! Problem with data selection.

Hi,
People, help me with problem to make the query qExpectedRESULT
I will accept any suggestion and suppositions!
Possibly use of User Defined Functions it is a right way ?
-- START of DB Objects CREATE scripts --
CREATE TABLE [dbo].[Customers] (
[AG_ID] [int] IDENTITY (1, 1) NOT NULL ,
[AG_TYPE] [tinyint] NULL ,
[AG_STATE] [tinyint] NULL ,
[AG_CODE] [smallint] NULL ,
[AG_REG_NO] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[AG_REG_NAME] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[AG_REG_DATE] [datetime] NULL ,
[AG_PRINT_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AG_SEARCH_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AG_CR_DATE] [datetime] NULL ,
[AG_MD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DealPRICE] (
[DP_ID] [int] IDENTITY (1, 1) NOT NULL ,
[AG_ID] [int] NULL ,
[ART_ID] [int] NULL ,
[DP_DATE] [datetime] NULL ,
[DEAL_PRICE] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Items] (
[ART_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ART_TYPE] [tinyint] NULL ,
[ART_STATE] [tinyint] NULL ,
[ART_FOLDER_ID] [int] NULL ,
[ART_MSK_ID] [int] NULL ,
[ART_DIN_ID] [int] NULL ,
[ART_LEVEL] [tinyint] NULL ,
[ART_INDEX] [smallint] NULL ,
[ART_NO] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ART_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[ART_V1] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V2] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V3] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V4] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V5] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_CR_DATE] [datetime] NULL ,
[ART_MD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsDIN] (
[DIN_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DIN_TYPE] [tinyint] NULL ,
[DIN_INDEX] [tinyint] NULL ,
[DIN_GROUP] [int] NULL ,
[DIN_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[DIN_ALTER] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_TEXT] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_TEXT_STR] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_PRICE_TYPE] [tinyint] NULL ,
[DIN_PRICE_UP] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsMASK] (
[MSK_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MSK_TYPE] [tinyint] NULL ,
[MSK_INDEX] [smallint] NULL ,
[MSK_MAIN] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MSK_DESCRIPTION] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[MSK_MASK] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[MSK_PART1] [int] NULL ,
[MSK_PART2] [int] NULL ,
[MSK_PART3] [int] NULL ,
[MSK_PART4] [int] NULL ,
[MSK_PART5] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Stores] (
[SKD_ID] [int] IDENTITY (1, 1) NOT NULL ,
[SKD_TYPE] [tinyint] NULL ,
[SKD_STATE] [tinyint] NULL ,
[SKD_ART_ID] [int] NULL ,
[SKD_UPDATED] [bit] NULL ,
[SKD_NOW_QUANT] [money] NULL ,
[SKD_NOW_REZRV] [money] NULL ,
[SKD_NOW_PREP] [money] NULL ,
[SKD_NOW_UNREG] [money] NULL ,
[SKD_NOW_MOD] [money] NULL ,
[SKD_NOW_NED] [money] NULL ,
[SKD_LIMIT_MIN] [money] NULL ,
[SKD_LIMIT_MAX] [money] NULL ,
[SKD_PRICE] [money] NULL ,
[SKD_LAST_SALE] [datetime] NULL ,
[SKD_CHG_DATE] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[AG_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DealPRICE] WITH NOCHECK ADD
CONSTRAINT [PK_DealPRICE] PRIMARY KEY CLUSTERED
(
[DP_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Items] WITH NOCHECK ADD
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[ART_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemsDIN] WITH NOCHECK ADD
CONSTRAINT [PK_ItemsDIN] PRIMARY KEY CLUSTERED
(
[DIN_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemsMASK] WITH NOCHECK ADD
CONSTRAINT [PK_ItemsMASK] PRIMARY KEY CLUSTERED
(
[MSK_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Stores] WITH NOCHECK ADD
CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED
(
[SKD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DealPRICE] ADD
CONSTRAINT [FK_DealPRICE_Customers] FOREIGN KEY
(
[AG_ID]
) REFERENCES [dbo].[Customers] (
[AG_ID]
),
CONSTRAINT [FK_DealPRICE_Items] FOREIGN KEY
(
[ART_ID]
) REFERENCES [dbo].[Items] (
[ART_ID]
)
GO
ALTER TABLE [dbo].[Items] ADD
CONSTRAINT [FK_Items_ItemsDIN] FOREIGN KEY
(
[ART_DIN_ID]
) REFERENCES [dbo].[ItemsDIN] (
[DIN_ID]
),
CONSTRAINT [FK_Items_ItemsMASK] FOREIGN KEY
(
[ART_MSK_ID]
) REFERENCES [dbo].[ItemsMASK] (
[MSK_ID]
)
GO
ALTER TABLE [dbo].[Stores] ADD
CONSTRAINT [FK_Stores_Items] FOREIGN KEY
(
[SKD_ART_ID]
) REFERENCES [dbo].[Items] (
[ART_ID]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.qBaseQUERY
AS
SELECT dbo.Items.*, dbo.Stores.*, dbo.ItemsDIN.DIN_NAME AS DIN_NAME,
dbo.ItemsMASK.MSK_PART1 AS MSK_PART1,
dbo.ItemsMASK.MSK_PART2 AS MSK_PART2,
dbo.ItemsMASK.MSK_PART3 AS MSK_PART3, dbo.ItemsMASK.MSK_PART4 AS MSK_PART4,
dbo.ItemsMASK.MSK_PART5 AS MSK_PART5
FROM dbo.Items LEFT OUTER JOIN
dbo.ItemsDIN ON dbo.Items.ART_DIN_ID =
dbo.ItemsDIN.DIN_ID LEFT OUTER JOIN
dbo.ItemsMASK ON dbo.Items.ART_MSK_ID =
dbo.ItemsMASK.MSK_ID LEFT OUTER JOIN
dbo.Stores ON dbo.Items.ART_ID = dbo.Stores.SKD_ART_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.qExpectedRESULT
AS
SELECT ART_ID, ART_NAME, SKD_NOW_QUANT, SKD_PRICE, 'from DealPRICE
table' AS DEAL_PRICE
FROM dbo.qBaseQUERY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- END of DB Objects CREATE scripts --
-- Fill Tables ----
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerA','CustomerA','Customer
A')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerB','CustomerB','Customer
B')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerC','CustomerC','Customer
C')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerD','CustomerD','Customer
D')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerE','CustomerE','Customer
E')
INSERT INTO Items(ART_NAME) VALUES('ItemA')
INSERT INTO Items(ART_NAME) VALUES('ItemB')
INSERT INTO Items(ART_NAME) VALUES('ItemC')
INSERT INTO Items(ART_NAME) VALUES('ItemD')
INSERT INTO Items(ART_NAME) VALUES('ItemE')
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(1,453,10.95)
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(3,675,15.95)
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(5,134,20.95)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,1,GETDATE(),10.55)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,2,GETDATE(),13)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,3,GETDATE(),13.5)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(2,3,GETDATE(),14.3)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(3,4,GETDATE(),15)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(4,5,GETDATE(),18.9)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(5,5,GETDATE(),19.1)
-- END of Fill Tables ---
Query qExpectedRESULT for Customers.AG_ID=1 must return the next list:
1 ItemA 453 10,95 10,55
2 ItemB NULL NULL 13
3 ItemC 675 15,95 13,5
4 ItemD NULL NULL NULL
5 ItemE 134 20,95 NULL
for Customers.AG_ID=2:
1 ItemA 453 10,95 NULL
2 ItemB NULL NULL NULL
3 ItemC 675 15,95 14.3
4 ItemD NULL NULL NULL
5 ItemE 134 20,95 NULL
In a real DB like:
Customers - 30 000 rows
Items - 25 000 rows
Stores - 15 000 rows
DealPRICE - 1 000 rows
im not select all 25000 rows:
SELECT qBaseQUERY.* -- base query
FROM qBaseQUERY -- about 25 000 rows
next text added on clients terminals depended on their needs, like:
WHERE qBaseQUERY.ART_MSK_ID=13 -- return 10...30 rows
AND ((((qBaseQUERY.MSK_PART1) = 1))
AND (((qBaseQUERY.ART_V1) = '100')))
AND ((((qBaseQUERY.MSK_PART2) = 3))
AND (((qBaseQUERY.ART_V2) = '050')))
AND ((((qBaseQUERY.MSK_PART3) = 12))
AND (((qBaseQUERY.ART_V3) = '058')))
AND ((((qBaseQUERY.MSK_PART4) = 11))
AND (((qBaseQUERY.ART_V4) = '001')))
AND ((((qBaseQUERY.MSK_PART5) = 36))
AND (((qBaseQUERY.ART_V5) = '105')))
AND (((qBaseQUERY.SKD_NOW_QUANT)>0) OR ((qBaseQUERY.SKD_NOW_UNREG)>0) )
ORDER BY qBaseQUERY.ART_FOLDER_ID, qBaseQUERY.ART_LEVEL,
qBaseQUERY.ART_INDEX
--
What do you think about it ?"Kachmaryk Yuriy" <kachya@.ua.fm> wrote in message
news:O39Wii2HGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> People, help me with problem to make the query qExpectedRESULT
> I will accept any suggestion and suppositions!
> Possibly use of User Defined Functions it is a right way ?
>
How about
SELECT B.ART_ID, B.ART_NAME, B.SKD_NOW_QUANT, B.SKD_PRICE, DP.DEAL_PRICE
FROM dbo.qBaseQUERY B
LEFT JOIN dbo.dealPRICE DP
ON B.ART_ID = DP.ART_ID
and AG_ID = 1
?
David

Monday, March 19, 2012

Help! How do I set a function as a default?

Help
I want to set a user defined function as the default for a
column.
How do I do that?
I think you in advance,
HeidiTry:
alter table MyTable
add constraint MyDefault default (dbo.MyFunc (123)) for MyColumn
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Heidi" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c4394d$7a0f3ae0$a101280a@.phx.gbl...
Help
I want to set a user defined function as the default for a
column.
How do I do that?
I think you in advance,
Heidi

Help! How do I set a function as a default?

Help
I want to set a user defined function as the default for a
column.
How do I do that?
I think you in advance,
Heidi
Try:
alter table MyTable
add constraint MyDefault default (dbo.MyFunc (123)) for MyColumn
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Heidi" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c4394d$7a0f3ae0$a101280a@.phx.gbl...
Help
I want to set a user defined function as the default for a
column.
How do I do that?
I think you in advance,
Heidi

Monday, March 12, 2012

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?Separate presentation from data. SQL Server returns data values and the value 0 is the same as the
value 0.00. The client application presents the binary values returned by SQL Server in a
human-readable form (like number with decimal points). If you didn't write the client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Services, etc, then these
tools will present the values according to the datatype *for the column'. I.e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the person writing the too
who makes these decision, but it applies to all values for the column. The only way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to handle this in the client
app. All client app designed for end users has functionality for formatting data (like for instance
a format() function).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegroups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>|||Thanks Tibor.
Unfortunately, handling this at the UI end is not an option. I'm
converting an app (from Access). I need the data to be exactly the
same. I unable to change the UI at this time.|||Plus, I would like to know why SQL Server is rounding that value. To
me this looks like a defect. It should not be rounding the value.|||I'm not sure I understand your comments about rounding. Let's work with executable code instead.
Can, you, based on below, describe when you want:
CREATE TABLE y(c1 float)
INSERT INTO y (c1) VALUES (NULL)
INSERT INTO y (c1) VALUES (123.45)
INSERT INTO y (c1) VALUES (555.70)
INSERT INTO y (c1) VALUES (35444.21)
INSERT INTO y (c1) VALUES (35444.79)
SELECT
CASE WHEN c1 IS NULL THEN 0 ELSE c1 END
FROM y
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174942335.112146.304030@.n59g2000hsh.googlegroups.com...
> Plus, I would like to know why SQL Server is rounding that value. To
> me this looks like a defect. It should not be rounding the value.
>|||The problem is in the last two writes in the example I gave.
Hopefully this explains it more...Run this code to create table Z.
Run this:
---
CREATE TABLE z(c1 varchar(15))
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
END FROM y
Here is what ends up in z for me:
---
0
123.45
555.7
35444.2
35444.8
The last two records get rounded to one decimal place. Why does that
happen? When I cast it as money, the values do not round:
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
CAST(y.c1 AS Money) END FROM y
0.00
123.45
555.70
35444.21
35444.79
But now the '0' is '0.00'. Which is NOT what I want.|||I really don't know why SQL Server would cast the float values that way. In general, I avoid float
being what sometime is called an approximate datatype. So, it seems that when SQL Server converts
from float to varchar, you see some funny things. I would suggest you cast to something else, like
decimal And if you *really* want to do presentation logic in the engine, you can convert NULL to 0,
cast to varchar, then replace '0.00' with 0. See below:
INSERT INTO z ([c1])
SELECT CASE WHEN c = '0.00' THEN '0' ELSE c END
FROM
(
SELECT
CAST(
CAST(
ISNULL(y.c1, 0)
AS decimal (9,2))
AS varchar(15)) AS c
FROM y
) AS inr
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1175032433.780317.287070@.d57g2000hsg.googlegroups.com...
> The problem is in the last two writes in the example I gave.
> Hopefully this explains it more...Run this code to create table Z.
> Run this:
> ---
> CREATE TABLE z(c1 varchar(15))
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
> END FROM y
> Here is what ends up in z for me:
> ---
> 0
> 123.45
> 555.7
> 35444.2
> 35444.8
> The last two records get rounded to one decimal place. Why does that
> happen? When I cast it as money, the values do not round:
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
> CAST(y.c1 AS Money) END FROM y
> 0.00
> 123.45
> 555.70
> 35444.21
> 35444.79
> But now the '0' is '0.00'. Which is NOT what I want.
>|||Thanks Tibor! That was helpful.

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?
Separate presentation from data. SQL Server returns data values and the value 0 is the same as the
value 0.00. The client application presents the binary values returned by SQL Server in a
human-readable form (like number with decimal points). If you didn't write the client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Services, etc, then these
tools will present the values according to the datatype *for the column'. I.e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the person writing the too
who makes these decision, but it applies to all values for the column. The only way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to handle this in the client
app. All client app designed for end users has functionality for formatting data (like for instance
a format() function).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegro ups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>
|||Thanks Tibor.
Unfortunately, handling this at the UI end is not an option. I'm
converting an app (from Access). I need the data to be exactly the
same. I unable to change the UI at this time.
|||Plus, I would like to know why SQL Server is rounding that value. To
me this looks like a defect. It should not be rounding the value.
|||I'm not sure I understand your comments about rounding. Let's work with executable code instead.
Can, you, based on below, describe when you want:
CREATE TABLE y(c1 float)
INSERT INTO y (c1) VALUES (NULL)
INSERT INTO y (c1) VALUES (123.45)
INSERT INTO y (c1) VALUES (555.70)
INSERT INTO y (c1) VALUES (35444.21)
INSERT INTO y (c1) VALUES (35444.79)
SELECT
CASE WHEN c1 IS NULL THEN 0 ELSE c1 END
FROM y
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174942335.112146.304030@.n59g2000hsh.googlegr oups.com...
> Plus, I would like to know why SQL Server is rounding that value. To
> me this looks like a defect. It should not be rounding the value.
>
|||The problem is in the last two writes in the example I gave.
Hopefully this explains it more...Run this code to create table Z.
Run this:
CREATE TABLE z(c1 varchar(15))
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
END FROM y
Here is what ends up in z for me:
0
123.45
555.7
35444.2
35444.8
The last two records get rounded to one decimal place. Why does that
happen? When I cast it as money, the values do not round:
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
CAST(y.c1 AS Money) END FROM y
0.00
123.45
555.70
35444.21
35444.79
But now the '0' is '0.00'. Which is NOT what I want.
|||I really don't know why SQL Server would cast the float values that way. In general, I avoid float
being what sometime is called an approximate datatype. So, it seems that when SQL Server converts
from float to varchar, you see some funny things. I would suggest you cast to something else, like
decimal And if you *really* want to do presentation logic in the engine, you can convert NULL to 0,
cast to varchar, then replace '0.00' with 0. See below:
INSERT INTO z ([c1])
SELECT CASE WHEN c = '0.00' THEN '0' ELSE c END
FROM
(
SELECT
CAST(
CAST(
ISNULL(y.c1, 0)
AS decimal (9,2))
AS varchar(15)) AS c
FROM y
) AS inr
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1175032433.780317.287070@.d57g2000hsg.googlegr oups.com...
> The problem is in the last two writes in the example I gave.
> Hopefully this explains it more...Run this code to create table Z.
> Run this:
> CREATE TABLE z(c1 varchar(15))
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
> END FROM y
> Here is what ends up in z for me:
> 0
> 123.45
> 555.7
> 35444.2
> 35444.8
> The last two records get rounded to one decimal place. Why does that
> happen? When I cast it as money, the values do not round:
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
> CAST(y.c1 AS Money) END FROM y
> 0.00
> 123.45
> 555.70
> 35444.21
> 35444.79
> But now the '0' is '0.00'. Which is NOT what I want.
>
|||Thanks Tibor! That was helpful.

Sunday, February 19, 2012

Help with user defined functions

Dear all,

I was given a project to transfer our database into sql server database.

In our previous database we used the datatype int4 for some columns to create some views and in some queries that we used to build our datawindows. In SQLServer 2000 i created a user defined function named int4. I can execute it with the line select dbo.int4(poso) from employee .

Unfortrunately this way make me to rebuild all my datawindows and replace int4( with dbo.int4( . Is there any way to execute queries using user defined function but omitting the first part name dbo. I mean to manage execute the command select int4(poso) from employee \\let int4 be a user definded function.

If i can’t solve this, i thing it will decided than is impossible to move to sqlserver Database. Has anyone any suggestions?

Thanks in advance,

Best regards,

Hellen

Then you are out of luck, the call to functions in SQL Server always have to use the owner prefix (in your case appearantly dbo).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help with user defined function

I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.

It works perfect except for one issue. It doesn't check to see if today's date is a holiday.

I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"

It then moves ahead two business days and returns that date.

Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.

I really don't want to rewrite the whole script .

Code------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

--DROP FUNCTION GetStartDate

--declare function receiving two parameters -
--the date we start counting and the number of business days

CREATE FUNCTION GetStartDate (@.startdate datetime, @.days int)
RETURNS datetime
AS
BEGIN

--declare a counter to keep track of how many days are passing
declare @.counter int

/*
Check your business rules. If 4 business days means you
count starting tomorrow, set counter to 0. If you start
counting today, set counter to 1
*/
set @.counter = 1

--declare a variable to hold the ending date
declare @.enddate datetime

--set the end date to the start date. we'll be
-- incrementing it for each passing business day
set @.enddate = @.startdate

/*
Start your loop.
While your counter (which was set to 1), is less than
or equal to the number of business days increment your
end date
*/
WHILE @.counter <= @.days

BEGIN

--for each day, we'll add one to the end date
set @.enddate = DATEADD(dd, 1, @.enddate)

--If the day is between 2 and 6 (meaning it's a week
--day and the day is not in the holiday table, we'll
--increment the counter
IF (DATEPART(dw, @.enddate) between 2 and 6) AND
(@.enddate not in
(
select HolidayDate
from tFederalHoliday
where [HolidayYear] = datepart(yyyy,@.enddate)
)
)
BEGIN
set @.counter = @.counter + 1
END

--end the while loop
END

--return the end date
RETURN @.enddate

--end the function
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------

You can have a table with a list of holidays (both your company declared holidays and public holidays) and have the query check against the table in your UDF. This gives you the flexibility of adding/removing new holidays each year. You can either put the holidays in a table or even hardcode them in the UDF, whichever works best for you.

Help with user defined function

I have the following function:
create function dbo.AttributesList (@.Customerno varchar(10),
@.sold_to_sales_grp varchar(3), @.attr varchar(2))
returns varchar(15)
as
begin
declare @.sOut varchar(15)
set @.sOut = ''
if @.attr = '1'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '2'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '3'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
-- previous
if @.attr = '4'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels_prev x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '5'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels_prev y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '6'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels_prev z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
if len(@.sOut) > 2
set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
return @.sOut
end
which you call with a customer number, a sales group, and an attribute.
The tables it queries are built in a job that gets run overnight.
The results of the query are supposed to string together a distribution
channel if there are more than 1 returned.
product1 could return CH
product2 could return CH, DL
product3 could return nothing
The function as it is works properly. I get the anticipated results
when I run a query that calls this user function.
However, I want to write an online app that will allow the user to
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
The problem is - I can't make it work all the time.
If I put in the where clause:
'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
on for each of the 6 possibilities, it'll return ones with a
Distribution channel = DL but only ones which START with a DL. If DL is
the second one in the list, it doesn't return anything.
I have also tried this in the HAVING clause, to see if I can get it to
come out there but can't.
How can I take the distribution channel (in this case DL) and return
records where DL is in any of the 6 product groups?
Any ideas/suggestions appreciated. I'm stumped.
BCBlasting Cap (goober@.christian.net) writes:
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + x.distr_channel
> from product1_channels x
> where x.cust_no = @.Customerno and
>...
> The function as it is works properly. I get the anticipated results
> when I run a query that calls this user function.
> However, I want to write an online app that will allow the user to
> select a channel from a dropdown box, and the query will then return any
> data with that distribution channel anywhere in the current or
> previous year's product areas.
> The problem is - I can't make it work all the time.
This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right.
If you are on SQL 2005, there is built-in syntax for this thanks to
the FOR XML construct, here demonstrated by a sample query that I
keep around:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<<This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right. >>
I am using SQL 2000.
The place I'm running the report is just a straight select, with the
functions being called for 6 columns being returned.
select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region
having
-- if they have bought things in any of the last 3 years
(len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
How would you work a cursor into that select?
DECLARE @.AuthorID char(11)
DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors
OPEN c1
FETCH NEXT FROM c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.AuthorID
FETCH NEXT FROM c1
INTO @.AuthorID
END
CLOSE c1
DEALLOCATE c1
Also - is there a more efficient way to do this?
Thanks,
BC

> Blasting Cap (goober@.christian.net) writes:
when I run a query that calls this user function.
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
> This is because the correct behaviour of this query is undefined. See
> http://support.microsoft.com/default.aspx?scid=287515.
> If you are on SQL 2000, you will need to run a cursor to get this
> right.
> If you are on SQL 2005, there is built-in syntax for this thanks to
> the FOR XML construct, here demonstrated by a sample query that I
keep around:
> select CustomerID,
> substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
> -- strip the last ',' from the list
> from
> Customers c cross apply
> (select convert(nvarchar(30), OrderID) + ',' as [text()]
> from Orders o
> where o.CustomerID = c.CustomerID
> order by o.OrderID
> for xml path('')) as Dummy(OrdIdList)
> go
>
>|||On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:

>I have the following function:
(snip)
Erland is correct - the UDF depends on undocumented behaviour. Even
though it works today, it might break tomorrow.
But Erland apparently missed the question you asked near the end of your
post:

>If I put in the where clause:
>'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
>on for each of the 6 possibilities, it'll return ones with a
>Distribution channel = DL but only ones which START with a DL. If DL is
>the second one in the list, it doesn't return anything.
And yet, I'll let Erland answer that, since he has authored an excellent
page about this problem, and lots of possible solutions:
http://www.sommarskog.se/arrays-in-sql.html
However, in your case there might be a better way. Instead of first
using undocumented techniques to get a comma-seperated list and then
using a second technique to split those to tables, why not rewrite the
AttributesList to a table-valued function?
Hugo Kornelis, SQL Server MVP|||Blasting Cap (goober@.christian.net) writes:
> The place I'm running the report is just a straight select, with the
> functions being called for 6 columns being returned.
>...
> How would you work a cursor into that select?
In your UDF.

> Also - is there a more efficient way to do this?
Yes. Two options:
1) Upgrade to SQL 2005.
2) Do it client-side.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:
> And yet, I'll let Erland answer that, since he has authored an excellent
> page about this problem, and lots of possible solutions:
> http://www.sommarskog.se/arrays-in-sql.html
> However, in your case there might be a better way. Instead of first
> using undocumented techniques to get a comma-seperated list and then
> using a second technique to split those to tables, why not rewrite the
> AttributesList to a table-valued function?
For the WHERE clause why not simply use an EXISTS against the underlying
table:
WHERE EXISTS (SELECT *
FROM product1_channels p
WHERE p.cust_no = a.cust_no
AND p.distr_channel = 'DL')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Blasting Cap (goober@.christian.net) writes:
> select
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region,
> 'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
> 'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
> 'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
> 'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
> 'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
> 'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
> from sales_customer_list as a
> where cust_no in ('R1411600','R6713155')
> group by
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region
> having
> -- if they have bought things in any of the last 3 years
> (len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
> order by
> a.ship_to_sales_grp,
> a.ship_to_sales_rep_cd,
> cust_name
It seems unnecessary to call the UDFs a second time in the HAVING clause.
I'm a little uncertain of the effect of the GROUP BY in this SELECT as
there is no aggregate functions, but assuming that it works the way you
want I retain them. However you could use a derived table:
SELECT cust_no, cust_name, ...
FROM (select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region) AS x
WHERE len(Prod1_curr) > 0 OR
len(Prod2_curr) > 0 OR
len(Prod3_curr) > 0 OR
len(Prod1_prev) > 0 OR
len(Prod2_prev) > 0 OR
len(Prod3_prev) > 0
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
A derived table is a logical temp table within the query, but not materialis
ed,
and SQL Server recast computation order for the best query plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, March 23, 2012

HELP! Problem with data selection.

Monday, March 19, 2012

Help! How do I set a function as a default?

Help! How do I set a function as a default?

Help! How do I set a function as a default?

Monday, March 12, 2012

HELP! Data Conversion Rounding Problem

HELP! Data Conversion Rounding Problem

HELP! Data Conversion Rounding Problem

Sunday, February 19, 2012

Help with user defined functions

Help with user defined function

Help with user defined function

Help with update forms

Blog Archive

About Me