CK (c_kettenbach@hotmail.com) writes:
> 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 ,

You get yourself a table of numbers. That is, a table that holds numbers
from 1 to which limit you want. Here is a table-valued function written
by SQL Server MVP Itzik Ben-Gan which very fast, as long as you don't
need millions of numbers:

CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO


--
Erland Sommarskog, SQL Server MVP, esquel@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

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 ,
~ck

"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 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 ,

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

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

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:b91a91cd-0e23-4ad1-b142-22bead23c75e@d45g2000hsc.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.

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 .

"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:gbf0cs$fjr$1@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 ,
>
> 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

>> 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.

Well I could use your 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 you might provide. I apologize
and I really would like your .

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--" <jcelko212@earthlink.net> wrote in message
news:1f6e7a6d-738d-4b0e-be80-0ac7fb84385a@f63g2000hsf.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.

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

1. The names violate ISO-11179 rules. You actually have a column
named "name" -- could you be vaguer?? Well, "datasource" pretty
generic. Actually, you were vaguer with "entity_id" which is absurd.
The essence of an identifier is that it is specific to one and only
one set of entities. To believe in a universal, generic "entity_id"
is Kabala mystic tradition which all things in creation have a number
assigned by the Lord God of Israel. We name a data element for what
it is by its nature; not for how it is stored, or used in a particular
situation.
2. Even MS gave up on camelCasing; it is a bitch to maintain because
your eyes jump to uppercase letters then back again. Start using the
underscore to separate the attribute name from its roles and
properties. People read it in a linear fashion, thanks to a century
of ruled notebook paper.
3. Standard SQL and now parts of T-SQL require a semi-colon statement
terminator, so start using it. Another question is why did you use
extra parentheses, single-statement BEGIN-END block, commas at the
start of a line, open and close parentheses on their own line, etc.?
We did that stuff with punch cards so we could slip in more cards
later in the 1960's. We have video terminals and editors now, so stop
formatting your code as if you were is a punch card world.
4. Why did you use "_field" as part of the data element names? Fields
and columns are totally different concepts. I chopped off that file
system legacy. And why are so many things VARCHAR(50), a magic number
from ACCESS? The length of a character column is one of the most
important properties it has, unlike field who length is determined by
the application program reading the file.
5. Table variables (proprietary in T-SQL) and temporary tables (not
ANSI Standard in T-SQL) are attempts to mimic scratch tapes and are to
be avoided. Instead we have CTEs, VIEWs and derived tables. The
optimizer can use these logical virtual tables to best advantage
instead of being forced to materialize them in physical storage.
6. We do not write with flags -- that is more punch card stuff. SQL
is a predicate language. We had to set flags with magnetic tapes and
punch cards after we determined a status in one procedural step to
pass the results to the next procedural step. Hey, back then, big
computers were less powerful than your wristwatch is today. Another
problem with flags is that you have to check them before you use them,
so why not just use the predicate instead of the flag? They force you
to have tightly coupled code modules Remember that term from basic
Software Engineering? It still applies in declarative languages.
Another problem is that you quickly have to write code that plays "20
Questions" and requires insanely complex CHECK() constraints. And the
name "active" is an adjective without a noun, so I changed it to
active_flg. But we need to get rid of it completely. We need a
(start_date, end_date) pair that tells us the duration of the active
status. This is, again, basic data modeling.
7. The construct ((A RIGHT OUTER B) UNION (A LEFT OUTER JOIN B)) in
your generated code is weird for several reasons. First of all, there
is a lot of redundancy (We hate redundancy in RDBMS, like the Pope
hates Evil). Next, we dislike RIGHT OUTER JOINs in our Latin alphabet
culture. We read left to right, so having the preserved table come
later is conceptually hard. Then you did not need the SELECT DISTINCT
because the UNION will remove redundant duplicates for you; let the
optimizers decide how to do that. But the kicker is that the whole
damn mess can probably be reduced to (A FULL OUTER JOIN B), preserving
both tables. Since you did not post DDL, as per netiquette, this is
only a guess.
SELECT ..
FROM (SELECT..
FROM Datasource
WHERE gr_ent_id IS NOT NULL) -- impossible by def
AS E
FULL OUTER JOIN
(SELECT ..
FROM db1.dbo.TrainingGroups
WHERE traininggroup_set_id = tg_set_id)
AS B
ON E.gr_ent_id = B.grouping_entity_id;

8. The comment /* If any training group id is NULL, we need to create
the training group */ is insane. BY DEFINITION, an identifier is
always NOT NULL, unique and exists. That is why it is ALWAYS a key
somewhere in the schema. But we have no DDL to see how screwed up the
schema is. That creation process is another function. I invented the
catch phrase "Automobiles, Squids and Britney Spears" to describe
tables and modules that have no cohesion -- they do more than one task
or store several unrelated things which are of totally different
natures.
9. What kind of things are "TrainingGroup" and "TrainingGroupSet" in a
set-oriented language? Think about it. Is this a hierarchy or
something? You are also telling us you have one of each by the
singular names; did you mean "TrainingGroups" or what?
10. Why did you put an "IF EXIST(..)" in front of an "UPDATE.."
statement? The WHERE clause of the UPDATE will find if the set of
rows to be updated is empty; just write a good predicate. Your
mindset is still in file systems. An empty set (i.e. table) is still
a set, but a missing file or an empty file (i.e. physical directory
entry NIL pointer) is a real problem in old file systems. Also, never
use that proprietary UPDATE.. FROM.. crap; it is proprietary and has
changed semantics at least twice since it was invent decades ago by
Sybase. Learn to write good Standard SQL.
11. The "EXISTS (SELECT 1.." tells me that you learned SQL from an old
Oracle person. The modern, preferred form is "EXISTS (SELECT * ..."
instead. Before Oracle had an optimizer, this made a difference in
performance decades ago. Now, it is just a confusing antique -- the
watch pocket in blue jean of SQL. All the SQLs I know will do it the
same way, but it makes you look like a hick.
12. Please set up basic naming conventions. I can guess that "tg_" is
"Testgroup_", that "ds" is "Datasource" and "gr_" is "group_", but why
not use a text editor and be consistent? Data dictionary needs this.
A real professional is always asking the question "How can I make this
code easier for the poor dumb bastards that have to maintain it after
I get my dot-com stock options and retire in the South of France?"
13. Dynamic SQL is usually an admission that any future random
untrained user will be able to write better code than you can. It is
right up there with cursors as a kludge, but it can allow SQL
Injection and destroy the company (read about T. J. Maxx and the
credit card attack in the trade press).
14. Why did you generate "(nmg_prfx + gr_ent_name) AS "vague_name"?
The data element names change from the rest of the schema, which is
awful. But this implied that you have split an attribute across two
columns when it needs to be in one.
15. You declared a table variable named "Testgroup_Set_Matrix" which
is as bad as or worst than the "_field" in those column names. First,
it violates the rule that a data element is named for what it is and
not how it is stored. But you cannot store a matrix in SQL! We have
one and only one data structure in RDBMS; the table. A matrix is an
algebraic structure with math rules, transposes, inverses, eigenvalues
and lots of other properties. Words have meaning, so don't snorf your
dinglehoppers!
16. This table ought to be a CTE and that still stinks. It would look
like this:
WITH Active_Testgroup_Set (..)
AS
(SELECT traininggroup_set_id, traininggroup_set_name,
datasource, -- why is this both a table & column name?
grouping_entity_id, grouping_entity_name, naming_prefix
FROM Traininggroup_Set
WHERE active_flg = 1) --flags suck!

17. Combine UPDATEs so you touch the tables only once, something like
this:

UPDATE db1.dbo.TrainingGroups
SET active_flg
= CASE
WHEN EXISTS
(SELECT *
FROM Active_Testgroup_Set AS A
WHERE A.entity_id IS NULL -- impossible by def!
AND TrainingGroups.traininggroup_id = A.traininggroup_id)
THEN 0
WHEN EXISTS
(SELECT *
FROM Active_Testgroup_Set AS A
WHERE TrainingGroups.traininggroup_id = A.traininggroup_id
AND A.entity_id IS NOT NULL -- true by def
AND A.traininggroup_id IS NOT NULL -- true by def
AND A.active_flg = 0)
THEN 1
ELSE active_flg -- otherwise, do nothing
END;

18. I think the DDL is screwed up and that if you can fix it, the
system will perform 1-2 or more orders of magnitude better, be
smaller, and not need convoluted DML to answer queries or change the
structure of the schema.

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 ful, 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 FUL. 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 . You tell everyone the same thing:
"You're stupid. Your code sucks. This is not ANSI/ISO standard. I
need the full table to you. I need sample data to you. I
can't 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.

0) Would mind commenting on what was actually posted instead of
telling me that you don't like my style? You might want to take a
look some current television programs to you focus; HOUSE and
KITCHEN NIGHTMARES.

>> 1) Your sarcasm is not funny. <<

My flaw is not sarcasm; I am pedantic to the point of playing Trivial
Pursuits when I answer a posting.

>> 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. <<

IT has a failure rate that very few other trades would live with.
Only exploratory mining and certain drug research areas are worse.
DeMacro, Weinberg, Yourdon, et al have investigated this for decades
(do you read Dorset House publications? Best place for the painful
details). The principle reason for this is poor design at the
beginning of projects.

Most applications did not "go wild" because of external changes, like
new laws or technology. They fail because they were not designed to
be robust, portable or modular (i.e. high cohesion, loose coupling).
Instead, they were proprietary and monolithic with no thought to
future changes.

>> Additionally, most of us must conform to certain coding standards, which also may predate certain ANSI/ISO standards. <<

Yes, you do, Cowboy If you are an old DB2 mainframer, the world is
uppercase. If you are a UNIX user, it is lowercase. If you are an
old Microserf, then it is Hungarian! Since eye movement and
readability are researched, measurable facts, do you think it might be
a good idea if the industry started to adopt one consistent set of
rules that have been tested?

>> 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. <<

Then you failed to give an example of any particular "certain
situations" in this bullet point. Would like to do that now? Any
common case jump out at you?

I will stand by that statement just like a stand by "high cohesion and
loose coupling give you good code", which is even more general. Table
variable are both proprietary and a way for a non-RDBMS programmer to
mimic a scratch tape. Next, since you will force the engine to
materialize a physical table at some point, you screw the optimizer.
Don't do that, cowboy.

I don't know if you are old enough to realize when people are
repeating past errors, but I am (look up Dijkstra's quote about
this). ANSI SQL has a temporary tale model that is very different
from T-SQL's version. If you write to T-SQL and have to explain it or
port to a proper implementation, it is a bitch -- been there, done
that.

>> 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. <<

Sorry, but there is a VERY important difference. How long have you
been trying to change the mindset of progammers to sets and not
sequences? It is like when mankind realized that zero is number. It
moves the mindset from concrete, sequential file processing to
abstract, set-oriented processing. Suddenly the data is not a passive
lump of bits on a punch card or tape; it is active, not bound in a
physical space, unrelated to a whole, etc.

After 30+ years of teaching SQL and more than that writing the
language, I found this was THE major blocking point. Once you see
data as a thing separate form the applications using it,

Please tell me your decades of experience that contradict that.

>> 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. <<

Flags and bits are great for low level things. Dijkstra's semaphore
algorithms are vital for modern operating systems. But they have no
place in RDBMS. And yes, bad developers are a major cause of the
insanely high failure rates in IT. And unfortunately when you get
legacy code (aka "Family curse") you have to deal with them.

Since I earn a lot of my living fixing crappy SQL, you might be
surprised how often you can get rid of flags. I had a quick gig where
the vector of bit flags had grown out of control over time, so you had
to deduce the current status by looking at 13 yes/no questions.

The bad news was that there were no CHECK() constraints to prevent an
invalid combination, so the data integrity was shot to hell. I often
feel like a pathologist instead of a consultant, but I digress.

These 13 flags were replaced with one Industry standard status code
and a local QA status code we invented and a single very simple
CHECK() that related the two (basically, you cannot do a QA step until
the product was at a certain status).

I added columns for the two new status codes and then did an update
from the flags. Where the data was screwed up -- almost 10% of the
data in a snapshot!! -- we used a NULL and then hand corrected it.
When the data was clean, then add constraints.

Suddenly, their data was consistent, could be exchanged with other DBs
and the queries became an order of magnitude easier to write and
maintain. One of their people re-wrote 30+ queries in one day. He did
a text edit where "(flg_1 = 1 AND flg_2 = 0 AND flg_3 IN (0,1)
AND.. )" became "(foobar_status BETWEEN 500 AND 599)" instead.

>> 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"? <<

Yes, it does. The first IDEF standards used a single noun for a file
name because they were meant describing for record-at-a-time
processing. Do you understand the conceptual difference between
these three words?

1) Employee: Dilbert
2) Employees: Dilbert, Wally, Asok, Alice, etc.,
3) Personnel: abstract set defined by a characteristic function

Now, ask your self what a table models. One entity (possible but not
likely)? A particular fixed list (possible but not likely)? An
abstract set defined by a characteristic function that changes over
time (BINGO!)?

>> 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. <<

Why am I supposed to applaud bad programming?

>> 7) Why must everyone write in "Standard SQL"? <<

That is so damn stupid, I don't know how to respond. Oh wait, fight
stupidity with sarcasm! Hey, wouldn't it be great if every vendor has
his own programming language and there were no standards at all? As
long as we are being cowboy coders, why not one language per
developer? (that is why TIL languages failed).

>> Rarely do products actually have to worry about their code being portable between database platforms--and when they do, its virtually impossible to do. <<

You really don't get out much. That worry is why the major (and many
minor) vendors are on ANSI and ISO committees. This why DB2, Oracle
and SQL server have ROW_NUMBER() . This is why SQL Server added the
ANSI temporal stuff in 2008. Hey, kid, smell the coffee! Nobody who
is anybody runs on just one RDBMS today. The game is data interchange
and specialized DBs. Your little Microsoft desktop world is the IT
version of an aboriginal tribe in the Amazon jungle.

>> 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 (i.e., performance & maintainability). <<

Gee, that is what I do for living If you are willing to spend the
time to get the DDL right, the DML almost writes itself. As the late
Ken Henderson said, I know how to write the fancy stuff, but I don't
it.

>> 8) When did dynamic code become so horrible? Again, this is a totally valid programming tool. <<

Actually, it is kludge from the early days when we were debating
compiler and interpreter version of SQL in ANSI X3H2.

>> Having dynamic SQL has nothing to do with SQL injection. <<

Okay, give me an example of SQL injection done with a compiled
procedure.

>> You need to protect against SQL injection in your front-end user interface. <<

You actually told the world that you write SQL which depends on the
front end for projection!!

>> A poorly written front end can always allow for SQL Injection. <<

Please show me a stored procedure call that allows injection when the
procedure has no dynamic code in it.

>> 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. <<

No, when the same data element has two or more names, then the naming
convention is NOT consistent.

>> 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 don't know what you would call that data, but I do know that on the
RDBMS side of the house we do not discuss display at all. Tiered
architecture? Front end? Back end? Basic programming principles??
Hello!!?

>> I think I would probably refer to it as a Matrix. <<

I think you would do that, too

>> Just because T-SQL doesn't have a concept of "Matrices" does not make it an invalid variable [sic] name. <<

How can I respond to someone who is this out of it?? It is not T-SQL
(wake up the world is bigger than your village, cowboy) that does not
have Matrices; it is RDBMS. It was not used as a variable name, but a
table name. This is the wrong word for the concept; he is trying to
build a temporary table of certain entities with an active flag set to
one. Hey, wild crazy idea! Let's use a relational, descriptive name
for the table. If he had added (i,j,k) index columns to ti, then it
might model a matrix.

>> T-SQL does not have a concept of "gymnasts"; does that mean I cannot have a temp table called "Olympic_Gymnasts"? <<

LOL!! And you accuse me of sarcasm?

But to answer the stupid question, that probably ought to be a VIEW
on a Gymnasts table. Then we need to ask if going to the Olympics is
enough or do you have to win a medal, etc. Give me a clear spec.

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

Can you define the verb "pwn" for me? Does it involve large barnyard
animals and pulleys?

And I think I am ful, if the greenhorn (funny you should use a
cowboy term) is willing to get over his own arrogance and actually
look for . More often than not, we are asked for kludges -- what
Chris Date called the "Do my homework/job for me!! NOW! And format it
for me! The world owes me!!" kind of people. By the way, Chris Date
has no email for that reason.

I get pedantic when the Newbie prefixes his post with a statement that
he is getting no education, no , etc. from his management. I
cannot give him 4-5 years of education in a Newsgroup; hell, I cannot
download one of my books into a posting! But if I can beat some
fundamentals into him in a posting, then he has a chance.

Google up the Dijkstra poster (http://lemonodor.com/archives/2007/10/
youre_doing_it_wrong.html) and put them on your wall. I deeply regret
that I could have met him in Austin at UT and did not. He did not
suffer fools gladly. And he made computer science a science in his
lifetime. Read his stuff on line at University of Texas.

>>: 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 . You tell everyone the same thing: "You're stupid. Your code sucks. This is not ANSI/ISO standard. I need the full table to you. I need sample data to you. I can't 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. <<

Gee, Andy, let's compare my contributions to SQL, RDBMS, IT, etc. and
yours.

Rarely of ? Well, I guess that is why my seven (soon to be eight)
books keep selling for *decades* in a market where titles seldom last
more than one printing. My name is associated with various SQL
programming techniques. I have 850+ articles in the trade press (look
for me in GAMES magazine in December this year, too). I have done
dozens of presentations over the years at trade shows.,

Oops! You have yet to make a mark. Instead, you are more worried
about your feelings. This is the mindset of a 3-year old, not an
adult. Or the mindset of the current generation that has a sense of
entitlement. -- "Hey, I crammed for an MS certification, so that 6
weeks makes me a million dollar a year developer! The world owe mes!
If anyone criticizes me, they are bad people! Boo hoo! Boo hoo!
Whine, whine, whine." Perhaps the "entitled generation" mindset is
the the 3-yer old mindset. Feelings over facts, form over substance,
etc;.

Now, to get back on topic, the poster failed to give DDL, so an exact
answer is not possible. But the basic approach -- CTE to replace
cursor, UPDATE with CASE expression, etc. -- are sound. Demonstrate
otherwise with a posting that has content.

In article <cdebc3df-f0e5-421a-a4cb-0e12c4441347@z66g2000hsc.googlegroups.com>,
--CELKO-- <jcelko212@earthlink.net> wrote:
> 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: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

>> 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.

--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
damorgan@x.washington.edu (replace x with u to respond)

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

"Andy M" <andy.mallon@gmail.com> wrote in message
news:6b15e7e0-497d-491f-91e1-8c1e1c16042c@a70g2000hsh.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 ful, 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 FUL. 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 . You tell everyone the same thing:
> "You're stupid. Your code sucks. This is not ANSI/ISO standard. I
> need the full table to you. I need sample data to you. I
> can't 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.
>

> 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.