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,someotherIDfi eld, someTable, someotherField, pre2, 1
3,somethirdIDfi eld, someTable, somethirdField, pre3, 1
4,somefourthIDf ield, 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, groupingEntityI D)
VALUES (1, prefix + nameField, 1, result1)

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

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

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

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

--next reord from groupSets
SELECT DISTINCT someotherIDfiel d FROM someTable WHERE someotherIDfiel d IS
NOT NULL


INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (2, prefix + nameField, 1, result1)

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

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

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

INSERT INTO groups(setId, groupName, active, groupingEntityI D)
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--]

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

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

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

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,someotherIDfi eld, someTable, someotherField, pre2, 1
3,somethirdIDfi eld, someTable, somethirdField, pre3, 1
4,somefourthIDf ield, 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, groupingEntityI D)
VALUES (1, prefix + nameField, 1, result1)
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (1, prefix + nameField, 1, result2)
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (1, prefix + nameField, 1, result3)
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (1, prefix + nameField, 1, result4)
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (1, prefix + nameField, 1, resultN)
>
--next reord from groupSets
SELECT DISTINCT someotherIDfiel d FROM someTable WHERE someotherIDfiel d IS
NOT NULL
>
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (2, prefix + nameField, 1, result1)
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (2, prefix + nameField, 1, result2)
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (2, prefix + nameField, 1, result3)
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
VALUES (2, prefix + nameField, 1, result4)
>
INSERT INTO groups(setId, groupName, active, groupingEntityI D)
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]

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

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

"--CELKO--" <jcelko212@eart hlink.netwrote in message
news:b91a91cd-0e23-4ad1-b142-22bead23c75e@d4 5g2000hsc.googl egroups.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]

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

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" <emurphy42@soca l.rr.comwrote in message
news:gbf0cs$fjr $1@registered.m otzarella.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,someIDfiel d, someTable, someField, pre1, 1
>2,someotherIDf ield, someTable, someotherField, pre2, 1
>3,somethirdIDf ield, someTable, somethirdField, pre3, 1
>4,somefourthID field, 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, groupingEntityI D)
>VALUES (1, prefix + nameField, 1, result1)
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>VALUES (1, prefix + nameField, 1, result2)
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>VALUES (1, prefix + nameField, 1, result3)
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>VALUES (1, prefix + nameField, 1, result4)
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>VALUES (1, prefix + nameField, 1, resultN)
>>
>--next reord from groupSets
>SELECT DISTINCT someotherIDfiel d FROM someTable WHERE someotherIDfiel d IS
>NOT NULL
>>
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>VALUES (2, prefix + nameField, 1, result1)
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>VALUES (2, prefix + nameField, 1, result2)
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>VALUES (2, prefix + nameField, 1, result3)
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>VALUES (2, prefix + nameField, 1, result4)
>>
>INSERT INTO groups(setId, groupName, active, groupingEntityI D)
>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--]

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

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

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

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)
,TrainingGroupN ame varchar(255)
,GroupingEntity ID int
,TrainingGroupI D int
,TrainingGroupS etID int
,Active bit
)

DECLARE trainingGroupSe ts_cursor CURSOR FOR
SELECT TrainingGroupSe tID, TrainingGroupSe tName, Datasource,
GroupingEntityI DField, GroupingEntityN ameField, NamingPrefix
FROM TrainingGroupSe t WHERE Active = 1

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

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT e.' + @grEntIdField + ', e.Name, b.TrainingGroup Name,
b.GroupingEntit yID, b.TrainingGroup ID, b.TrainingGroup SetID, 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 GroupingEntityI D,
TrainingGroupNa me, TrainingGroupID , TrainingGRoupSe tID, Active FROM
db1.dbo.Trainin gGroup WHERE TrainingGroupSe tID = ' + @tgSetId + ' '
SET @sql = @sql + ') b ON e.' + @grEntIdField + '= b.GroupingEntit yID '
SET @sql = @sql + 'UNION SELECT e.' + @grEntIdField + ', e.Name,
b.TrainingGroup Name, b.GroupingEntit yID, b.TrainingGroup ID,
b.TrainingGroup SetID, 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 GroupingEntityI D,
TrainingGroupNa me, TrainingGroupID , TrainingGRoupSe tID, Active FROM
db1.dbo.Trainin gGroup WHERE TrainingGroupSe tID = ' + @tgSetId + ' '
SET @sql = @sql + ') b ON e.' + @grEntIdField + '= b.GroupingEntit yID '


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.Trainin gGroup(Training GroupSetID, TrainingGroupNa me,
Active, GroupingEntityI D)
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.Trainin gGroup tg INNER JOIN @tgSetMatrix m ON
tg.TrainingGrou pID = m.TrainingGroup ID
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.Trainin gGroup tg INNER JOIN @tgSetMatrix m ON
tg.TrainingGrou pID = m.TrainingGroup ID
WHERE (m.EntityID IS NOT NULL) AND (m.TrainingGrou pID IS NOT NULL) AND
(m.Active = 0)
END

DELETE FROM @tgSetMatrix

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

CLOSE trainingGroupSe ts_cursor
DEALLOCATE trainingGroupSe ts_cursor
END


"--CELKO--" <jcelko212@eart hlink.netwrote in message
news:1f6e7a6d-738d-4b0e-be80-0ac7fb84385a@f6 3g2000hsf.googl egroups.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]

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

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 "TrainingGroupS etName" 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]

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

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_synt ax?

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

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]

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

In article <cdebc3df-f0e5-421a-a4cb-0e12c4441347@z6 6g2000hsc.googl egroups.com>,
--CELKO-- <jcelko212@eart hlink.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: tony@softins.co .uk - http://www.softins.co.uk
Play: tony@mountifiel d.org - http://tony.mountifield.org

[--CELKO--]

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

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

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

--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.wash ington.edu (replace x with u to respond)

[CK]

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

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@gm ail.comwrote in message
news:6b15e7e0-497d-491f-91e1-8c1e1c16042c@a7 0g2000hsh.googl egroups.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_synt ax?
>
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_gymnas ts"?
>
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]

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

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]

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

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?