How do i write Set based queries and avoid a cursor?

[CK]

How do I write a set based query? I have a groupSets table with fields
setId, idField, datasource, nameField, prefix, active
Data:
1,someIDfield, someTable, someField, pre1, 1
2,someotherIDfield, someTable, someotherField, pre2, 1
3,somethirdIDfield, someTable, somethirdField, pre3, 1
4,somefourthIDfield, someotherTable, somefourthField, pre4, 1

I need to generate records in another table by constructing queries from the
data in groups sets. I need to insert a record for each distinct result of
the query.
Example:
SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL

then I need to do an insert for each result of the above query

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, resultN)

--next reord from groupSets
SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS
NOT NULL
INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, resultN)
I basically want to do the same operation on each record in the groupSets
table. How can I accomplish this without a cursor? Any ideas?
Thanks for your help,
~ck

[--CELKO--]

"A Well Stated Problem is a Half Solved Problem". -- unknown IBM
expert

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. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

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. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

You have fundamental design problems in your vague narrative. You
still have not learned that a field is not anything whatsoever like a
column, that we don't use bit flags in SQL -- they are not scalars.
We don't mix data and metadata in a table. Your names reek of an EAV
design.

>I need to generate records [sic] in another table by constructing queries from the data in group sets. I need to insert a record [sic] for each distinct result of the query. <<

NO! This is not magnetic tapes or punch cards. We write a VIEW -- a
virtual table that is always correct when invoked. The VIEW also
needs a good table name -- not "Groups", which don't tell us what kind
of things is in that set.

>I basically want to do the same operation [what is the operation??] on each record [sic] in the GroupSets table. How can I accomplish this without a cursor? <<

I played with trying to guess at this operation, but decided that you
should be the one describing it. Without keys, this is not usable non-
code.

[Ed Murphy]

CK wrote:

How do I write a set based query? I have a groupSets table with fields
setId, idField, datasource, nameField, prefix, active
Data:
1,someIDfield, someTable, someField, pre1, 1
2,someotherIDfield, someTable, someotherField, pre2, 1
3,somethirdIDfield, someTable, somethirdField, pre3, 1
4,somefourthIDfield, someotherTable, somefourthField, pre4, 1

This smells fishy. groupSets should probably be replaced with a view,
but the proper way to build that view depends on the specific contents
desired (four dummy entries is not much to go on).

I need to generate records in another table by constructing queries from the
data in groups sets. I need to insert a record for each distinct result of
the query.
Example:
SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL

then I need to do an insert for each result of the above query

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, resultN)

--next reord from groupSets
SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS
NOT NULL
INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, resultN)
I basically want to do the same operation on each record in the groupSets
table. How can I accomplish this without a cursor? Any ideas?
Thanks for your help,

Your generic names are way too generic. Anonymize if you must, but
make it possible to distinguish between different levels.

Post the following:
* SQL statements to define input and output tables
* SQL statements to populate input tables with sample input data
* What specific output data you would want to end up with, given
this input data

[CK]

Ur an idiot man. Get lost. Your ego far exceeds your intelligence.

"--CELKO--" <jc*******@earthlink.netwrote in message
news:b9**********************************@d45g2000 hsc.googlegroups.com...

"A Well Stated Problem is a Half Solved Problem". -- unknown IBM
expert

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. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

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. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

You have fundamental design problems in your vague narrative. You
still have not learned that a field is not anything whatsoever like a
column, that we don't use bit flags in SQL -- they are not scalars.
We don't mix data and metadata in a table. Your names reek of an EAV
design.

>>I need to generate records [sic] in another table by constructing
queries from the data in group sets. I need to insert a record [sic] for
each distinct result of the query. <<

NO! This is not magnetic tapes or punch cards. We write a VIEW -- a
virtual table that is always correct when invoked. The VIEW also
needs a good table name -- not "Groups", which don't tell us what kind
of things is in that set.

>>I basically want to do the same operation [what is the operation??] on
each record [sic] in the GroupSets table. How can I accomplish this
without a cursor? <<

I played with trying to guess at this operation, but decided that you
should be the one describing it. Without keys, this is not usable non-
code.

[CK]

Never mind. I figured it out. It does what I need to do using Cursors. this
is a small amount of data and the cursor works quite well. Sounds like the
Set based queries are overkill in this situation. Just adds an unnecessary
degree of complexity. Thanks for your help.

"Ed Murphy" <em*******@socal.rr.comwrote in message
news:gb**********@registered.motzarella.org...

CK wrote:

>How do I write a set based query? I have a groupSets table with fields
setId, idField, datasource, nameField, prefix, active
Data:
1,someIDfield, someTable, someField, pre1, 1
2,someotherIDfield, someTable, someotherField, pre2, 1
3,somethirdIDfield, someTable, somethirdField, pre3, 1
4,somefourthIDfield, someotherTable, somefourthField, pre4, 1

This smells fishy. groupSets should probably be replaced with a view,
but the proper way to build that view depends on the specific contents
desired (four dummy entries is not much to go on).

>I need to generate records in another table by constructing queries from
the
data in groups sets. I need to insert a record for each distinct result
of
the query.
Example:
SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL

then I need to do an insert for each result of the above query

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, resultN)

--next reord from groupSets
SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS
NOT NULL
INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, resultN)
I basically want to do the same operation on each record in the groupSets
table. How can I accomplish this without a cursor? Any ideas?
Thanks for your help,

Your generic names are way too generic. Anonymize if you must, but
make it possible to distinguish between different levels.

Post the following:
* SQL statements to define input and output tables
* SQL statements to populate input tables with sample input data
* What specific output data you would want to end up with, given
this input data

[--CELKO--]

>Never mind. I figured it out. It does what I need to do using Cursors. this is a small amount of data and the cursor works quite well. Sounds like the Set based queries are overkill in this situation. Just adds an unnecessary degree of complexity. <<

"Against stupidity the gods themselves struggle in vain." - Die
Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

Gee, if you are so proud of it, then post it so that we poor unwashed
can see the concise, portable, scalable, robust code in your cursor.
Just based on 25+ years experience, I would bet that someone here
will find a set-based solution that is concise, portable, scalable and
robust.

[CK]

Well I could use your help and you just rip on me like I'm a dumbass. I am
here to learn. That's all.
I am not proud of my solution but it is a solution nonetheless. Here is what
I came up with.I really would like to know how to accomplish the same thing
using Set based queries. Thanks for any help you might provide. I apologize
and I really would like your help.

Cheers,
~ck
BEGIN
DECLARE @tgSetId varchar(5), @tgSetName varchar(50), @ds varchar(50),
@grEntIdField varchar(50), @grEntNameField varchar(50), @nmgPrfx varchar(50)
DECLARE @sql nvarchar(4000)
DECLARE @tgSetMatrix TABLE
(
EntityID int
,[Name] varchar(255)
,TrainingGroupName varchar(255)
,GroupingEntityID int
,TrainingGroupID int
,TrainingGroupSetID int
,Active bit
)

DECLARE trainingGroupSets_cursor CURSOR FOR
SELECT TrainingGroupSetID, TrainingGroupSetName, Datasource,
GroupingEntityIDField, GroupingEntityNameField, NamingPrefix
FROM TrainingGroupSet WHERE Active = 1

OPEN trainingGroupSets_cursor
FETCH NEXT FROM trainingGroupSets_cursor
INTO @tgSetId, @tgSetName, @ds, @grEntIdField, @grEntNameField, @nmgPrfx

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT e.' + @grEntIdField + ', e.Name, b.TrainingGroupName,
b.GroupingEntityID, b.TrainingGroupID, b.TrainingGroupSetID, b.Active FROM
( '
SET @sql = @sql + 'SELECT DISTINCT ' + @grEntIdField + ',''' + @nmgPrfx +
'''+'+ @grEntNameField + ' AS [Name] FROM ' + @ds + ' WHERE ' +
@grEntIDField + ' IS NOT NULL) e '
SET @sql = @sql + 'RIGHT OUTER JOIN (SELECT GroupingEntityID,
TrainingGroupName, TrainingGroupID, TrainingGRoupSetID, Active FROM
db1.dbo.TrainingGroup WHERE TrainingGroupSetID = ' + @tgSetId + ' '
SET @sql = @sql + ') b ON e.' + @grEntIdField + '= b.GroupingEntityID '
SET @sql = @sql + 'UNION SELECT e.' + @grEntIdField + ', e.Name,
b.TrainingGroupName, b.GroupingEntityID, b.TrainingGroupID,
b.TrainingGroupSetID, b.Active FROM ( '
SET @sql = @sql + 'SELECT DISTINCT ' + @grEntIdField + ',''' + @nmgPrfx +
'''+'+ @grEntNameField + ' AS [Name] FROM ' + @ds + ' WHERE ' +
@grEntIDField + ' IS NOT NULL) e '
SET @sql = @sql + 'LEFT OUTER JOIN (SELECT GroupingEntityID,
TrainingGroupName, TrainingGroupID, TrainingGRoupSetID, Active FROM
db1.dbo.TrainingGroup WHERE TrainingGroupSetID = ' + @tgSetId + ' '
SET @sql = @sql + ') b ON e.' + @grEntIdField + '= b.GroupingEntityID '
INSERT INTO @tgSetMatrix EXEC sp_executesql @sql

/* If any training group id is null, we need to create the training group */
IF EXISTS(SELECT 1 FROM @tgSetMatrix WHERE TrainingGroupID IS NULL)
BEGIN
INSERT INTO db1.dbo.TrainingGroup(TrainingGroupSetID, TrainingGroupName,
Active, GroupingEntityID)
SELECT @tgSetId, [Name], 1, EntityID FROM @tgSetMatrix WHERE TrainingGroupID
IS NULL
END

/* If there is a training group but the entityId is null, we need to
deactivate the training group */
IF EXISTS(SELECT 1 FROM @tgSetMatrix WHERE EntityID IS NULL)
BEGIN
UPDATE tg
SET Active = 0
FROM db1.dbo.TrainingGroup tg INNER JOIN @tgSetMatrix m ON
tg.TrainingGroupID = m.TrainingGroupID
WHERE m.EntityID IS NULL
END

/* If there is a training group and entityId is not null and trainingGroupID
is not null and active is false, we need to reactivate the training group */
IF EXISTS(SELECT 1 FROM @tgSetMatrix WHERE EntityID IS NOT NULL AND
TrainingGroupID IS NOT NULL AND Active = 0)
BEGIN
UPDATE tg
SET Active = 1
FROM db1.dbo.TrainingGroup tg INNER JOIN @tgSetMatrix m ON
tg.TrainingGroupID = m.TrainingGroupID
WHERE (m.EntityID IS NOT NULL) AND (m.TrainingGroupID IS NOT NULL) AND
(m.Active = 0)
END

DELETE FROM @tgSetMatrix

FETCH NEXT FROM trainingGroupSets_cursor
INTO @tgSetId, @tgSetName, @ds, @grEntIdField, @grEntNameField, @nmgPrfx
END

CLOSE trainingGroupSets_cursor
DEALLOCATE trainingGroupSets_cursor
END
"--CELKO--" <jc*******@earthlink.netwrote in message
news:1f**********************************@f63g2000 hsf.googlegroups.com...

>>Never mind. I figured it out. It does what I need to do using Cursors.
this is a small amount of data and the cursor works quite well. Sounds
like the Set based queries are overkill in this situation. Just adds an
unnecessary degree of complexity. <<

"Against stupidity the gods themselves struggle in vain." - Die
Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

Gee, if you are so proud of it, then post it so that we poor unwashed
can see the concise, portable, scalable, robust code in your cursor.
Just based on 25+ years experience, I would bet that someone here
will find a set-based solution that is concise, portable, scalable and
robust.

[Philipp Post]

As far as I can see there is a database design problem (attribute
splitting on the TrainingGroups, as each has its own table, whose name
can be found in the "TrainingGroupSetName" column) and this procedure
is the cleanup task to put them into one table properly. So good set
based queries will be possible after that cleanup, but most likely not
before.

Brgds

Philipp Post

[Andy M]

Celko--

You seem to put an awful lot of effort into being a jackass to people
asking honest questions. If you put just half of that energy into
being helpful, you may actually be able to be an asset to this group,
rather than being a distraction.

1) Your sarcasm is not funny.

2) In the real world, most developers, DBAs, and Tech Support are
working with applications that have been "in the wild" for a number of
years. When this is the case, you cannot realistically rename every
column, nor rewrite every procedure, function, DDL, DML to match
current ANSI/ISO standards. Additionally, most of us must conform to
certain coding standards, which also may predate certain ANSI/ISO
standards. Is it worse to use CamelCase (consistently), or to have a
hodgepodge smattering of CamelCase and underscore_syntax?

3) Your use of blanket statements such as "table variables and
temporary tables...are to be avoided" are clearly inaccurate over-
generalizations. These tools are provided as part of T-SQL because
they are a valuable tool for certain situations.

4) Field and column may have differing definitions, but please come
back to the real world where they are used interchangeably in the
course of every day conversion. This is a Google Group--not a text or
dissertation.

5) Perhaps you do not use flags, but perhaps you are forgetting that
they exist and are used by others. I won't even debate whether they
ought to be used or not--but thousands of developers use them, so
sometimes we just inherit them. If you have time to re-write every
piece of code to make it "ideal", then I would love to have your job.

6) Please avoid ridicule for column/variable naming conventions.
Developers & DBAs usually have coding standards to follow which
specify if a table should be singular or plural. Does it really
matter if your table is named "Address" or "Addresses"? In fact,
since this varies so greatly from database to database, its common
(albeit unfortunate) that there usually exists at least a couple of
tables or columns that do not match the standard for that database.
Welcome to the real world.

7) Why must everyone write in "Standard SQL"? Rarely do products
actually have to worry about their code being portable between
database platforms--and when they do, its virtually impossible to do.
Try writing 500 MB worth of SQL code, and write it so that it can work
on SQL Server, DB2, Oracle, Sybase, and Postgres. You could probably
either make it work, but it probably would not work well (ie,
performance & maintainability).

8) When did dynamic code become so horrible? Again, this is a totally
valid programming tool. Having dynamic SQL has nothing to do with SQL
injection. You need to protect against SQL injection in your front-
end user interface. A poorly written front end can always allow for
SQL Injection.

9) Perhaps "ds_", "tg_", etc ARE naming conventions. CK seems to use
them fairly consistently. Just because you don't know what the naming
convention is, doesn't mean there is no naming convention.

10) If I write a SELECT statement that contains data which will be
presented to the user as a matrix.... what do you propose I call that
in my code? I think I would probably refer to it as a Matrix. Just
because T-SQL doesn't have a concept of "Matrices" does not make it an
invalid variable name. T-SQL does not have a concept of "gymnasts"
Does that mean I cannot have a temp table called "olympic_gymnasts"?

11) The purpose of these Groups is to be HELPFUL. The purpose is not
to pwn as many SQL greenhorns as possible.

CELKO --- I'm not sure if you simply enjoy belittling others, or if
you think you are doing some great service to the SQL language. Be
assured you are rarely of help. You tell everyone the same thing:
"You're stupid. Your code sucks. This is not ANSI/ISO standard. I
need the full table to help you. I need sample data to help you. I
can't help you because your code sucks. Your database design sucks.
Rewrite your database schema then come back to me." Please do us all
a favor--contribute in a constructive way, or contribute less.

[Tony Mountifield]

In article <cd**********************************@z66g2000hsc. googlegroups.com>,
--CELKO-- <jc*******@earthlink.netwrote:

0) Would mind commenting on what was actually posted instead of
telling me that you don't like my style?

Winston Churchill once said "When you have to kill a man, it costs nothing
to be polite."

The same is true when you choose to assist people in newsgroups, even if
you are an expert (which, owning two of your books, I know you are).

Cheers
Tony
--
Tony Mountifield
Work: to**@softins.co.uk - http://www.softins.co.uk
Play: to**@mountifield.org - http://tony.mountifield.org

[--CELKO--]

>Winston Churchill once said "When you have to kill a man, it costs nothing to be polite." The same is true when you choose to assist people in newsgroups, even if you are an expert (which, owning two of your books, I know you are). <<

I love Churchill quotes! My favorites are "I will be sober in the
morning" and "Madam, if I were your husband, I would drink it."

Have you seen KITCHEN NIGHTMARES on television? Gordon Ramsey arrives
with basic restaurant management knowledge and saves the day. Forget
the fancy cooking stuff he can do. It is the basics that make or
break things. Ditto RDBMS.

If you play with "kludge hunting kids" who ask stupid questions as if
they have the same status as a real SQL programmer, fine. I am after
the real students who want to learn.

[DA Morgan]

--CELKO-- wrote:

<edited for brevity>

Now, to get back on topic, the poster failed to give DDL, so an exact
answer is not possible.

Well said Joe. It is always a pleasure to read your comments.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[CK]

Thank you Andy for sticking up. I am sure CELKO is an expert but 95% of us
out here are not.

"Andy M" <an*********@gmail.comwrote in message
news:6b**********************************@a70g2000 hsh.googlegroups.com...

Celko--

You seem to put an awful lot of effort into being a jackass to people
asking honest questions. If you put just half of that energy into
being helpful, you may actually be able to be an asset to this group,
rather than being a distraction.

1) Your sarcasm is not funny.

2) In the real world, most developers, DBAs, and Tech Support are
working with applications that have been "in the wild" for a number of
years. When this is the case, you cannot realistically rename every
column, nor rewrite every procedure, function, DDL, DML to match
current ANSI/ISO standards. Additionally, most of us must conform to
certain coding standards, which also may predate certain ANSI/ISO
standards. Is it worse to use CamelCase (consistently), or to have a
hodgepodge smattering of CamelCase and underscore_syntax?

3) Your use of blanket statements such as "table variables and
temporary tables...are to be avoided" are clearly inaccurate over-
generalizations. These tools are provided as part of T-SQL because
they are a valuable tool for certain situations.

4) Field and column may have differing definitions, but please come
back to the real world where they are used interchangeably in the
course of every day conversion. This is a Google Group--not a text or
dissertation.

5) Perhaps you do not use flags, but perhaps you are forgetting that
they exist and are used by others. I won't even debate whether they
ought to be used or not--but thousands of developers use them, so
sometimes we just inherit them. If you have time to re-write every
piece of code to make it "ideal", then I would love to have your job.

6) Please avoid ridicule for column/variable naming conventions.
Developers & DBAs usually have coding standards to follow which
specify if a table should be singular or plural. Does it really
matter if your table is named "Address" or "Addresses"? In fact,
since this varies so greatly from database to database, its common
(albeit unfortunate) that there usually exists at least a couple of
tables or columns that do not match the standard for that database.
Welcome to the real world.

7) Why must everyone write in "Standard SQL"? Rarely do products
actually have to worry about their code being portable between
database platforms--and when they do, its virtually impossible to do.
Try writing 500 MB worth of SQL code, and write it so that it can work
on SQL Server, DB2, Oracle, Sybase, and Postgres. You could probably
either make it work, but it probably would not work well (ie,
performance & maintainability).

8) When did dynamic code become so horrible? Again, this is a totally
valid programming tool. Having dynamic SQL has nothing to do with SQL
injection. You need to protect against SQL injection in your front-
end user interface. A poorly written front end can always allow for
SQL Injection.

9) Perhaps "ds_", "tg_", etc ARE naming conventions. CK seems to use
them fairly consistently. Just because you don't know what the naming
convention is, doesn't mean there is no naming convention.

10) If I write a SELECT statement that contains data which will be
presented to the user as a matrix.... what do you propose I call that
in my code? I think I would probably refer to it as a Matrix. Just
because T-SQL doesn't have a concept of "Matrices" does not make it an
invalid variable name. T-SQL does not have a concept of "gymnasts"
Does that mean I cannot have a temp table called "olympic_gymnasts"?

11) The purpose of these Groups is to be HELPFUL. The purpose is not
to pwn as many SQL greenhorns as possible.

CELKO --- I'm not sure if you simply enjoy belittling others, or if
you think you are doing some great service to the SQL language. Be
assured you are rarely of help. You tell everyone the same thing:
"You're stupid. Your code sucks. This is not ANSI/ISO standard. I
need the full table to help you. I need sample data to help you. I
can't help you because your code sucks. Your database design sucks.
Rewrite your database schema then come back to me." Please do us all
a favor--contribute in a constructive way, or contribute less.

[CK]

You have fundamental design problems in your vague narrative. You

still have not learned that a field is not anything whatsoever like a
column, that we don't use bit flags in SQL -- they are not scalars.
We don't mix data and metadata in a table. Your names reek of an EAV
design.

Yes it is indeed designed using the Entity-Attribute-Value model. We have to
create training groups based on data provided in a datafeed. For examples if
someone comes through with a new departmentId and we don't have a training
group for that department yet, then we need to create a training group for
that department.

[CK]

5) Perhaps you do not use flags, but perhaps you are forgetting that
they exist and are used by others. I won't even debate whether they
ought to be used or not--but thousands of developers use them, so
sometimes we just inherit them. If you have time to re-write every
piece of code to make it "ideal", then I would love to have your job.

How do you avoid using flags? If you have a field called "Active" and its
values should be 'Yes or No'. What else would you use? Some type of int
field and link it to an ActiveID table? or something?

[Erland Sommarskog]

CK (c_**********@hotmail.com) writes:

Thank you Andy for sticking up. I am sure CELKO is an expert but 95% of us
out here are not.

Celko is certainly an expert in being snotty. Else I am not so sure.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Mon, 29 Sep 2008 12:47:06 -0700, CK wrote:

>How do you avoid using flags? If you have a field called "Active" and its
values should be 'Yes or No'. What else would you use? Some type of int
field and link it to an ActiveID table? or something?

Hi CK,

Based on previous replies to similar questions, I expect Joe Celko to
tell you to use a column active_date that stores the date (or even date
and time) that the active status was achieved, and that is NULL if
whatever is modeled in the table is not active. (And he'll also blast
you for using "field" instead of "column").

Of course, Joe will not care whether the business is actually interested
at all in what time the widget became active. Nor will he care that it
might involve lots of extra work for the data entry department, maybe
even severe changes in some business process, to track and enter this
information (that the business doesn't even need). In fact, he'll also
be blind to the fact that this will without any doubt result in fake
dates being stored, as that's what always happpens when some clowns from
the IT department who are compleetely out of touch with the real work on
the business floor dictate the storage of some uninteresting attribute.

Joe won't care. He'll finish his job, cash his cheque and walk to the
next customer, leaving others to sort out the mess while updating his
resume with yet another "improved data quality and audit trail".

And when a few years down the road management invents a new business
requirement that does involve tracking when the active state was
reached, the poor chap tasked with the database will see this column,
will of course assume that it holds the data required, and will then
produce reports based on incorrect data. Wouldn't you much rather have a
design with just the IsActive column and no ActiveDate, so that you know
to tell upper management that this new requirement involves changes to
the database and can only be tracked for new widgets?
Oh, and for the record (pun not intended): I would call the column
"IsActive", use data type CHAR(1), and add a constraint CHECK (IsActive
IN ('Y','N')).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Eric Isaacs]

CURRENT_TIMESTAMP is more portable than GETDATE() and accomplishes the
same thing. My code isn't going anywhere, so I use them pretty much
interchangeably, but I lead toward CURRENT_TIMESTAMP. I also have
faith that if my code IS every transferred to another platform by
someone else, that person will have a good enough grasp on "Find and
Replace" to be able to handle the problem. :o)

-Eric Isaacs

[--CELKO--]

>I'm sure Celko has something to say about using GETDATE() preventing your code from being portable, and therefore you should never use it. *;) <<

You've got CURRENT_TIMESTAMP now, so you have no excuse for bad coding
practices anymore. Shame on you!!

I also make a distinction among Standard SQL, Portable SQL and
Proprietary SQL. Standard is always portable. But portable code can
be converted into Standard code with a *simple* text edit -- semantics
versus syntax. Proprietary code has different semantics, and probably
different -- but not always different -- syntax.

[--CELKO--]

>Based on previous replies to similar questions, I expect Joe Celko to tell you to use a column active_date that stores the date (or even date and time) that the active status was achieved, and that is NULL if whatever is modeled in the table is not active. <<

No, based on previous replies, you should have said I would advise a
pair of columns to show duration of that status, more like this:

CREATE TABLE Foobar
(..
foo_status CHAR(5) NOT NULL
CHECK(foo_status IN (..)),
start_date DATE NOT NULL,
end_date DATE, -- null is current

>(And he'll also blast you for using "field" instead of "column"). <<

Yes. This is important if you are ever going to think in sets and not
sequential files.

>Of course, Joe will not care whether the business is actually interested at all in what time the widget became active. <<

Can you think of a business that would not have such an interest when
it involves a product or service they are libel for? Business is very
temporal.

>Nor will he care that it might involve lots of extra work for the data entry department, maybe even severe changes in some business process, to track and enter this information (that the business doesn't even need). <<

This usually involves adding a DEFAULT CURRENT_TIMESTAMP to a table
declaration or a line in a stored procedure.

>In fact, he'll also be blind to the fact that this will without any doubt result in fake dates being stored, as that's what always happens when some clowns from the IT department who are completely out of touch with the real work on the business floor dictate the storage of some uninteresting attribute. <<

The time of an event for which the company is libel might not be
considered an "uninteresting attribute" by management or the lawyers.
Why would "fake dates" be stored? That is not usually an IT problem;
it mostly comes from crooked management messing with the books.
Remember Enron?

>And when a few years down the road management invents a new business requirement that does involve tracking when the active state was reached, the poor chap tasked with the database will see this column, will of course assume that it holds the data required, and will then produce reports based on incorrect data. <<

1) Why do you assume that if you record a fact (especially when the DB
does it for you with CURRENT_DATE), it has to be wrong? Weird.
2) "Errors using inadequate data are much less than those using no
data at all." --Charles Babbage

>Wouldn't you much rather have a design with just the is_active column and no active_date, so that you know to tell upper management that this new requirement involves changes to the database and can only be tracked for new widgets? <<

No, hell no! In the real world, new requirements almost always have a
temporal component. See another posting of mine about the drinking
age changing in the US. Before the laws changed, Sally was a
perfectly bartender when I hired her at 18. The next week, I have to
take her off the bar and put a wrist band on her (to mark underage
servers). If I "flip her flag" without the history, I have destroyed
information -- was I engaged in criminal activity with a minor or did
I overpay her for the bartending she could not have been doing?

>Oh, and for the record (pun not intended [oh sure it was! It was too good miss :)]): I would call the column "is_active", use data type CHAR(1), and add a constraint CHECK (is_active IN ('Y','N')). <<

The "<verb>_<adjective>" naming convention is not ISO-11179, but at
least it clearly shows that the data element is not scalar as required
by RDBMS. It lkooks like OO programming. The flag does not measure
the quality or quantity of an attribute, but answers a meta-data
question about existence. Instead of a specific "how much", "how
many", etc., we have a generalized "is" meta-question.

A better name in RDBMS might be "active_flag CHAR(1) DEFAULT 'Y' NOT
NULL
CHECK (active_flag IN ('Y','N'))". But "active" is a bit vague --
might want a "something_active_flag" for this piece of meta-data if
you are going to have a bad model.

[Hugo Kornelis]

On Wed, 1 Oct 2008 14:10:39 -0700 (PDT), --CELKO-- wrote:

>>Based on previous replies to similar questions, I expect Joe Celko to tell you to use a column active_date that stores the date (or even date and time) that the active status was achieved, and that is NULL if whatever is modeled in the table is not active. <<

No, based on previous replies, you should have said I would advise a
pair of columns to show duration of that status, more like this:

CREATE TABLE Foobar
(..
foo_status CHAR(5) NOT NULL
CHECK(foo_status IN (..)),
start_date DATE NOT NULL,
end_date DATE, -- null is current

Hi Joe,

True. You've got me there!

>>(And he'll also blast you for using "field" instead of "column"). <<

Yes. This is important if you are ever going to think in sets and not
sequential files.

Trust me on this, Joe. It *IS* possible to use the terms "field" and
"record" and still think in sets.

"That which we call a rose, by amny other name would smell as sweet!"
-- Shakespeare, "Romeo and Juliet", 1594

>>Of course, Joe will not care whether the business is actually interested at all in what time the widget became active. <<

Can you think of a business that would not have such an interest when
it involves a product or service they are libel for? Business is very
temporal.

Who says that the original poster was modelling a product or service
they are libel for? Assumptions, my friend! They (together with good
intentions) pave the path to hell!

There are various situation where all information needed is whether some
FooBar is or is not active. To give just one example - when you have to
dispatch jobs to workers, you need a list of idle workers. You do not
need to know any history of when they were idle and when they were
active - you just need to send the task to an idle worker.

>>Nor will he care that it might involve lots of extra work for the data entry department, maybe even severe changes in some business process, to track and enter this information (that the business doesn't even need). <<

This usually involves adding a DEFAULT CURRENT_TIMESTAMP to a table
declaration or a line in a stored procedure.

That does not help one bit when someone flips the "active" status. A
DEFAULT doesn't do anything when you run an UPDATE.

>>In fact, he'll also be blind to the fact that this will without any doubt result in fake dates being stored, as that's what always happens when some clowns from the IT department who are completely out of touch with the real work on the business floor dictate the storage of some uninteresting attribute. <<

The time of an event for which the company is libel might not be
considered an "uninteresting attribute" by management or the lawyers.

I think we already covered that. If the company were libel for the time
of the event, the table would already have been designed with a column
to store that time. Since it's not, we can safely assume that the
company isn't libel for said time (or, if you prefer to be cynical, that
the managers that pay the DBA's bills think they're not).

>Why would "fake dates" be stored?

Because that's what happens when idiots from the IT department add
mandatory attributes that the business never asked for. The data entry
people are presented a screen that demands some input that they can't
supply, so they just enter something and move on - after all, they got
their work to do!

>>And when a few years down the road management invents a new business requirement that does involve tracking when the active state was reached, the poor chap tasked with the database will see this column, will of course assume that it holds the data required, and will then produce reports based on incorrect data. <<

1) Why do you assume that if you record a fact (especially when the DB
does it for you with CURRENT_DATE), it has to be wrong? Weird.

So you've never been in a situation where changes were entered in
advance, or after the fact?

>2) "Errors using inadequate data are much less than those using no
data at all." --Charles Babbage

If Charles said that, he was wrong.

I think it'll be 20 years ago now that I took over maintenance for an
existing application (PL/1 on a mainframe, using an IMS/DB database;
aaah, the memories!). After a few days of studying the documentation and
comparing it to the actual source code, I knew of a way to greatly
improve the quality of the documentation in just a few minutes: I binned
all of the documentation! It was seriously outdated. The code had moved
on but the docs had not. If I had kept the documentation, I would have
wasted lots of my time, and run the risk of making incorrect changes by
relying on the outdated documentation. Binning the documentation ensured
that both I and all my successors should use the source as the only
trustworthy source of information, until finally budget was approved to
re-document the thing (which, of course, never happened).

>>Wouldn't you much rather have a design with just the is_active column and no active_date, so that you know to tell upper management that this new requirement involves changes to the database and can only be tracked for new widgets? <<

No, hell no! In the real world, new requirements almost always have a
temporal component. See another posting of mine about the drinking
age changing in the US. Before the laws changed, Sally was a
perfectly bartender when I hired her at 18. The next week, I have to
take her off the bar and put a wrist band on her (to mark underage
servers). If I "flip her flag" without the history, I have destroyed
information -- was I engaged in criminal activity with a minor or did
I overpay her for the bartending she could not have been doing?

You get to pick your examples, I get to pick mine.

Imagine a DB app that calls a webservice to check address against postal
code (I believe you call that ZIP code). Since webservices are not 100%
reliable, a list of available webservices is kept in a table. When an
address has to be checked, one of them is called. If the call results in
an error or timeout, the webservice is marked as not in use, and another
webservice is called instead. Once or twice a month, an employee will
check webservices marked "not in use", find out if the problem is
permanent or temporal and mark the service as "in use" once a temporal
problem is resolved, or remove it completely if the problem is
permanent. Management decides not to store when exactly these services
started or stopped being in use, as they are just using some service
provided for free by some third parties.

Three months later, it turns out that one of the services used isn't
free after all, but requires a monthly fee. If I had followed your
advice, I might see a status of "in use" from Jan 1st until March 13th,
"not in use" from March 13th until March 16th, and "in use" from March
16th until now. That's almost three full months use - quite a hefty
bill. But in reality, the service was never called until March 13th,
when some other service failed. This service failed too (turns out one
parameter was misconfigured), so it was also given status "not in use".
Three days later, the parameter was repaired and the status was set to
"in use" once more, after which we actually started using it.

With your table, management would probably get a report saying that the
service has been used for three months, with a three day outage.
Grudgingly, they'd pay the bill for three months (with a small discount
for the outage).
With my table, I'd have to report to management that there is no easy
way to figure out the actual use of that particular service. Not very
good, but at least better than the lies you'd tell them. And I'd then
have to try to dig up the information on the actual use in some other
way. Maybe we'd eventually even end up examining the logs from the
service provider. But we would not pay for the months that the service
was not actually used.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[DA Morgan]

Hugo Kornelis wrote:

On Wed, 1 Oct 2008 14:10:39 -0700 (PDT), --CELKO-- wrote:

>>>Based on previous replies to similar questions, I expect Joe Celko to tell you to use a column active_date that stores the date (or even date and time) that the active status was achieved, and that is NULL if whatever is modeled in the table is not active. <<

No, based on previous replies, you should have said I would advise a
pair of columns to show duration of that status, more like this:

CREATE TABLE Foobar
(..
foo_status CHAR(5) NOT NULL
CHECK(foo_status IN (..)),
start_date DATE NOT NULL,
end_date DATE, -- null is current

Hi Joe,

True. You've got me there!

>>>(And he'll also blast you for using "field" instead of "column"). <<

Yes. This is important if you are ever going to think in sets and not
sequential files.

Trust me on this, Joe. It *IS* possible to use the terms "field" and
"record" and still think in sets.

"That which we call a rose, by amny other name would smell as sweet!"
-- Shakespeare, "Romeo and Juliet", 1594

And thus wallow in willful ignorance?

You are, of course, technically correct, you can call a rose a sewer
if you wish. But likely you will not find too many people willing to
tour your sewage garden. I would suggest that if you want to be a poet
you change your profession. In ours it matters.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[Hugo Kornelis]

On Sat, 04 Oct 2008 14:02:48 -0700, DA Morgan wrote:

>Hugo Kornelis wrote:

(snip)

>Trust me on this, Joe. It *IS* possible to use the terms "field" and
"record" and still think in sets.

"That which we call a rose, by amny other name would smell as sweet!"
-- Shakespeare, "Romeo and Juliet", 1594

And thus wallow in willful ignorance?

You are, of course, technically correct, you can call a rose a sewer
if you wish. But likely you will not find too many people willing to
tour your sewage garden. I would suggest that if you want to be a poet
you change your profession. In ours it matters.

Hi DA,

If you use Google groups to check some (or even all) of my posts, you'll
note that I do always call rows rows, and columns columns. But, unlike
Joe, I do see that many people in the current world use the terms field
and record for these same things. It is clear, from their messages, that
they MEAN "row" and "column", and that they have probably never worked
with an ancient application that reads records from a sequential files
and uses various redefines to define the fields.

Times change. Language evolves. Or to throw in another ancient quote:
Panta rhei. We can attempt to fight it, but we can't stop it. I do try
to stop the change of the meaning of these two words, by gently using
the correct terms even if the question was using the other words. But
(unlike Joe!) I will not slap someone in the face for using these terms,
since I am fully aware that for most people, the words field and record
no longer have their original meaning, but are instead synonyms for
column and row.

(Oh, and by the way, The American Heritage Dictionary of the English
Language, Fourth Edition, has this as one of its definitions for field:
"An element of a database record in which one piece of information is
stored", and this for record: "_Computer Science_ A collection of
related, often adjacent items of data, treated as a unit").

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[DA Morgan]

Hugo Kornelis wrote:

On Sat, 04 Oct 2008 14:02:48 -0700, DA Morgan wrote:

>Hugo Kornelis wrote:

(snip)

>>Trust me on this, Joe. It *IS* possible to use the terms "field" and
"record" and still think in sets.

"That which we call a rose, by amny other name would smell as sweet!"
-- Shakespeare, "Romeo and Juliet", 1594

And thus wallow in willful ignorance?

You are, of course, technically correct, you can call a rose a sewer
if you wish. But likely you will not find too many people willing to
tour your sewage garden. I would suggest that if you want to be a poet
you change your profession. In ours it matters.

Hi DA,

If you use Google groups to check some (or even all) of my posts, you'll
note that I do always call rows rows, and columns columns. But, unlike
Joe, I do see that many people in the current world use the terms field
and record for these same things.

You are correct they do. It would also be correct to state that there
are people that rob banks, steal chickens, and don't know how to merge
into traffic. Each and every one of them should be taken to the
proverbial woodshed too.

We are the only "profession" where sloppiness is accepted. You won't
find physicians, surgeons, pharmacists, attorneys, accountants, or
engineers tolerating such nonsense. I am in full agreement with Joe:
Se need to start acting like we are worth our salaries.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[Erland Sommarskog]

DA Morgan (da******@psoug.org) writes:

You are correct they do. It would also be correct to state that there
are people that rob banks, steal chickens, and don't know how to merge
into traffic. Each and every one of them should be taken to the
proverbial woodshed too.

Obviously you are seriously in need of a holiday if you equate using
records and fields in place of rows and columns to robbing banks.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DA Morgan]

Erland Sommarskog wrote:

DA Morgan (da******@psoug.org) writes:

>You are correct they do. It would also be correct to state that there
are people that rob banks, steal chickens, and don't know how to merge
into traffic. Each and every one of them should be taken to the
proverbial woodshed too.

Obviously you are seriously in need of a holiday if you equate using
records and fields in place of rows and columns to robbing banks.

Of course I don't equate them. But equally true I don't find value
in the argument that "other people do it too" which was the excuse
made: It is a poor one and I was highlighting its weakness.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
da******@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Hugo Kornelis]

On Sun, 05 Oct 2008 12:35:39 -0700, DA Morgan wrote:

>Erland Sommarskog wrote:

>DA Morgan (da******@psoug.org) writes:

>>You are correct they do. It would also be correct to state that there
are people that rob banks, steal chickens, and don't know how to merge
into traffic. Each and every one of them should be taken to the
proverbial woodshed too.

Obviously you are seriously in need of a holiday if you equate using
records and fields in place of rows and columns to robbing banks.

Of course I don't equate them. But equally true I don't find value
in the argument that "other people do it too" which was the excuse
made: It is a poor one and I was highlighting its weakness.

Hi DA,

You obviously misunderstood me. The excuse made was not that "other
people do it to". The excuse (if you insist on calling it that) was
rather that it's a long-proven fact that language evolves over time, and
that correct use of language is ultimately decided by the people using
it.

I also noticed that you did not in any way address my finding that the
American Heritage (as far as I, not being a native English speaker, can
tell, an authorative English dictionary) includes meanings for both
record and field that you, me, and Joe would personally rather see only
attached to row and record. Can you really keep clinging to the idea
that records and fields are reserved for non-relational storage if even
the dictionary says otherwise?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[DA Morgan]

Hugo Kornelis wrote:

On Sun, 05 Oct 2008 12:35:39 -0700, DA Morgan wrote:

>Erland Sommarskog wrote:

>>DA Morgan (da******@psoug.org) writes:
You are correct they do. It would also be correct to state that there
are people that rob banks, steal chickens, and don't know how to merge
into traffic. Each and every one of them should be taken to the
proverbial woodshed too.

Obviously you are seriously in need of a holiday if you equate using
records and fields in place of rows and columns to robbing banks.

Of course I don't equate them. But equally true I don't find value
in the argument that "other people do it too" which was the excuse
made: It is a poor one and I was highlighting its weakness.

Hi DA,

You obviously misunderstood me. The excuse made was not that "other
people do it to". The excuse (if you insist on calling it that) was
rather that it's a long-proven fact that language evolves over time, and
that correct use of language is ultimately decided by the people using
it.

Absolutely correct that language evolves over time. But this isn't about
language. This is about verbiage coded into the ANSI standard. This is
about keywords in the product documentation. This is about being able to
communicate with a world-wide audience with clarity.

If you want to say "Whatsup" or "Kool" knock yourself out. But to
claim that "table" communicates clearly when the question might
relate to whether it is a "heap table" or a "working (temp) table"
is quite another matter.

I am with Joe on this one and you'd not pass my class if you didn't
know the difference between a row and a record, a column and a field.

Does it matter? Look at all of the failed and terrible implementations
of software that plague our industry: Yes it matters.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[--CELKO--]

>Trust me on this, Joe. It *IS* possible to use the terms "field" and "record" and still think in sets.

"That which we call a rose, by any other name would smell as sweet!"
-- Shakespeare, "Romeo and Juliet", 1594 <<

Is this an argument for doing math with Roman Numerals, since they are
the same Integers? Words are mind tools. Common people also confuse
an engine and a motor, meteors, meteoroids and meteorites, guns and
pistols. ships and boats, etc.

But it does make a difference to the trained professional whose skill
domain makes use of exact words.

What I have found is that if you can get the concepts into a student
and cut him loose from his old mindset, he learns much faster, much
better. Let me do another quote I like:

It was beautiful, complex and wrong. In 150 AD, Ptolemy of Alexandria
published his theory of epicycles--the idea that the moon, the sun and
the planets moved in circles which were moving in circles which were
moving in circles around the Earth. This theory explained the motion
of celestial objects to an astonishing degree of precision. It was,
however, what computer programmers call a kludge: a dirty, inelegant
solution. Some 1,500 years later, Johannes Kepler, a German
astronomer, replaced the whole complex edifice with three simple laws.
-- The Economist

>>Binning the documentation ensured that both I and all my successors should use the source as the only trustworthy source of information, until finally budget was approved to re-document the thing (which, of course, never happened). <<

There is another great Babbage quote which I cannot find right now,
but the flavor of it that verification is an inseparable part of a
database (he did not have that word, of course). The reason he had
designed his difference engine was provide the British Navy with
accurate navigation tables. The tables in those days were full of
errors and even the errata had errors.

The same principle applies to documentation and your predecessors had
failed completely to maintain as a part of the system. Nothing should
have been allowed to modify the system without changing the
documents. And management who did not make that part of the system
failed their job.

[Erland Sommarskog]

DA Morgan (da******@psoug.org) writes:

Absolutely correct that language evolves over time. But this isn't about
language. This is about verbiage coded into the ANSI standard. This is
about keywords in the product documentation. This is about being able to
communicate with a world-wide audience with clarity.

And therefore you must be able to use "fields" and "records" everyonce
in a while. After all, these terms are usually more clear and precise
than "rows" and "columns". (Is that a "column" in a database table, or
is the author referring to a column in the table on the next page of the
book?)

Even Joe knows that. As testified by the fact that he has been heard to
use "record" (or if it was "field") in his presentations.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Andy M]

On Oct 4, 8:11*pm, DA Morgan <[e-mail]:

We are the only "profession" where sloppiness is accepted. You won't
find physicians, surgeons, pharmacists, attorneys, accountants, or
engineers tolerating such nonsense. I am in full agreement with Joe:
Se need to start acting like we are worth our salaries.

I am quite sure that Garbage Collectors, Janitors, Landscapers, and a
number of others tolerate a certain level of "sloppiness", as you term
it. And perhaps you've heard the term "Good 'nuff for gov'ment work."

The key to language is that one uses a term that is understood by the
audience that one is speaking to. If I say "field" when I mean
"column," then its quite OK, so long as folks understand me. I am
quite sure many doctors have told patients that they have a "cold."
Ah ha! Sloppiness!! They ought to be telling their patients that
they have "acute viral nasopharyngitis!" The patient is not cold at
all--they might even have a fever. From my work in medical software,
I am quite confident that the official medical record shows a
diagnosis of a "cold." Is this sloppiness? Not at all. Everyone
knows exactly what is meant.

For persons who are quite so dead-set on accuracy of language, and
consistency, it is interesting to see so many typos!

On Oct 1, 5:10 pm, --CELKO-- <[e-mail]:

Can you think of a business that would not have such an interest when
it involves a product or service they are *libel* [sic] for? Business isvery
temporal.

libel - v - print slanderous statements against; "The newspaper was
accused of libeling him"
liable - n - subject to legal action; "liable to criminal charges"

On Oct 1, 5:10 pm, --CELKO-- <[e-mail]:

The "<verb>_<adjective>" naming convention is not ISO-11179, but at
least it clearly shows that the data element is not scalar as required
by RDBMS. It *lkooks* [sic] like OO programming. The flag does not measure
the quality or quantity of an attribute, but answers a meta-data
question about existence. Instead of a specific "how much", "how
many", etc., we have a generalized "is" meta-question.

lkooks - unknown. perhaps an attempt to spell "cooks"?

On Oct 4, 8:11 pm, DA Morgan <[e-mail]:

*Se* [sic] need to start acting like we are worth our salaries.

se - abbr - selenium: a toxic nonmetallic element related to sulfur
and tellurium
SE - abbr - South East
..se - the Internet country code top-level domain (ccTLD) for Sweden
se - n - an ancient Chinese plucked zither (string instrument)
se - French - Third-person reflexive pronoun

[Andy M]

On Oct 4, 5:02*pm, DA Morgan <[e-mail]:

You are, of course, technically correct, you can call a rose a sewer
if you wish. But likely you will not find too many people willing to
tour your sewage garden. I would suggest that if you want to be a poet
you change your profession. In ours it matters.

I am generally not very excited about sewer gardens, but I do enjoy
several other types of gardens:

Flower Gardens
Perennial Gardens

[Hugo Kornelis]

On Mon, 06 Oct 2008 10:23:15 -0700, DA Morgan wrote:

>Hugo Kornelis wrote:

>On Sun, 05 Oct 2008 12:35:39 -0700, DA Morgan wrote:

>>Erland Sommarskog wrote:
DA Morgan (da******@psoug.org) writes:
You are correct they do. It would also be correct to state that there
are people that rob banks, steal chickens, and don't know how to merge
into traffic. Each and every one of them should be taken to the
proverbial woodshed too.

Obviously you are seriously in need of a holiday if you equate using
records and fields in place of rows and columns to robbing banks.
Of course I don't equate them. But equally true I don't find value
in the argument that "other people do it too" which was the excuse
made: It is a poor one and I was highlighting its weakness.

Hi DA,

You obviously misunderstood me. The excuse made was not that "other
people do it to". The excuse (if you insist on calling it that) was
rather that it's a long-proven fact that language evolves over time, and
that correct use of language is ultimately decided by the people using
it.

Absolutely correct that language evolves over time. But this isn't about
language. This is about verbiage coded into the ANSI standard. This is
about keywords in the product documentation. This is about being able to
communicate with a world-wide audience with clarity.

Hi DA,

If it's about communicating with the world-wide audience, then why not
rely on the dictionary to set the standards rather than cling on the
verbiage in a standard that is only used by a fraction of the total
world population?

Also, "field" IS defined in the ANSI standard. See ISO/IEC 9075-1:2003
(E), 4.4.5.3 on page 17 (in the late draft that I have; the newest
version I found for free on the internet):

4.4.5.3 Fields
A field is a (field name, data type) pair. A value of a field is a value
of its data type.

The term is further used heavily throughout the standard.

I do agree that "record" is not defined in the ANSI standard.

But alll the above is irrelevant. Whether or not row, column, field, and
record are defined in any standard - the world has moved on; the meaning
of the words has evolved. If I have to make a guesstimate, I'd say that
over 70% of all people professionally involved with database programming
consider the terms field and column and the terms row and record to be
100% synonyms. Fighting this just because it comes from the ultimate
evil named MS-Access is (a) useless, and (b) a fine wayy to show oneself
off as a dinosaur.

>If you want to say "Whatsup" or "Kool" knock yourself out. But to
claim that "table" communicates clearly when the question might
relate to whether it is a "heap table" or a "working (temp) table"
is quite another matter.

I am with Joe on this one and you'd not pass my class if you didn't
know the difference between a row and a record, a column and a field.

There's a huge gap between "not knowing the difference" between terms,
or "occasionaly using the wrong term" or even "using the wrong term on
purpose since that will resonate better with the audience and the
difference is not really relevant in the context".

When modeling an EAR diagram, do you also religously stress the word
type in the terms "entity type", "attrbiute type", and "relationship
type" all the time, or do you just go with the flow and use the terms
"entity", "attribute", and "relationship" even though you are aware that
these terms actually describe a single instance rather than the
generalization?

>Does it matter? Look at all of the failed and terrible implementations
of software that plague our industry: Yes it matters.

Ah, and those implementation would all have been great if noone had ever
used the words "field" or "record" in a relational context?

If that's you analysis of why projects fail, than you'd better prepare
yourself for many more failed implementations.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Hugo Kornelis]

On Mon, 6 Oct 2008 10:35:52 -0700 (PDT), --CELKO-- wrote:

>>Trust me on this, Joe. It *IS* possible to use the terms "field" and "record" and still think in sets.

"That which we call a rose, by any other name would smell as sweet!"
-- Shakespeare, "Romeo and Juliet", 1594 <<

Is this an argument for doing math with Roman Numerals, since they are
the same Integers? Words are mind tools. Common people also confuse
an engine and a motor, meteors, meteoroids and meteorites, guns and
pistols. ships and boats, etc.

But it does make a difference to the trained professional whose skill
domain makes use of exact words.

What I have found is that if you can get the concepts into a student
and cut him loose from his old mindset, he learns much faster, much
better.

Hi Joe,

What you appear to be forgetting is that most people out there are not
dinosaurs like you and me. They have never ever worked with file storage
or other sequential I/O. The *only* times they see the terms record and
field is in the context of a relational database. So these words
describe the same concepts to them as the words row and column describe
to us.

(snip)

>The same principle applies to documentation and your predecessors had
failed completely to maintain as a part of the system. Nothing should
have been allowed to modify the system without changing the
documents. And management who did not make that part of the system
failed their job.

Exactly!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[--CELKO--]

>What you appear to be forgetting is that most people out there are not dinosaurs like you and me. They have never ever worked with file storage or other sequential I/O. The *only* times they see the terms record and field is in the context of a relational database. So these words describe the same concepts to them as the words row and column describe <<

While I grant that there are only a few of us here who remember punch
cards and mag tapes, even the newbies with their new-fangled C#, Java
and VB have used files that come with those languages.

I am making shadow boxes for the house. So far I have put my old
slide rulers on exhibit, and have the collections of abaci and
astrolabes ready to go up next. But I cannot find a punch card !

[Roy Harvey (SQL Server MVP)]

On Thu, 9 Oct 2008 07:41:15 -0700 (PDT), --CELKO--
<jc*******@earthlink.netwrote:

>While I grant that there are only a few of us here who remember punch
cards and mag tapes, even the newbies with their new-fangled C#, Java
and VB have used files that come with those languages.

You continue to think strictly in terms of bit IT operations. While
you weren't paying attention over the last 20 years or so that has
become only a fraction of the IT industry as much of the growth has
been in companies and in systems that are entirely divorced from the
world of big iron.

>I am making shadow boxes for the house. So far I have put my old
slide rulers on exhibit, and have the collections of abaci and
astrolabes ready to go up next. But I cannot find a punch card !

Alas, I seem to have discarded all my punch cards too. However I do
have a five gallon glass water cooler bottle just about full of punch
chips and the dots from teletype.

Roy Harvey
Beacon Falls, CT

[DA Morgan]

--CELKO-- wrote:

I am making shadow boxes for the house. So far I have put my old
slide rulers on exhibit, and have the collections of abaci and
astrolabes ready to go up next. But I cannot find a punch card !

I have a couple 80 column cards on my desk. Want one?
Trade you for a 10MB hard disk. <g>
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)