I have ViewA that sums up 4 fields from one table. I then have ViewB that
uses ViewA to calculate the results. Now I do this with 5 different tables
and then link them all to get my final results. Each View has a date range
(begin / end) that I need to pass from a Web form.
How should I go about doing this? Should I create a temporary table to hold
the begin / end dates to use in the sub Views? Or something else? I am at a
lost on how to go about this and need some direction and syntax?
Thanks in advance for your help!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1Chamark via webservertalk.com wrote:
> I have ViewA that sums up 4 fields from one table. I then have ViewB that
> uses ViewA to calculate the results. Now I do this with 5 different tables
> and then link them all to get my final results. Each View has a date range
> (begin / end) that I need to pass from a Web form.
> How should I go about doing this? Should I create a temporary table to hol
d
> the begin / end dates to use in the sub Views? Or something else? I am at
a
> lost on how to go about this and need some direction and syntax?
> Thanks in advance for your help!!
>
Post your DDL, and let's start by eliminating these unnecessary views.
Simplify this whole mess into a single query, making it easier to pass
date parameters to.|||>> I have ViewA that sums up 4 fields [sic] from one table. I then have ViewB tha
t uses ViewA to calculate the results. <<
Fileds and columns are totally different concepts.
VIEWs do not take parameters; stored procedures take parameters. Since
this is an SQL newsgroup, we really don't care about the Web form part.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it and all your terms are wrong.|||Hi Chamark,
Use a stored procedure and call that from your application, without seeing
your DDL (create table and create view stuff) its difficult but this might
give you an idea...
create proc get_yourstuff
@.range1_start datetime,
@.range1_end datetime,
@.range2_start datetime,
@.range2_end datetime
as
begin
select ...
from view1 as v1
inner join view2 as v2 on v2.yoursurrogatekey = v1.yoursurrogatekey
where v1.yourdate between @.range1_start and @.range1_end
and v2.yourdate between @.range2_start and @.range2_end
order by ...
end
PS... ignore celko's comment about not posting about the webform part - the
guy is an idiot, this is a SQL Server programming group and your post is
fine.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6247bb4bcf5d5@.uwe...
>I have ViewA that sums up 4 fields from one table. I then have ViewB that
> uses ViewA to calculate the results. Now I do this with 5 different tables
> and then link them all to get my final results. Each View has a date range
> (begin / end) that I need to pass from a Web form.
> How should I go about doing this? Should I create a temporary table to
> hold
> the begin / end dates to use in the sub Views? Or something else? I am at
> a
> lost on how to go about this and need some direction and syntax?
> Thanks in advance for your help!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> Since
> this is an SQL newsgroup, we really don't care about the Web form part.
You are the LAST person to give any advice on posting content here, you
don't seem to realise this is a forum for MICROSOFT SQL SERVER PROGRAMMING
and NOT a SQL only forum.
Next time you post, check what forum you are posting to, this is
MICROSOFT.PUBLIC.SQLSERVER.PROGRAMMING.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151206577.847462.67840@.b68g2000cwa.googlegroups.com...
> Fileds and columns are totally different concepts.
>
> VIEWs do not take parameters; stored procedures take parameters. Since
> this is an SQL newsgroup, we really don't care about the Web form part.
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it and all your terms are wrong.
>|||Thanks Tracy,
I haven't posted the DDL before. I am hoping the following is what you are
referencing. If not please advise.
Associate is my common field.
CREATE DATABASE [CSSMetrics] ON (NAME = N'MetricsSQL_dat', FILENAME = N'C:\
Program Files\Microsoft SQL Server\MSSQL\data\MetricsSQL.mdf' , SIZE = 1405,
FILEGROWTH = 10%) LOG ON (NAME = N'MetricsSQL_log', FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL\data\MetricsSQL.ldf' , SIZE = 4112,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'CSSMetrics', N'autoclose', N'false'
GO
exec sp_dboption N'CSSMetrics', N'bulkcopy', N'false'
GO
exec sp_dboption N'CSSMetrics', N'trunc. log', N'false'
GO
exec sp_dboption N'CSSMetrics', N'torn page detection', N'true'
GO
exec sp_dboption N'CSSMetrics', N'read only', N'false'
GO
exec sp_dboption N'CSSMetrics', N'dbo use', N'false'
GO
exec sp_dboption N'CSSMetrics', N'single', N'false'
GO
exec sp_dboption N'CSSMetrics', N'autoshrink', N'false'
GO
exec sp_dboption N'CSSMetrics', N'ANSI null default', N'false'
GO
exec sp_dboption N'CSSMetrics', N'recursive triggers', N'false'
GO
exec sp_dboption N'CSSMetrics', N'ANSI nulls', N'false'
GO
exec sp_dboption N'CSSMetrics', N'concat null yields null', N'false'
GO
exec sp_dboption N'CSSMetrics', N'cursor close on commit', N'false'
GO
exec sp_dboption N'CSSMetrics', N'default to local cursor', N'false'
GO
exec sp_dboption N'CSSMetrics', N'quoted identifier', N'false'
GO
exec sp_dboption N'CSSMetrics', N'ANSI warnings', N'false'
GO
exec sp_dboption N'CSSMetrics', N'auto create statistics', N'true'
GO
exec sp_dboption N'CSSMetrics', N'auto update statistics', N'true'
GO
use [CSSMetrics]
GO
CREATE TABLE [dbo].[Bonus] (
[BonusDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CSI] (
[Part Surv Id] [float] NULL ,
[Seq C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prod Id C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [smalldatetime] NULL ,
[#] [float] NULL ,
[Resp Val Id] [float] NULL ,
[Adjusted Weight] [float] NULL ,
[ExtSatWt] [float] NULL ,
[VerSatWt] [float] NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Surv Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CSI-Disconnect] (
[Date] [smalldatetime] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Call Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Call Scores] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Goals] (
[Segment] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Adherence] [float] NULL ,
[Efficiency] [float] NULL ,
[AHT] [float] NULL ,
[Shift Account] [float] NULL ,
[Top 2 Box] [float] NULL ,
[Disconnect] [float] NULL ,
[Call Score] [float] NULL ,
[Retained] [float] NULL ,
[RWI] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[National AR] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN Count] [float] NULL ,
[RTF] [money] NULL ,
[RTC] [money] NULL ,
[CashedOut] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[National Call Stats] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep Ssn] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NchQty] [float] NULL ,
[SchdOpenSecsQty] [float] NULL ,
[LogOnSecsQty] [float] NULL ,
[InAdherenceSecsQty] [float] NULL ,
[OutOfAdherenceSecsQty] [float] NULL ,
[HoldSecsQty] [float] NULL ,
[TotalHandleTime] [float] NULL ,
[TalkHoldAvailable] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OPA] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OPA Code] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Opa Seconds Qty] [float] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Tracy McKibben wrote:
>[quoted text clipped - 6 lines]
>Post your DDL, and let's start by eliminating these unnecessary views.
>Simplify this whole mess into a single query, making it easier to pass
>date parameters to.
Message posted via http://www.webservertalk.com|||Chamark via webservertalk.com wrote:
> Thanks Tracy,
> I haven't posted the DDL before. I am hoping the following is what you are
> referencing. If not please advise.
>
Need to see your CREATE VIEW statements too, it's not clear what you
meant by "sums up 4 fields" and "calculate the results".
> Tracy McKibben wrote:
>|||I use all these views joined together to get my final results. Each sub view
has the date range. I need one common place to supply the data range coming
from a Web form to supply these views.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_AR_STATS_VIEW
AS
SELECT TOP 10000 dbo.ASSOC_AR_SUMS_VIEW.Team, dbo.ASSOC_AR_SUMS_VIEW.
Segment, dbo.ASSOC_AR_SUMS_VIEW.Associate,
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTF, 0) AS RTF,
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTC, 0) AS RTC,
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfCashedOut, 0) AS
[Cashed Out], NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTF, 0)
/ (NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTF, 0) + NULLIF
(dbo.ASSOC_AR_SUMS_VIEW.SumOfRTC, 0)) AS Retention,
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfCashedOut, 0) /
NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfCashedOut, 0)
+ NULLIF (dbo.ASSOC_AR_SUMS_VIEW.SumOfRTF, 0) AS COR,
dbo.Goals.Retained
FROM dbo.ASSOC_AR_SUMS_VIEW INNER JOIN
dbo.Goals ON dbo.ASSOC_AR_SUMS_VIEW.Segment = dbo.Goals.
Segment AND dbo.ASSOC_AR_SUMS_VIEW.[Date] >= '5/1/2006' AND
dbo.ASSOC_AR_SUMS_VIEW.[Date] <= '5/20/2006' AND dbo.
ASSOC_AR_SUMS_VIEW.Team = 'Abbott'
ORDER BY dbo.ASSOC_AR_SUMS_VIEW.Team, dbo.ASSOC_AR_SUMS_VIEW.Segment
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_AR_SUMS_VIEW
AS
SELECT TOP 10000 Team, Segment, Associate, SUM([SSN Count]) AS
Opportunities, SUM(RTF) AS SumOfRTF, SUM(RTC) AS SumOfRTC, SUM(CashedOut)
AS SumOfCashedOut, [Date], Site
FROM dbo.[National AR]
GROUP BY Team, Segment, Associate, [Date], Site
ORDER BY Team, Segment, Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CALL_STATS_VIEW
AS
SELECT dbo.ASSOC_CALL_SUMS_VIEW.Team, dbo.ASSOC_CALL_SUMS_VIEW.Associate
,
dbo.ASSOC_CALL_SUMS_VIEW.SumOfInAdherenceSecsQty / (dbo.
ASSOC_CALL_SUMS_VIEW.SumOfInAdherenceSecsQty + dbo.ASSOC_CALL_SUMS_VIEW.
SumOfOutOfAdherenceSecsQty)
AS Adherence, dbo.ASSOC_CALL_SUMS_VIEW.
SumOfTalkHoldAvailable / dbo.ASSOC_CALL_SUMS_VIEW.SumOfLogOnSecsQty AS
Efficiency,
[sumofTotalHandleTime ] / [sumofNchQty ] AS AHT,
dbo.ASSOC_CALL_SUMS_VIEW.SumOfHoldSecsQty / dbo.
ASSOC_CALL_SUMS_VIEW.SumOfNchQty AS HT,
dbo.ASSOC_CALL_SUMS_VIEW.SumOfLogOnSecsQty / dbo.
ASSOC_CALL_SUMS_VIEW.SumOfSchdOpenSecsQty AS [Shift Account],
dbo.ASSOC_CALL_SUMS_VIEW.SumOfNchQty AS [Calls Taken],
dbo.Goals.Adherence AS [Adherence Goal], dbo.Goals.Efficiency AS [Efficiency
Goal],
dbo.Goals.AHT AS [AHT Goal], dbo.Goals.[Shift Account]
AS [SA Goal]
FROM dbo.ASSOC_CALL_SUMS_VIEW INNER JOIN
dbo.Goals ON dbo.ASSOC_CALL_SUMS_VIEW.Segment = dbo.
Goals.Segment
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CALL_SUMS_VIEW
AS
SELECT TOP 10000 Team, Segment, Associate, SUM(NchQty) AS SumOfNchQty,
SUM(SchdOpenSecsQty) AS SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
SumOfOutOfAdherenceSecsQty,
SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
(TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
SumOfTalkHoldAvailable,
[Date]
FROM dbo.[National Call Stats]
GROUP BY Team, Associate, Segment, [Date]
ORDER BY Team, Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CSI_DISC_STATS_VIEW
AS
SELECT TOP 1000 dbo.ASSOC_CSI_DISC_SUMS_VIEW.Team, dbo.
ASSOC_CSI_DISC_SUMS_VIEW.Associate,
NULLIF (dbo.ASSOC_CSI_DISC_SUMS_VIEW.SumOfH1, 0) /
NULLIF (dbo.ASSOC_CSI_DISC_SUMS_VIEW.[SumOfLast Touch], 0) AS Disconnect,
dbo.Goals.[Disconnect] AS [Disconnect Goal]
FROM dbo.ASSOC_CSI_DISC_SUMS_VIEW INNER JOIN
dbo.Goals ON dbo.ASSOC_CSI_DISC_SUMS_VIEW.Segment = dbo.
Goals.Segment
ORDER BY dbo.ASSOC_CSI_DISC_SUMS_VIEW.Team, dbo.ASSOC_CSI_DISC_SUMS_VIEW.
Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CSI_DISC_SUMS_VIEW
AS
SELECT TOP 5000 dbo.[CSI-Disconnect].Team, dbo.[National Call Stats].
Segment, dbo.[CSI-Disconnect].Associate, SUM(dbo.[CSI-Disconnect].H1) AS
SumOfH1,
SUM(dbo.[CSI-Disconnect].[Last Touch]) AS [SumOfLast
Touch]
FROM dbo.[CSI-Disconnect] RIGHT OUTER JOIN
dbo.[National Call Stats] ON dbo.[CSI-Disconnect].[Date]
= dbo.[National Call Stats].[Date] AND
dbo.[CSI-Disconnect].Site = dbo.[National Call Stats].
Site AND dbo.[CSI-Disconnect].Associate = dbo.[National Call Stats].Associat
e
WHERE (dbo.[CSI-Disconnect].[Date] >= '5/1/2006') AND (dbo.[CSI-
Disconnect].[Date] <= '5/31/2006') AND (dbo.[CSI-Disconnect].Site = 'Dallas')
AND
(dbo.[CSI-Disconnect].Team = 'Abbott')
GROUP BY dbo.[CSI-Disconnect].Team, dbo.[CSI-Disconnect].Associate, dbo.
[National Call Stats].Segment
HAVING (dbo.[CSI-Disconnect].Team <> N' ')
ORDER BY dbo.[CSI-Disconnect].Team, dbo.[CSI-Disconnect].Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CSI_STATS_VIEW
AS
SELECT TOP 10000 dbo.ASSOC_CSI_SUMS_VIEW.Team, dbo.ASSOC_CSI_SUMS_VIEW.
Associate,
([Sumofextsatwt ] + dbo.ASSOC_CSI_SUMS_VIEW.
SumOfVerSatWt) / dbo.ASSOC_CSI_SUMS_VIEW.[SumOfAdjusted Weight] AS [CSI
Overall],
dbo.ASSOC_CSI_SUMS_VIEW.[CountOfSurv Strt Tm] AS
Surveys, dbo.Goals.[Top 2 Box] AS [CSI Goal]
FROM dbo.ASSOC_CSI_SUMS_VIEW INNER JOIN
dbo.Goals ON dbo.ASSOC_CSI_SUMS_VIEW.Segment = dbo.
Goals.Segment
ORDER BY dbo.ASSOC_CSI_SUMS_VIEW.Team, dbo.ASSOC_CSI_SUMS_VIEW.Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.ASSOC_CSI_SUMS_VIEW
AS
SELECT TOP 10000 Team, Segment, Associate, SUM([Adjusted Weight]) AS
[SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt, SUM(VerSatWt)
AS SumOfVerSatWt, COUNT([Surv Strt Tm]) AS [CountOfSurv
Strt Tm], [Date]
FROM dbo.CSI
WHERE ([Date] >= ' 5/1/2006') AND ([Date] <= '5/31/2006') AND (Site =
'Dallas') AND (Team = 'Abbott')
GROUP BY Team, Associate, Segment, [Date]
ORDER BY Team, Associate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Tracy McKibben wrote:
>Need to see your CREATE VIEW statements too, it's not clear what you
>meant by "sums up 4 fields" and "calculate the results".
>
>[quoted text clipped - 3 lines]
Message posted via http://www.webservertalk.com|||Yes, this is DDL. And **everything** in it is wrong. If I put this in
one of my books, people would think I made it up
1. Why is everything NULL-able? That means you cannot ever have a key.
Tables by definition must have keys.
2. What careful research lead to you to discover the amazing fact
almost everything in your universe is not only NULL-able but also
NVARCHAR (255)? In your world, I use the Diamond Sutra in Chinese for
a ZIP code. And someone will. These tables will accumulate crap the
minute they go into production.
3. Why do you have data element names with spaces or special characters
in them? My personal favorite is "# FLOAT NULL". Octothrope!?
That is the sort of thing you in an old beginning programming book
where they give you a list of illegal names. We allowed quoted
identifiers in SQL so that future reserved words could be used in
current code. It was never, never meant for doing display work in the
database. What you have found is a great way to prevent readability,
portability and maintainability of code!
4. So many of the names are vague and violate ISO-11179 rules. For
team given "team NVARCHAR (255) NULL" is this the insanely lonh
name of the team? An identifier of some kind, like a URL? Their
location? Their mascot? Their total body weight? What?
5. I see you like SMALLDATETIME and MONEY to prevent portability. Good
SQL do not use proprietary data types. Do you know about MONEY math
errors and deprecation in SQL Server? Are you following GAAP rules? EU
rules?
6. You use FLOAT for a lot of things. First of all, floating point
math is only used in scientific systems for a good reason - rounding
errors and lack of precision would destroy the integrity of a
commercial system. For example, why is ssn_count a FLOAT and not an
integer? Give me an example.
7. You have an identifier called "part_surv_id FLOAT NULL" which is
a real nightmare. Identifiers have to be unique and therefore they
have to be an exact data type.
8. You have a "surv_strt_tm DATETIME", which looks like "survey
start time" but no ending time. The nature of time is that it is a
continuum, so you must have a half-open interval with an explicit or
implicit ending time.
9. Tables repeat columns over and over, but there is no RI among any of
them.
I strongly recommend that you start over and get more help than you are
going to find in a newsgroup. You clearly have no idea what a data
mdoel is, much less how to program in SQL (asking what DDL is kinda
like walking into a Mosque and asking "Hey, you on your knees, which
way is Mecca?" ).
Someone here with a lot of time on their hands might be able to get you
a kludge to make you think that you have been helped, but it will
simply destroy you in the long run.|||Celko,
First I appreciate you taking your valuable time (no tone here) to respond i
n
the detail that you did. I obviously am not as advanced as you. I took this
over as an ACCESS upgrade. I can not start over. I can't answer why about th
e
NULLs & Float or your other comments. I'm learning on the fly. I will not
waste anymore of your time, and look elsewhere for an answer.
--CELKO-- wrote:
>Yes, this is DDL. And **everything** in it is wrong. If I put this in
>one of my books, people would think I made it up
>1. Why is everything NULL-able? That means you cannot ever have a key.
> Tables by definition must have keys.
>2. What careful research lead to you to discover the amazing fact
>almost everything in your universe is not only NULL-able but also
>NVARCHAR (255)? In your world, I use the Diamond Sutra in Chinese for
>a ZIP code. And someone will. These tables will accumulate crap the
>minute they go into production.
>3. Why do you have data element names with spaces or special characters
>in them? My personal favorite is "# FLOAT NULL". Octothrope!?
>That is the sort of thing you in an old beginning programming book
>where they give you a list of illegal names. We allowed quoted
>identifiers in SQL so that future reserved words could be used in
>current code. It was never, never meant for doing display work in the
>database. What you have found is a great way to prevent readability,
>portability and maintainability of code!
>4. So many of the names are vague and violate ISO-11179 rules. For
>team given "team NVARCHAR (255) NULL" is this the insanely lonh
>name of the team? An identifier of some kind, like a URL? Their
>location? Their mascot? Their total body weight? What?
>5. I see you like SMALLDATETIME and MONEY to prevent portability. Good
>SQL do not use proprietary data types. Do you know about MONEY math
>errors and deprecation in SQL Server? Are you following GAAP rules? EU
>rules?
>6. You use FLOAT for a lot of things. First of all, floating point
>math is only used in scientific systems for a good reason - rounding
>errors and lack of precision would destroy the integrity of a
>commercial system. For example, why is ssn_count a FLOAT and not an
>integer? Give me an example.
>7. You have an identifier called "part_surv_id FLOAT NULL" which is
>a real nightmare. Identifiers have to be unique and therefore they
>have to be an exact data type.
>8. You have a "surv_strt_tm DATETIME", which looks like "survey
>start time" but no ending time. The nature of time is that it is a
>continuum, so you must have a half-open interval with an explicit or
>implicit ending time.
>9. Tables repeat columns over and over, but there is no RI among any of
>them.
>I strongly recommend that you start over and get more help than you are
>going to find in a newsgroup. You clearly have no idea what a data
>mdoel is, much less how to program in SQL (asking what DDL is kinda
>like walking into a Mosque and asking "Hey, you on your knees, which
>way is Mecca?" ).
>Someone here with a lot of time on their hands might be able to get you
>a kludge to make you think that you have been helped, but it will
>simply destroy you in the long run.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1
