Joe Celko The SQL Apprentice

A Strange Use of UDFs?

2007-12-28T13:24:00.000-05:00

SQL Apprentice Question
started a new project recently and the .Net/SQL Server 2000
application I was hired to help with was developed by a large
consulting firm. All the data access for the application is done via
stored procedures, but most stored procedure data access is done
via functions. So, you might have a SELECT inside a stored
procedure that looks similar to this:

SELECT e.LastName, et.TerritoryDescription, en.NADescription
FROM Employees e
LEFT JOIN fxEmpNat() en ON e.EmployeeID = en.EmployeeID
LEFT JOIN fxEmpTer() et ON e.EmployeeID = et.EmployeeID
WHERE e.LastName = 'Fuller'

And the function definitions are *similar* to those created in the
script listed at the end of this post (using the Northwind database).
I would solve the same problem using this approach:

SELECT e.LastName, t.TerritoryDescription, n.NADescription
FROM dbo.Employees e
LEFT JOIN dbo.EmployeeTerritories et ON e.EmployeeID = et.EmployeeID
JOIN dbo.Territories t ON et.TerritoryID = t.TerritoryID
LEFT JOIN dbo.EmployeeNationality en ON e.EmployeeID = en.EmployeeID
JOIN dbo.Nationality n ON en.NationalityID = n.NationalityID
WHERE e.LastName = 'Fuller'

The original developers are long gone and no one currently working
on the project knows why the function-centric approach was used.
There are no security restrictions that would merit such an approach,
and even if there were I would think a VIEW solution would be the
more traditional approach. And there are only a couple of complicated
relationships that one might want to "hide" from a less experienced
developer that did not know the intricacies of the data. Anybody have
any ideas when this approach would be justified?

-- Addl. Table and Function Definitions
CREATE TABLE Nationality
(
NationalityID int PRIMARY KEY,
NADescription varchar(20) NOT NULL
)
go
CREATE INDEX IX_Nationality_NADescription ON Nationality(NADescription)
go
INSERT Nationality VALUES (1,'America')
INSERT Nationality VALUES (2,'Canada')
INSERT Nationality VALUES (3,'Angola')
go

CREATE TABLE EmployeeNationality
(
EmployeeID int,
NationalityID int,
CreateDate datetime
CONSTRAINT PK_EmployeeNationality
PRIMARY KEY NONCLUSTERED
(EmployeeID,NationalityID,CreateDate)
)
go
INSERT EmployeeNationality values(1,1,'01/01/80')
INSERT EmployeeNationality values(1,1,'01/01/90')
INSERT EmployeeNationality values(2,1,'01/01/90')
INSERT EmployeeNationality values(3,2,'01/01/90')
INSERT EmployeeNationality values(4,3,'01/01/90')
go

CREATE FUNCTION fxEmpNat()

RETURNS TABLE

AS

RETURN (
SELECT e.EmployeeID, n.NADescription
FROM dbo.Employees e
JOIN dbo.EmployeeNationality en
ON e.EmployeeID = en.EmployeeID
AND en.CreateDate = (SELECT MAX(en2.CreateDate)
FROM EmployeeNationality en2
WHERE en.EmployeeID = en2.EmployeeID
AND en.NationalityID = en2.NationalityID )
JOIN dbo.Nationality n ON en.NationalityID = n.NationalityID
)
go

CREATE FUNCTION fxEmpTer()

RETURNS TABLE

AS

RETURN (
SELECT e.EmployeeID, t.TerritoryDescription
FROM dbo.Employees e
JOIN dbo.EmployeeTerritories et ON e.EmployeeID = et.EmployeeID
JOIN dbo.Territories t ON et.TerritoryID = t.TerritoryID
)
go

Celko Answers
One reason I can think of is job security. This code will never port
and can be read only by dialect speakers. A function call cannot be
optimized like a VIEW, so you are at risk for poor performance, as
well as the maintenance problems, as time goes on.

Another reason is that they are not SQL programmers and do not think
in terms of declarations. They want to see the familiar function call
they know from procedural languages.

Original source

Using WHERE clause parameter

2007-12-28T13:21:00.000-05:00

SQL Apprentice Question
When I try to use an SP with parameter that is the WHERE clause it generates
an error.

E.g,

@myWhere = varchar(200)

AS

SELECT x, y FROM skwi WHERE @myWhere

myWhere = status = 7 AND LastName = 'Smith'

The problem is that the where clause is built conditionally in the program.
Any advise and examples on how to accomplish would be appreciated.

Celko Answers

>> When I try to use an SP with parameter that is the WHERE clause it generates an error. <<

The short, dangerous kludge is to use Dynamic SQL.

The right answer is to get out that old text book on Software
Engineering and the chapters on coupling and cohesion of code
modules. Those rules still apply in SQL.

You are writing a "Britney Spears, Automobiles and Squids" module --
you have no idea what it will do at run time, so it has absolutely no
cohesion. Instead of depending on every random future user to write
proper SQL, you need to earn your salary and proper them with a well-
defined module with a meaningful name and a known parameter list.

If you want a general query tool, then use QA. Application users
should be kept away form it.

Original source

How do I group in a union?

2007-12-27T10:25:00.000-05:00

SQL Apprentice Question
I have a select statement that uses union to pull data from multiple
databases and return them in a single recordset. I want to group these
results using group by. How do I do that?

Here's what I have:

SELECT EmployeeID, ProjectID
FROM DB1.table1
UNION
SELECT EmployeeID, ProjectID
FROM DB2.table1

This works fine but I want to group all projects by EmployeeID. I tried the
following but it didn't work

SELECT EmployeeID, ProjectID
FROM DB1.table1
UNION
SELECT EmployeeID, ProjectID
FROM DB2.table1
GROUP BY EmployeeID

I'd appreciate some help here.

Celko Answers
The results of a UNION do not have column names

SELECT X.emp_id, COUNT(project_id) AS project_tot
FROM (SELECT emp_id, project_id
FROM DB1.Table1
UNION
SELECT emp_id, project_id
FROM DB2.Table1)
AS X(emp_id, project_id)
GROUP BY X.emp_id;

UNION ALL will be faster, if it is possible.

Original source

when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?

2007-12-27T10:20:00.000-05:00

SQL Apprentice Question
when calling UPDATE from the result set of a SELECT statement, is the order
in which rows from the SELECT statement 100% geronteed?

tableA

myid
-----------
0
1
2
3
4
5

UPDATE tableB u
SET myid = i.myid
FROM tableA i
ORDER BY myid

Will this always update tableB with 5 since it is the last one? is this 100%
garonteed to follow the order of the source result set?

Celko Answers
No. There is no ordering in a table by defintion. Since this strictly
proprietary syntax it an do anything MS feels like next week. You are
just looking for the comfort of a 1960's sequential file system
instead of learning to think in RDBMS.

Original source

Which values do NOT appear within a tolerance value Options

2007-12-27T10:05:00.000-05:00

SQL Apprentice Question
I have a table of servers that receive updates on a regular basis

CREATE TABLE [dbo].[tbl_ServerUpdate](
[ServerUpdateID] [int] NOT NULL,
[Server] [nvarchar](256) NOT NULL,
[UpdateStamp] [datetime] NOT NULL,
)

I then have tolerance values in another table which I can get like this
(they are in terms of minutes):

DECLARE @Interval INT

SELECT @Interval = IntervalValue
FROM tbl_Interval (NOLOCK)
WHERE IntervalID = 1

I want to know which servers have NOT received an update within the
tolerance value. For example, give me the set of Server that have not
received an update in the last 5 (@Interval) minutes.

Celko Answers
What is an update_id? What would it mean in a logical data model?
Surely, you did NOT just physically number the rows in a table!

This table also had no key, so I made an assumption that you want to
use the (originally very long AND possibly in Chinese!) server names.
But isn't the update interval logically an attribute of each server?
Shouldn't it be in the Servers table? I cannot see an interval
floating around as an entity in itself.

CREATE TABLE Servers
(server_name VARCHAR(25) NOT NULL PRIMARY KEY,
update_stamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
update_interval INTEGER NOT NULL
CHECK(update_interval > 0),
etc.
);

If you need this a lot, put it in a VIEW.

CREATE VIEW ExpiredServers (server_name)
AS
SELECT server_name
FROM Servers
WHERE update_stamp
< DATEADD(MI, -update_interval, CURRENT_TIMESTAMP);

SQL Apprentice Question
In the real world, everything is not a beautifully static picture of
relational data. I didn't include the key because I don't think its relevant
to the solution. But since you brought it up: is it better to have a natural
key on a varchar or an identity key as an integer that doesn't really have
any intrinsic business meaning

? In theory, it should be the varchar, but in practice its more performant
to index an int rather than a varchar(25). Is that not true?

The interval is not an attribute of the server, but rather an attribute of
the update type. In my case, there are seven different minute intervals. So,
in the end, I need to know - for each update type - which server did not
receive it within the last [interval] (in terms of minutes). The interval is
configurable by the operations group - sometimes they may want to be alerted
when a server didn't receive an update in the last 3 minutes, sometimes in
the last 10 minutes. They can change this real-time in the database.

I am not a TSQL guru - thats why I use this newsgroup from time to time. I
also have every one of your books on my shelf. However, the corporate world
is NOT academic and there is a tradeoff between having an understandable
data model that is easy to work with and having a data model that can serve
as a model in CS405 (or whatever). Sometimes we have to denormalize or
flatten data to be able to run reports in an acceptable time; sometimes we
have to add bit columns that could easily be derived from another to improve
query performance.

The balance you give is valuable, so I hope you continue to berate us when
our designs violate, but I'll bet - despite all the companies you have
worked with - you can't name one that had a data model that gave you a
boner.

Celko Answers
>> is it better to have a natural key on a VARCHAR(n) or an IDENTITY key [SIC] as an integer that doesn't really have any intrinsic business meaning? <<

How important is data integrity? Why not use a pointer chain DB
instead of mimicking it in SQL? At least they have features to do
garbage collection, restore chains, etc. Eventually, denormalization
and short-cuts will come back and bite you.

>> In theory, it should be the VARCHAR, but in practice its more performant to index an INTEGER rather than a VARCHAR(25). Is that not true? <<

It depends on the product. You automatically assumed indexing; look
at hashing in Teradata. Longer strings for keys lead to easy perfect
hashing, which is always one probe as opposed to multiple probes with
an index when the database gets large. If you are using hidden
pointer chains like SQL Anywhere to implement PK-FK, there is no
difference. SQL Server does have a lot of "code museum" problems and
this is one of them; one type of simple B+ Tree index is used for all
data types and distributions. However, until you get to large DBs, it
works fine and it will get a shot in the arm from 64-bit hardware,
too.

>> The interval is not an attribute of the server, but rather an attribute of the update type. In my case, there are seven different minute intervals. So, in the end, I need to know - for each update type - which server did not receive it within the last [interval] (in terms of minutes). The interval is configurable by the operations group - sometimes they may want to be alerted when a server didn't receive an update in the last 3 minutes, sometimes in the last 10 minutes. They can change this real-time in the database. <<

A type is an attribute by definition, so what entity does the
update_type belong to, if not a server? I would think from this
description, you would have seven columns for the logically different
updates, and seven columns for their corresponding intervals. Or is
this a repeating group where not all update types apply to all servers
and the update types can be changed?

>> I also have every one of your books on my shelf. <<

Neat! Number 7 is out in 2008 February.

>> .. I'll bet - despite all the companies you have worked with - you can't name one that had a data model that gave you a boner. <<

LOL! Remember what I do for a living -- this is like asking a doctor
why all his patients are sick!

The best one I can remember just off hand was for a credit management
company. They had just designed it and wanted a two day review from
me. The only real problem I found was that a bunch of the columns
were stubbed in with a magical CHAR(1) NOT NULL data type; they were
still working on the encodings and waiting for advise from legal.

Another one was a software company with a portal product that manages
corporate software access. We replaced ~60 tables with a nested set
model that lead to ~6 tables for the core processes. In fairness, the
original model had grown over time from a denormalized model on a
small platform to a mainframe tool. People kept adding tables to it
as a work-around and it had become a jungle.

Original source

QUOTED_IDENTIFIER & ANSI_NULLS Options

2007-12-27T10:02:00.000-05:00

SQL Apprentice Question
does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.

Celko Answers
>> is there a reason I wouldn't want to do this? <<

Conformance to ANSI/ISO Standards should be a goal in any shop, so you
would not turn off options that bring you to that goal. Why would you
want to write your own database language?

Original source

update table dateCol3 to the later of dateCol1 or dateCol2 Options

2007-12-27T09:59:00.000-05:00

SQL Apprentice Question
CREATE TABLE #tmp1(rowID int identity(1,1), dateCol1 datetime, dateCol2
datetime, dateCol3 datetime)

INSERT INTO #tmp1(dateCol1, dateCol2)
SELECT '1/1/05', '2/1/05'
UNION ALL SELECT '3/1/05', '3/7/05'
UNION ALL SELECT '4/1/05', '3/20/05'
UNION ALL SELECT '5/1/05', '5/13/05'

UPDATE #tmp1 SET dateCol3 = ?

If dateCol1 > dateCol2 then update dateCol3 to dateCol1
else
update #tmp1 Set dateCol3 to dateCol2

I am sure this is not the most normalized example, but what would be the
tSql to update my table with the latest date in a row without having to do it
in 2 queries?

I realize I could say

update #tmp1 set datecol3 = datecol1 where datecol1 > datecol2

and then

update #tmp1 set datecol3 = datecol2 where datecol2 > datecol1

Is there a way to do this in one query statement? What would that look like?

Thanks,

Celko Answers
If you followed ISO-11179 data element naming rules, avoided
needlessly proprietary code, and put your date into the proper format
for Standard SQL, would your posting look like this?

CREATE TABLE Foobar
(foobar_id INTEGER NOT NULL PRIMARY KEY,
col1_date DATETIME NOT NULL,
col2_date DATETIME NOT NULL,
col3_date DATETIME);

INSERT INTO Foobar(foobar_id, col1_date, col2_date)
VALUES (42, '2005-01-01', '2005-02-01');
Etc.

IF col1_date > col2_date
THEN update col3_date to col1_date
ELSE update col3_date to col2_date
<< col3_date =" CASE"> col2_date
THEN col1_date
ELSE col2_date END;

You have a few choices here.
1) You can make col3_date into a computed column in proprietary syntax
-- look it up.
2) You can use the CASE expression to update col3_date; "UPDATE Foobar
SET col3_date = CASE.. END;" Of course this still means that you have
materialized computed data in violate of good design.
3) You can put it in a VIEW, and drop col3_date from the base table.
The code will always be right and portable.

Original source

How to retrieve all records from 30th record to 50th record of a table?

2007-10-04T13:14:00.000-04:00

SQL Apprentice Question
I have a table named myTable. It is sorted by some column and there is
no primery key or unique column.I want to retrieve all records from
30th record to 50th record. How can I do this by a sql statement?

Celko Answers
>> I have a table named myTable. It is sorted by some column and there is no primary key or unique column. <<

BY DEFINITION:
1) Tables have at least one key
2) Tables have no ordering

>> I want to retrieve all records from 30th record to 50th record. <<

1) Rows are not anything like records
2) Tables have no ordering

Please read a book --ANY book -- are RDBMS. You have gotten every
basic concept wrong.

Original Source

No idea where to start with Query

2007-10-04T13:03:00.000-04:00

SQL Apprentice Question
I need some help with a query using SQL 2005 and I really don't know
where to start.
I have 3 columns: Date(dateTime), Name(varchar), Value(decimal)
I need to retireive 3 values, one query or all, or one query for
each. It makes no difference to me.

1)For each Name I need to get the difference of current day's value
and previous day's value
(TodayValue- PreviousDayValue)

2)For each Name I need to get the sum of difference of yesterday's
value and today's value for the current month
So something like (Day1Value - Day2Value) + (Day2Value - Day3Value)
+...+ (Day29Value - Day30Value)

3)Same as #2 but just for the current year.

Any help would be greatly appreciated.

Celko Answers
The best place to start is with 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. 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. What you did post was a pile
of vague names and/or reserved words. Let's make it real and sensible:

CREATE TABLE DogSchedule
(walk_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
dog_name CHAR(15) NOT NULL,
walk_kilometers DECIMAL (5,2) NOT NULL,
PRIMARY KEY (walk_date, dog_name));

1)For each dog_name I need to get the difference of current day's
value and previous day's value <<

You did not tell us if you are sure that all days are represented in
the table? Only one walk per day? What constraint enforces that
business rule? My point is that SQL is an integrated whole -- you
cannot separate DDL and DML; they must work together!

Next, you are going to be using the delta from day to day, so let's
put that in a VIEW.

CREATE VIEW DeltaDogWalks (walk_date, dog_name, delta_kilometers)
AS
SELECT D2.walk_date, D2.dog_name,
(D2.walk_kilometers - D1.walk_kilometers)
FROM DogSchedule AS D1, DogSchedule AS D2
WHERE D1.dog_name = D2.dog_name
AND D2.walk_date = DATEADD(DD, 1, D1.walk_date);

To sum the deltas, set up a report periods table that you can adjust:

CREATE TABLE ReportPeriods
(period_name CHAR(15) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK(start_date < end_date));

Now use a "walk_date BETWEEN start_date AND end_date" to group on the
names of the reporting periods.

Original source

A Strange Query Options

2007-08-09T09:08:00.000-04:00

SQL Apprentice Question
Hi guys,

I'm trying to work this query out in my head see if you can help me with it.
I'll give you a little background information so you have the big picture.
This is a real estate database, every home has an APN number it's a unique
number that always means the same property. When a company wants to list a
home on the MLS to sell the home it gets an MLS number which is only unique
to the instance the entity wants to sell that property, so in a span of a
few years the same APN could go for sale several times and have several MLS
Numbers, but maintain the same APN. This database is setup so each row
is an MLS Number.

I need to make a list of all entries that qualify:

1. Find APN's with multiple entries

2. Narrow it down to only solds

3. Only solds that have been modified in the last 3 years

and it needs to generate a list of just DISTINCT APN's that qualify.

Thanks,

Celko Answers
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. We don't even know your data types, column or table
names much less your codes! 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.

>> This is a real estate database, every home has an APN number it's a unique number that always means the same property. <<

CREATE TABLE Properties
(apn INTEGER NOT NULL PRIMARY KEY,
etc);

>> When a company wants to list a home on the MLS to sell the home it gets an MLS number which is only unique to the instance the entity wants to sell that property, so in a span of a few years the same APN could go for sale several times and have several MLS Numbers, but maintain the same APN. <<

CREATE TABLE Listings
(apn INTEGER NOT NULL
REFERENCES Properties(apn)
ON DELETE CASCADE
ON UPDATE CASCADE,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
CHECK (start_date < end_date),
PRIMARY KEY (apn, start_date),
mls INTEGER NOT NULL,
listing_status CHAR(10) DEFAULT 'listed' NOT NULL
CHECK (listing_status IN ('listed', 'sold', 'reduced', etc)),
asking_price DECIMAL(12,2) NOT NULL,
etc);

You also need a Calendar table, since this is a history schema. Google
that.

>> I need to make a list of all entries that qualify:

1. Find APN's with multiple entries
2. Narrow it down to only solds
3. Only solds that have been modified in the last 3 years and it
needs to generate a list of just DISTINCT APN's that qualify. <<

Did you notice that you gave a step by step **procedure**, rather than
a **declarative statement**? You do not thinking SQL yet. This is
steps 1 and 2 as a query:

SELECT apn, COUNT(*) AS listed_cnt
FROM Listings
WHERE listing_status = 'sold'
GROUP BY apn
HAVING COUNT(*) > 1;

Step 3 is impossible with what you posted. What does "modified" mean
and where is it in the tables? I assume with the Properties, which
means I need a history schema on it, too. My mental image is that
"modified" is going to involve the bedroom counts, kitchen appliances,
roofing, etc. and NOT one column with a simple code in it.

Boolean computed column

2007-07-13T12:43:00.000-04:00

SQL Apprentice Question
I have a column in my database I want to be a computed flag based on an
active date.. the Flag column is called active..

basicall I want if the ActiveDate <= getdate() (as in today is after the
item was active) then the active flag is a bit 1, else its a bit 0... I
tried this as a flat out <= statement, got an error by SQL Managment
studio... so how would this be done? thanks!

Celko Answers
>> I have a column in my database I want to be a computed flag based on an active date.. the Flag column is called active.. basically I want if the ActiveDate <= getdate() (as in today is after the item was active) then the active flag is a bit 1, else its a bit 0...
I tried this as a flat out <= statement, got an error by SQL
Management studio... so how would this be done? <<

This would be done by forgetting all the basic rules for programming
in SQL :)

1) We do not use the proprietary BIT data type that does not exist in
Standard SQL

2) Flags are for assembly language programming and have no place in
RDBMS; that is why SQL has no BOOLEAN data types

3) We do not store computed columns in a table. Write a VIEW and
learn to use CURRENT_TIMESTAMP instead of the old proprietary
getdate() function call to get today's active data.

Original Source

SQL Views - embedded view work-a-rounds

2007-07-13T12:39:00.000-04:00

SQL Apprentice Question
I've been asked to re-write a sql view. The view itself contains
several calls to other views (embedded). Is there a way to get around
using embedded views. I've written the same query up using temp.
tables but obviously temp. tables can't be used in views?

Is there any special things I should be looking for?

Celko Answers
>> I've been asked to re-write a sql view. The view itself contains several calls[sic: invocations?] to other views (embedded). Is there a way to get around using embedded views. I've written the same query up using temp tables but obviously temp. tables can't be used in views? <<

Nesting VIEWs is a good progrmming practice when it is done right. It
can assure that nobody invents their own definition of something, like
how we compute a tricky formula that can send us all to prison.

Temp tables are a baaaaad idea. The SQL Server model is in violation
of ANSI/ISO and most everyone else's model of them. They are usually
a way to fake a "scratch tape" in a procedural solution, where each
step passes the tape to the next step in a process; SQL is declarative
and we want to write that way.

But the real point is that you never said *why* you want to re-write
this unnamed VIEW. Damn to give advise about anything without any
kind of spec at all ..

real world advise on temp tables please

2007-07-13T12:35:00.000-04:00

SQL Apprentice Question
a system with around 1500 users. a "bad" stored procedure that writes 3
#temp_tables. I notice system slow downs when more than 5 - 10 people run the
SP at the same time. Is this to be expected. what are the real world
expectancies of the temdb?

Celko Answers
>> a "bad" stored procedure that writes 3 #temp_tables. I notice system slow downs when more than 5 - 10 people run the SP at the same time. Is this to be expected. what are the real world expectancies of the temdb? <<

Yes, it is expected. The best solution is to re-write the procedure
to use derived tables and subqueries.

Besides being proprietary in both syntax and implementation the # temp
tables are usually a sign of bad programming. They are used as
"scratch tapes" in a routine structured as if it were a 1950's mag
tape batch; each step of a sequential process is written to a scratch
tape (aka # temp table) to be passed to the following step.

Remember coupling and cohesion from that freshman S.E. course?
Temporal coupling?

I have problem with union

2007-06-23T10:46:00.000-04:00

SQL Apprentice Question
use Sql Server 2000. I've structure like it:

Create table T1(
RID int primary key,
A varchar(8),
...
...
)

Create table T2(
RID int primary key,
A varchar(8),
...
...
)

To make easy to view, so I make a view like it

Create view VUni as
Select RID, A From T1
union

Select RID, A From T2

Now, when I execute

Select * From VUNI Where RID between 15 and 25

So the process needs a lot of times alias Very Slow. How to make it best
performance?

Oh yeach, it's other question, I want to select data from table which its
name is name dimanically (variable)

IF @YEAR=2000 Select * FROM T2000
ELSE
IF @YEAR=2001 Select * FROM T2001
ELSE
IF @YEAR=2002 Select * FROM T2002
ELSE
...
...

To make it compact, So how to the code?

Next question, How to avoid error? I execute like it

Select a/b from T2

seldom the statement is error, because value of filed "a" is 0. I want if
a/b is error calculation, so it give value 0 automatically. Can I do it?
Without I ve to make a function? Or any function (built in) to handle it?

Celko Answers
>> I want to select data from table whose name is dynamic (variable) <<

IF @year=2000 SELECT * FROM T2000
ELSE
IF @year=2001 SELECT * FROM T2001
ELSE
IF @year=2002 SELECT * FROM T2002
ELSE
...
<<

This design flaw is so bad it has a name, like a disease: "Attribute
Splitting". Instead of separate tables, you need one table with a
"_year" column in it. You then use VIEWs or queries.

You probably also split an attribute in the first part of this
posting, and are trying to fix it with a UNION [ALL] construct.

Original Source

Working days calendar in T-SQL

2007-06-23T10:39:00.000-04:00

SQL Apprentice Question
My company working days is from Monday to Friday.
I would like to generate a result set which show all
consecutive working days (Monday to Friday excluding the
weekends) that is, for June 2007, it show me 1st, 4th, 5th,
6th, 8th, 11th etc... until 29th (last working day of June). Is
it possible to do this with T-SQL?My company working days is from Monday to Friday.
I would like to generate a result set which show all
consecutive working days (Monday to Friday excluding the
weekends) that is, for June 2007, it show me 1st, 4th, 5th,
6th, 8th, 11th etc... until 29th (last working day of June). Is
it possible to do this with T-SQL?

Celko Answers
Build a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year INTEGER NOT NULL,
fiscal_month INTEGER NOT NULL,
week_in_year INTEGER NOT NULL, -- SQL server is not ISO standard
holiday_type INTEGER NOT NULL
CHECK(holiday_type IN ( ..), --
day_in_year INTEGER NOT NULL,
julian_business_day INTEGER NOT NULL,
...);

The Julian business day is a good trick. Number the days from
whenever your calendar starts and repeat a number for a weekend or
company holiday.

A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day weekends:

http://www.smart.net/~mmontes/ushols.html

Time zones with fractional hour displacements http://www.timeanddate.com/worldclock/city.html?n=246 http://www.timeanddate.com/worldclock/city.html?n=176 http://www.timeanddate.com/worldclock/city.html?n=5 http://www.timeanddate.com/worldclock/city.html?n=54

But the STRANGE ones are:
http://www.timeanddate.com/worldclock/city.html?n=63 http://www.timeanddate.com/worldclock/city.html?n=5

Original Source

group by datetime

2007-06-16T09:07:00.000-04:00

SQL Apprentice Question
I am baffled by this query and can use a little help pls!

query count for each Monday of the week in the last few months between 9 pm
and 1 am. I know I can use datepart() but can't figure out how to query
between 9pm and 1am. Thanks.

Celko Answers
>> I am baffled by this query and can use a little help pls! <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Why are you so rude?

>> query count for each Monday of the week in the last few month between 9 pm [sic: 11:00:00 Hrs] and 1 am [sic: 01:00:00 the next day??]. I know I can use DATEPART() but can't figure out how to query between 9 pm [sic] and 1 am [sic]. <<

You really have no idea how time works!! What the hell is that AM and
PM crap? You never heard of ISO-8601 Standards and UTC???

What you do is set up a table of temporal ranges with upper and lower
limits with TIMESTAMP limits and join to it. Hey, you spit on us by
not posting DDL, why should we post DDL and data for you?

Your unit of measurement is wrong and you are getting screwed up. But
your invisible DDL tells us nothing!!

Original Source

Updating based on values of two records

2007-06-16T09:03:00.000-04:00

SQL Apprentice Question
I'm sure this is obvious, but for some reason, I can't get my hands
around the solution. I have a table like this:

Account CalYear CalMonth Amount

Comm 2006 12 80
Comm 2007 01 100
Comm 2007 02 125
Incr 2007 01 21
Incr 2007 02 28

I want to update the incr account so it shows the correct difference
between the previous month, the change basically. Corrected it would
look like this:

Account CalYear CalMonth Amount

Comm 2006 12 80
Comm 2007 01 100
Comm 2007 02 125
Incr 2007 01 20
Incr 2007 02 25

Celko Answers

>> I have a table like this: <<

Why do you spit on us and want us to do your homework/job for you?

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Why did you invent your own two column data types that avoid the
temporal data types? Have you ever read the ISO-8601 rules for
temporal data?

CREATE TABLE StinkingFoobar -- until we get a real name
(acct_type CHAR(4) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
squid_amt INTEGER NOT NULL, -- read ISO-11179 before you program
again!!
PRIMARY KEY (acct_type, start_date)
);

Did I guess right?? Gee, wish you had helped!!

>> I want to update the incr account so it shows the correct difference between the previous month <<

No. That is soooooo non-RDBMS!! This is a computed value and needs
to be done in a VIEW using the OLAP functions. RTFM.

Original Source

newbie question on update

2007-06-16T09:00:00.000-04:00

SQL Apprentice Question
I have a clients table which consists of, among other things, a column
named source.

I have a second table, sources, with two columns - client_id and source.

The are fewer rows in sources than in clients. I am trying to update
clients.source with sources.source without affecting the clients that
do not have a corresponding row in sources.

My first try was update clients set clients.source = sources.source
where clients.client_id = sources.client_id

But I ended up with nulls in the clients.source column for rows in
which there was no row in sources.

I am guessing that I need to involve a join somehow, but I am having a
very hard time not thinking procedurally, and just don't "get" it.

I've been looking at Join examples, but anything non-trivial just
baffles me. Would someone be so kind as to explain how to do what I'm
trying to do?

Thanks.

Celko Answers
>> I am finding it difficult to think in terms of sets and 'simultaneous' actions. I keep wanting to solve these things procedurally. <<

It takes about a year to have the revelation :) My next book will
deal with thinking in sets. Hey, remember how weird recursion was?

Original Source

Question regarding multiple data sources and coalesce

2007-06-14T08:55:00.000-04:00

SQL Apprentice Question
I inherited a db that is pulling data from 5 different sources and
storing them in 5 different tables. The tables all have the same
basic data, minor differences based on source. The company currently
creates a "summary" table by joining the 5 source tables and using
coalesce( ) to display data from preferred data sources if it is
available from there.

I've been asked to present an alternate schema. My first thought was
to normalize the 5 tables into 1 with a source andI could just include
the source in the ORDER BY of queries to get the preferred source.
But then I thought since each source could have 30Million rows maybe I
would loose performance over the existing schema. So, could somebody
point me towards a reference source that may cover this type of
topic? Of course, any opinions (on this issue) would be appreciated
as well.

Celko Answers

>> I've been asked to present an alternate schema. My first thought was to normalize the 5 tables into 1 with a source and I could just include the source in the ORDER BY of queries to get the preferred source. <<

Something ike this? Cram all the data that you have into staging
table

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL,
source_nbr INTEGER NOT NULL
CHECK (source_nbr BETWEEN 1 AND 5),
PRIMARY KEY (foo_key, source_nbr),
etc.);

Assuming sources are ranks from 1 to 5, pull out the most trusted for
each key

SELECT foo_key, etc.
FROM Foobar AS F1
WHERE source_nbr =
(SELECT MIN(source_nbr)
FROM Foobar AS F2
WHERE F1.foo_key = F2.foo_key);

This is the best we can do without more specs.

Original Source

Need urgent help on a QUERY

2007-06-14T08:52:00.000-04:00

SQL Apprentice Question
Given:

SELECT STATE_ID
FROM GROUP_STATE

STATE_ID
----------------
10
15
16
17
18
19
20

SELECT GROUP_ID
FROM GROUP
WHERE GROUP_ID NOT IN (SELECT DISTINCT GROUP_ID FROM GROUP_STATE)

GROUP_ID
---------------
1
2
16
5

I need a query that would look like:
GROUP_ID STATE_ID
--------------- ----------------
1 10
1 15
1 16
1 17
1 18
1 19
1 20

2 10
2 15
2 16
2 17
2 18
2 19
2 20

ETC....

Celko Answers
SELECT GS.state_id, G.group_id
FROM ( SELECT state_id FROM GroupStates AS GS
CROSS JOIN
SELECT group_id FROM Groups
WHERE group_id
NOT IN (SELECT DISTINCT group_id FROM GroupStates)
AS G);

Do not use reserved words for data element names.

Original Source

changing order of records

2007-06-14T08:50:00.000-04:00

SQL Apprentice Question
hi, i've got a problem. i need to change order of records in my table . here
is the table

id forumname
----------------
1 test1
2 test2
3 test3
4 test4

i need to write code that can move nay record up or down without changing
id of forumname. i thought about another column called order

id forumname order
---------------------
1 test1 1
2 test2 2
3 test3 3
4 test4 4

but couldnt figureout how to change that order column. any one got an idea?

Celko Answers
Here is an old "cut & paste" for this problem: Given a motorpool with
numbered parking spaces, you want to move the automobiles around.
CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(17) NOT NULL);

Re-arrange the display order based on the parking_space column:

CREATE PROCEDURE SwapVehicles (@old_parking_space INTEGER,
@new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space
WHEN @old_parking_space
THEN @new_parking_space
ELSE parking_space + SIGN(@old_parking_space - @new_pos)
END
WHERE parking_space BETWEEN @old_parking_space AND
@new_parking_space
OR parking_space BETWEEN @new_parking_space AND
@old_parking_space;

When you want to drop a few rows, remember to close the gaps with
this:

CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);

Original Source

self-referencing constraint with identity column?

2007-06-13T06:59:00.000-04:00

SQL Apprentice Question
have a table that requires to have a self-referencing constraint to enforce
a parent-child type of relationship:

ID (identity column)
ParentID (INT column that references the ID column).

To determine if I'm at the top-level of of the relationship, I was going to
leave the ParentID null, otherwise it must be a value of another ID column in
the table which indicates its child of another.

However, can you have it so you don't use NULL to indicate this and instead
set ID and ParentID equal to the same value and still use the IDENTITY column?

So on a INSERT (i.e the IDENTITY would generate 25, so I'd like to set the
ParentID to 25 as well). This would was causing FK violation and I was
wondering if there is a way around it using an insert trigger?

Celko Answers

>> I have a table that requires to have a self-referencing constraint to enforce a parent-child type of relationship: <<

No it is not required at all. Look up the Nested Sets model for trees
and hierarchies. You will find the constraitns are much easier and the
code will run1-2 orders of magnitude faster than recursive path
traversals.

Also, never use IDENTITY in an RDBMS; find a valid relational key
instead.

Original Source

Database Design Question

2007-06-12T06:13:00.000-04:00

SQL Apprentice Question
I'm fairly new to database design, having only really worked with
existing tables etc in the past.

Simple question this really...........

In a users table, is it wise to have a ManagerID column that is
effectively pointing to another user in the same table, with the
theory being that if that person is top dog, they will just have a
null entry in ManagerID.

I will check for circular references before entering the data.

Is there a better way of storing the data, or is this the only way?

Celko Answers
>> Is there a better way of storing the data, or is this the only way? <<

Get a copy of TREES & HIERARCHIES IN SQL for several different ways to
do an organizational chart. What you have is thd adjacency list
model; look up the nested sets model.

Original Source

Using Soundex to identify possible duplicates

2007-06-12T06:09:00.000-04:00

SQL Apprentice Question
In a table that has person forename and surname, is it possible to use
soundex to identify for every row in the table what similar matches
there are in the same table?

Celko Answers

>> is it possible to use Soundex to identify for every row in the table what similar matches there are in the same table? <<

Don't do it. Name handling is an ugly problem and if you have to do
this on a regular basis get a package designed for this kind of work.
Some companies are Group 1 Software, SSA (used to have a great booklet
on this topic), Melissa Data Corporation and Promark Software Inc.

Their software handles mailing lists and you can get a review copy
from Melissa Data. They do not choke on names like "John Paul van der
Poon" and worse.

Original Source

giving one union preference

2007-06-12T06:06:00.000-04:00

SQL Apprentice Question
Let say I have a query with the following structure:

Select name, mdate, kdate
from table1 a
inner join (
select name, mdate
from table2 b
where a.id = b.id
and mdate >= kdate
union
select name, mdate
from table3 c
where a.id = c.id
and mdate >= kdate
) mindate

When the select mdate from the inner join union (mindate) returns the
same mdate for table b and c, I want to give preference to the c.mdate
and so c.name.

Is there a way to do this?

Celko Answers
>> When the select mdate from the inner join union (mindate) returns the same mdate for table b and c, I want to give preference to the c.mdate and so c.name. <<

This question makes no sense. The poorly named derived table,
Mindate, acts as if it is materialized and you no longer have access
to tables B and C. It is like asking to see the eggs after the cake
has been baked.

Original Source

Help writing query to return data if there are 4 consecutive holidays

2007-06-12T06:04:00.000-04:00

SQL Apprentice Question
I need to write a query that returns data if an employee has taken four or
more consecutive holidays. I need to get the startdate and the employee name.
The employee works either a 4X10..or an 8X5 shift...
Also, the employee need not have an off only on Sat and Sun...Some of them
work on the weekends and have an off during the weekdays.

Also, I need to take into account the days employee is off (eg.Sat and Sun).

So if an employee is out Mon, Tues, Wed, Thu it shows that employee or if
the employee is out on Thu, Fri, Mon, Tues it also shows that employee...

Can anyone assist!

Celko Answers
Use a Calendar table which has the work days Julianized (consecutively
numbered). Google this newsgroup for examples.

Original Source

add blank row between groups of rows to separate them?

2007-06-12T06:00:00.000-04:00

SQL Apprentice Question
I want to add a blank row between groups of rows to separate them.

select * from tbl1 where fname in ('Bill', 'tom', 'sam')

returns

Bill, 1, 2, 3
bill 4, 5, 6
tom, 1, 2, 3
tom, 4, 5, 6
sam, 1, 2, 3
sam, 4, 5, 6

I want to return this:

Bill, 1, 2, 3
bill 4, 5, 6

tom, 1, 2, 3
tom, 4, 5, 6

sam, 1, 2, 3
sam, 4, 5, 6

How can I perform this type of operation with tsql?

Celko Answers
>> How can I perform this type of operation with tsql? <<

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just
SQL and RDBMS.

Original Source

Calc minutes between days

2007-06-11T09:18:00.000-04:00

SQL Apprentice Question
I am using DATEDIFF('n',EndTime, StartTime) to return minutes between 2
datetime columns. The problem is that when the StartTime is 23:00:00 PM and
EndTime is 07:00:00 AM I get a negative # of minutes. How can get 480
minutes from this calculation. Note the 23:00:00 is yesterday and the
07:00:00 is today. Thanks

Celko Answers

>> OK, but I'm not storing the date portion so it thinks both dates are 12/30/1899. Do I need a CASE statement to check for 1st time 2nd time? <<

No, you need to learn to do it right and not look for kludges in
newsgroups. Oh, you will get the kludges, as you have seen, but then
your code will become a convoluted mess over time, queries cannot use
indexes, portability is destroyed, etc.

It would also help if you had learned to use ISO-8601 temporal
formats, too (i.e '1899-12-30'). That would have lead you to study
the ISO temporal model. A proper data model will use DATETIME data
types and model events as having durations. Get a free copy of Rick
Snodgrass book in PDF from the University of Arizona website and spent
a week with. There is also a ton of free code you can download.

Get info from multiple tables. Join problem? Options

2007-06-11T09:12:00.000-04:00

SQL Apprentice Question
The Stored Procedure below returns article attributes for my articles
(tblArticleAttribute). This works fine IF NOT tblArticleAttribute.Content is
empty, then it returns all templatedefinitions with the Content. But, if the
Content is empty, it also has to return all template definitions, but with
the content fields empty (now it returns nothing). This is because I bind
this information to a dynamic form, and if (example) the title or ingress is
empty the textfields (definitions) should be shown with no text. Then the
user can fill inn text an save it.

----------------------------------
Here is the SQL:
----------------------------------

SELECT tblTemplateDefinition.[Name] as TemplateDefinitionName,
tblTemplateDefinition.HelpText as TemplateDefinitionHelpText,
tblArticleAttribute.[Content], tblArticle.Id AS ArticleId,
tblTemplateDefinition.Id AS TemplateDefinitionId
FROM tblTemplateDefinition
LEFT OUTER JOIN tblArticleAttribute ON tblTemplateDefinition.Id =
tblArticleAttribute.TemplateDefinitionId
INNER JOIN tblArticle ON tblArticleAttribute.ArticleId= tblArticle.Id
WHERE tblArticle.Id = @ArticleId

----------------------------------
Here is the tables:
----------------------------------

tblArticle:
id | Name

tblArticleAttribute
TemplateDefinitionId | ArticleId | Content

tblTemplate
Id | Name

tblTemplateDefinition
Id | TemplateId | TemplateDefinitionId |Name | HelpText

Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You might want to read any basic book on data modeling. You seem to
have multiple names for the same data element, based on where it
appears. And you have a universal, magical "id" column in all the
pseudo-code you did give us. You also violate ISO-11179 naming rules.
My guess as to what you meant to post if you knew how would be
something like this:

CREATE TABLE Articles:
(article_id INTEGER NOT NULL PRIMARY KEY, -- needs work
article_title VARCHAR(150) NOT NULL);

I have no idea which carefully researched industry standard you are
using the article identifier. But at least I know it is not an
IDENTITY column -- that would be soooo non-relational.

Why did you use a singular name for the table? Do you really have
only one article. Why did you put that silly "tbl-" prefix on the
table names? Just to mess up the data dictionary, or because you are
an OO programmer who has not learned RDBMS yet?

CREATE TABLE ArticleAttributes
(template_id INTEGER NOT NULL PRIMARY KEY,
article_id INTEGER NOT NULL
REFERENCES Articles(article_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
article_content VARCHAR(255) NOT NULL);

We would never store attributes in a schema; that would mix data and
metadata. What about the templates? Why is the definition of a
template not an attribute of a template? Why it is an entity with its
own table?

Also note that you need DRI actions, constraints, etc. to make this
work.

I can make a lot of assumptions about the magical universal names and
how they are related (is the magical "id" actually article_id,
template_id, template_definition_id, template_id, or a hundred other
possible things?) and the cardinality of the relationships, etc., but
based on decades of cleaning up bad schemas, you need more than a
sample query with an OUTER JOIN.

Can you explain your problem better?

Maximum number of tables (260) exceeded

2007-06-07T11:50:00.000-04:00

SQL Apprentice Question
Hi! I receive the referenced message when i run a view I created in SQL2005
in a SQL2000 install of the same database. Basically it is a union query with
about 9 individual select queries. I am sure this has something to do with a
lack of efficiency in my query structure but I am confounded that it runs in
SQL2005 and not in SQL2000. Any suggestions? Thanks in avance for any help
you can offer...

Celko Answers
>> I am confounded that it runs in SQL2005 and not in SQL2000. Any suggestions?

Why not just write better code? Seriously, after all my decades (NOT
years) with SQL, I cannot imagine anything properly designed that
would break the limit.

Remember the human interface part of the first SE course you had
before you were allowed to production data? 5 to 7 levels is all a
human being can maintain. Duh.

Find the moron who nested things this deep and kill him. Then re-
write EVERYTHING he wrote and re-write it. Do a Wikipeida on
"spaghetti code" versus "lasagna code" for some of the issues.

Original Source

db table design question

2007-06-04T05:44:00.000-04:00

SQL Apprentice Question
We have an application which has its settings stored in a database
(SQL Server 2000)

We have (strong) disagrements on the best way to store these values,
and I am looking for advice on doing this.
Most of the values are not used by the stored procedures, just the
applications running on the clients PC. The data rarely changes.

Developer #1 wants many tables with many columns. This gives the
ability to strongly type each variable. Each table would have only
one row. He believes this to be faster in retrieving the data.

Developer #2 wants one table with a Key column and a Value column.
This table would have many rows. The application would be
responsible
for detecting invalid datatype in the value. He believes this to be
a
more efficent use of the database, and easier to maintain.

So - what do you think? Are there any advantages or problems with
either approach?

Celko Answers
Use #1. The other way is called a OTLT ("One True Lookup Table") or
MUCK ("Massively Unified Code Keys") in the literature. It is
incredibly bad design. Here is a cut and paste from one of my books
on Constants tables

04.02. Constants Table

When you configure a system, you might want to have a way to set and
keep constants in the schema. One method for doing this is to have a
one-row table that can be set with default values at the start, and
then updated only by someone with administrative privileges.

CREATE TABLE Constants
(lock CHAR(1) DEFAULT 'X'
NOT NULL PRIMARY KEY
CHECK (lock = 'X'),
pi FLOAT DEFAULT 3.142592653 NOT NULL,
e FLOAT DEFAULT 2.71828182 NOT NULL,
phi FLOAT DEFAULT 1.6180339887 NOT NULL,
..);

To initialize the row, execute this statement.

INSERT INTO Constants VALUES DEFAULTS;

Most SQL programmers do not know about the VALUES DEFAULTS option in
the INSERT INTO statement. The lock column assures there is only one
row and the DEFAULT values load the initial values. These defaults
can include the current user and current timestamp, as well as numeric
and character constant values.

Another version of this idea that does not allow for any updates is a
VIEW defined with a table constructor. [[not in SQL Server yet!]]

CREATE VIEW Constants (pi, e, phi, ..)
AS VALUES (CAST 3.142592653 AS FLOAT),
(CAST 2.71828182 AS FLOAT),
(CAST 1.6180339887 AS FLOAT),
..;

Please notice that you have to use a CAST() operators to assure that
the data types are correct. This is not a problem with INTEGER
values, but can be if you wanted DOUBLE PRECISION and got a default of
DECIMAL(s, p) or FLOAT.

[[ a little bit later in the chapter..]]

OTLT or MUCK Table Problems

I think that Paul Keister was the first person to coin the phrase
"OTLT" (One True Look-up Table) for a common SQL programming technique
that is popular with Newbies. Don Peterson (www.SQLServerCentral.com)
gave the same technique the name "Massively Unified Code-Key" or MUCK
tables in one of his articles.

The technique crops up time and time again, but I'll give him credit
as the first writer to give it a name. Simply put, the idea is to
have one table to do all of the code look-ups in the schema. It
usually looks like this:

CREATE TABLE Look-ups
(code_type CHAR(10) NOT NULL,
code_value VARCHAR(255) NOT NULL, -- notice size!
code_description VARCHAR(255) NOT NULL, -- notice size!
PRIMARY KEY (code_value, code_type));

So if we have Dewey Decimal Classification (library codes), ICD
(International Classification of Diseases), and two-letter ISO-3166
country codes in the schema, we have them all in one, honking big
table.

Let's start with the problems in the DDL and then look at the awful
queries you have to write (or hide in VIEWs). So we need to go back
to the original DDL and add a CHECK() constraint on the code_type
column. Otherwise, we might "invent" a new encoding system by
typographical error.

The Dewey Decimal and ICD codes are digits and have the same format --
three digits, a decimal point and more digits (usually three); the
ISO-3166 is alphabetic. Oops, need another CHECK constraint that will
look at the code_type and make sure that the string is in the right
format. Now the table looks something like this, if anyone attempted
to do it right, which is not usually the case:

CREATE TABLE OTLT
(code_type CHAR(10) NOT NULL
CHECK(code_type IN ('DDC','ICD','ISO3166', ..),
code_value VARCHAR(255) NOT NULL,
CHECK
(CASE
WHEN code_type = 'DDC'
AND code_value
SIMILAR TO '[0-9][0-9][0-9].[0-9][0-9][0-9]'
THEN 1
WHEN code_type = 'ICD'
AND code_value
SIMILAR TO '[0-9][0-9][0-9].[0-9][0-9][0-9]'
THEN 1
WHEN code_type = 'ISO3166'
AND code_value SIMILAR TO '[A-Z][A-Z]'
THEN 1 ELSE 0 END = 1),
code_description VARCHAR(255) NOT NULL,
PRIMARY KEY (code_value, code_type));

The "SIMILAR TO" predicate is the SQL-92 version of a regular
expression parser based on the POSIX Standards, if you are not
familiar with it. Since the typical application database can have
dozens and dozens of codes in it, just keep extending this pattern for
as long as required. Not very pretty is it? In fact, there is a good
chance that you will exceed the number of WHEN clauses allowed in a
CASE expression. That's why most OTLT programmers don't bother with
it.

Now let us consider adding new rows to the OTLT.

INSERT INTO OTLT (code_type, code_value, code_description)
VALUES
('ICD', 259.0, 'Inadequate Genitalia after Puberty');

and also

INSERT INTO OTLT (code_type, code_value, code_description)
VALUES
('DDC', 259.0, 'Christian Pastoral Practices & Religious Orders');

If you make an error in the code_type during insert, update or delete,
you have screwed up a totally unrelated value. If you make an error
in the code_type during a query, the results could be interesting.
This can really hard to find when one of the similarly structured
schemes had unused codes in it.

The next thing you notice about this table is that the columns are
pretty wide VARCHAR(n), or even worse, that they are NVARCHAR(n) which
can store characters from a strange language. The value of (n) is
most often the largest one allowed in that particular SQL product.

Since you have no idea what is going to be shoved into the table,
there is no way to predict and design with a safe, reasonable maximum
size. The size constraint has to be put into the WHEN clause of that
second CHECK() constraint between code_type and code_value. Or you
can live with fixed length codes that are longer or shorter than what
they should be.

These large sizes tend to invite bad data. You give someone a
VARCHAR(n) column, and you eventually get a string with a lot of white
space and a small odd character sitting at the end of it. You give
someone an NVARCHAR(255) column and eventually it will get a Buddhist
sutra in Chinese Unicode.

Now let's consider the problems with actually using the OTLT in a
query. It is always necessary to add the code_type as well as the
value which you are trying to look-up.

SELECT P1.ssn, P1.lastname, .., L1.code_description
FROM OTLT AS L1, Personnel AS P1
WHERE L1.code_type = 'ICD'
AND L1.code_value = P1.disease_code
AND ..;

In this sample query, you need to know the code_type of the Personnel
table disease_code column and of every other encoded column in the
table. If you got a code_type wrong, you can still get a result.

You also need to allow for some overhead for data type conversions.
It might be more natural to use numeric values instead of VARCHAR(n)
for some encodings to ensure a proper sorting order. Padding a string
of digits with leading zeros adds overhead and can be risky if
programmers do not agree on how many zeros to use.

When you execute a query, the SQL engine has to pull in the entire
look-up table, even if it only uses a few codes. If one code is at
the start of the physical storage, and another is at the end of
physical storage, I can do a lot of caching and paging. When I update
the OTLT table, I have to lock out everyone until I am finished. It
is like having to carry an encyclopedia set with you when all you
needed was a magazine article.

Now consider the overhead with a two-part FOREIGN KEY in a table:

CREATE TABLE EmployeeAbsences
(..
code_type CHAR(3) -- min length needed
DEFAULT 'ICD' NOT NULL
CHECK (code_type = 'ICD'),

code_value CHAR(7) NOT NULL, -- min length needed
FOREIGN KEY (code_type, code_value)
REFERENCES OTLT (code_type, code_value),
..);

Now I have to convert the character types for more overhead. Even
worse, ICD has a natural DEFAULT value (000.000 means "undiagnosed"),
while Dewey Decimal does not. Older encoding schemes often used all
9's for "miscellaneous" so they would sort to the end of the reports
in COBOL programs. Just as there is no Magical Universal "id", there
is no Magical Universal DEFAULT value. I just lost one of the most
important features of SQL!

I am going to venture a guess that this idea came from OO programmers
who think of it as some kind of polymorphism done in SQL. They say to
themselves that a table is a class, which it isn't, and therefore it
ought to have polymorphic behaviors, which it doesn't.

Maybe there are good reasons for the data modeling principle that a
well-designed table is a set of things of the same kind instead of a
pile of unrelated items.

Original Source

Find first available block that does not intersect a range

2007-06-04T05:40:00.000-04:00

SQL Apprentice Question
Given a number of numeric ranges, I need to find the first available row
(range) that can accomodate a requested block of contiguous values. For
example, given the following table definition:

CREATE TABLE [dbo].[tbl_AllocatedRange](
[RangeID] [int] IDENTITY(1,1) NOT NULL,
[RangeStart] [bigint] NOT NULL,
[RangeEnd] [bigint] NOT NULL,
CONSTRAINT [PK_tbl_AllocatedRange] PRIMARY KEY CLUSTERED
(
[RangeID] ASC
)

And the following DML:

INSERT INTO [dbo].[tbl_AllocatedRange] ([RangeID], [RangeStart], [RangeEnd])
VALUES ( 1, 100, 200 );

INSERT INTO [dbo].[tbl_AllocatedRange] ([RangeID], [RangeStart], [RangeEnd])
VALUES ( 2, 500, 650 );

INSERT INTO [dbo].[tbl_AllocatedRange] ([RangeID], [RangeStart], [RangeEnd])
VALUES ( 3, 2000, 2200 );

INSERT INTO [dbo].[tbl_AllocatedRange] ([RangeID], [RangeStart], [RangeEnd])
VALUES ( 4, 4000, 8000 );

INSERT INTO [dbo].[tbl_AllocatedRange] ([RangeID], [RangeStart], [RangeEnd])
VALUES ( 5, 16000, 25000 );

INSERT INTO [dbo].[tbl_AllocatedRange] ([RangeID], [RangeStart], [RangeEnd])
VALUES ( 6, 25001, 50000 );

INSERT INTO [dbo].[tbl_AllocatedRange] ([RangeID], [RangeStart], [RangeEnd])
VALUES ( 7, 100000, 200000 );

A request for a block of 10000, should return the row where RangeID=6
because that is the first available position where I could allocate a
contiguous block of 10000 without intersecting any of the values in the
existing ranges.

Celko Answers
Let's add some constraints and reduce those BIGINTs to just INTEGER
unless you really need it.

CREATE TABLE AllocatedRanges
(range_id INTEGER NOT NULL PRIMARY KEY,
range_start INTEGER NOT NULL
CHECK (range_start >= 0),
range_end INTEGER NOT NULL
CHECK (range_end >= 0),
CHECK (range_start <= range_end));

This will get you all of the candidates, but in RDBMS, there is no
concept of an ordering so talking about the "first avaialble" range
needs more definition.

SELECT range_id, (range_end - range_start)+1 AS range_size, @my_size
AS my_size
FROM AllocatedRanges
WHERE (range_end - range_start)+1 >= @my_size;

We can do it with the lowest range id:

SELECT MIN(range_id)
FROM AllocatedRanges
WHERE (range_end - range_start)+1 >= @my_size;

But the usual requirement is to find the best fit, not the first
one.

SELECT range_id
FROM AllocatedRanges
WHERE (range_end - range_start)+1 =
(SELECT MIN((range_end - range_start)+1 - @my_size)
FROM AllocatedRanges AS A1
WHERE ((range_end - range_start)+1 - @my_size) >= 0);

Original Source

mutliple refential integrity

2007-06-04T05:37:00.000-04:00

SQL Apprentice Question
Suppose I want a column in my table to reference two other columns of
other two tables (of course datatypes are same, and the other two
columns are primary key in their respective relations). How can I
enforce this using SQL?

Celko Answers
>> Suppose I want a column in my table to reference two other columns of other two tables (of course data types are same, and the other two columns are primary key in their respective relations). How can I enforce this using SQL? <<-

CREATE TABLE Foo
(foo_id INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE Bar
(_id INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL
REFERENCES Foo(foo_id),
bar_id INTEGER NOT NULL
REFERENCES Bar(bar_id),

Original Source

SQL Query Help

2007-05-23T10:57:00.000-04:00

SQL Apprentice Question
Need to learn SQL queries again (SQL 2005). Need help with the
following situation:

I have two tables, CUSTOMERS and ORDERS. One each column in these
tables should have identical data (except the name of the column is
different) which is customer's name. Because each order creates an
entry in ORDER table, each customer's name may be appearing more than
once in that table. There could be also instances where a particular
customer might not have placed an order at all and will not have any
entry in the ORDERS table, but do have one entry in CUSTOMERS table
(no duplicates in CUSTOMERS table).

I need help with a query which shows a list of customers who have
never placed any orders. This essentially means that they do not have
any entry in the ORDERS table. Here is the Pseudo Code:

select customername from CUSTOMERS table
who are NOT IN
ORDERS table

Will appreciate any quick thoughts. Thanks in advance!

Celko Answers

>> Need to learn SQL queries again (SQL 2005). <<>> I have two tables, CUSTOMERS and ORDERS. One each column in these tables should have identical data (except the name of the column is different) which is customer's name. <<>> I need help with a query which shows a list of customers who have never placed any orders. <<

Wild guess, done without DDL

SELECT C.customer_name
FROM Customers AS C
WHERE NOT EXISTS
(SELECT *
FROM Orders AS O
WHERE O.customer_name = C.customer_name);

Totals

2007-05-23T10:55:00.000-04:00

SQL Apprentice Question
I have a table that is populated everyday with daily totals, I'm
trying to teach myself SQL using this table.

Someone kindly gave me a query that gave the weekly totals based on
the table:
SELECT week_start, week_start + 6 AS week_end, SUM(Total3) AS
production
FROM (SELECT Date - DATEPART(weekday, Date + @@DATEFIRST -
1) + 1 AS week_start, Total3
FROM dbo.A_Totals) AS d
GROUP BY week_start
ORDER BY week_start

How can I adapt this to divide the weekly totals into years so I'd
have: week number, year, year-1,year-2 etc.

Thanks in advance

Celko Answers

>> I have a table that is populated everyday with daily totals, I'm trying to teach myself SQL using this table. <<

Bad idea; get a good book and a tutorial, then work on a problem.

Your approach to SQL is typical of someone coming in via procedural
code and looking for functions to solve it. SQL is a data retrieval
language that likes to use sets (tables). This is a WHOLE DIFFERENT
PROGRAMMING PARADIGM. After you have a few basics, then look up the
use of calendars -- temporal stuff is one of the hardest parts of
RDBMS, so it is a horrible starting point for a self-education. Try
something like this:

CREATE TABLE ReportPeriods
(period_name VARCHAR(20) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));

Create and load a table of reporting periods of whatever range you
want. They can overlap, have gaps, etc. They can be crazy, one shot
things, etc. If you are a Don Martin fan:

INSERT INTO ReportPeriods
VALUES ('National Gorilla Suit Week', 2007-05-21', 2007-05-27');
etc.

You also have to learn to design data which should take about a year,
if you really work at it. So if your weeks are encoding the ISO-8601
year-week number format, then you can simply write:

SELECT period_name, SUM(some_col) AS week_total
FROM DailyReports AS D, ReportPeriods AS R
WHERE D.report_date BETWEEN start_date AND end_date
AND period_name LIKE 'WEEK-2007__;

Change the wild cards as needed.

Correct Way to Insert into Multiple Tables

2007-05-11T08:54:00.000-04:00

SQL Apprentice Question
I am just wondering if what I am doing would be considered the correct
way to insert data into multiple tables when a forigen key is in place
between the tables primary keys.

Here is a simple DB structure
Table 1
ID int (auto incriment) Primary Key
FirstName varchar(100)
LastName varchar(200)

Table 2
ID (Primary Key)
Age int
BirthPlace varchar(100)

I have created the following sotred proceedure, it works from the test I
have done, but I am wondering if it is the *correct* way to do it.

PROCEDURE [dbo].[InsertPerson]
@ID int output,
@FirstName varchar(100),
@LastName varchar(200),
@Age int,
@BirthPlace varchar(100)
AS
Insert into dbo.UserInfo(FirstName, LastName)
Values(@FirstName, @LastName)
SET @ID = SCOPE_IDENTITY()

Insert into dbo.UserDetails(ID, Age, BirthPlace)
Values (@ID, @Age, @BirthPlace)

Celko Answers

>> I am just wondering if what I am doing would be considered the correct way to insert data into multiple tables when a forigen key is in place between the tables primary keys. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

And what you have done is wrong on several points.

1) an auto-increment cannot ever be used as a key in an RDBMS. A key
is a subset of attributes in the data model. An auto-increment is
inside the hardware and has nothing to do with the data model,

2) Do you really have names that long? Well, you will if you allow
it,

3) Why did you split information of what-ever-the-heck you are
modeling across two tables?

4) "age" of what? Ands we never store age; we store a birthdate so we
can always correctly compute the age. Is this what you wanted?

CREATE TABLE Customers
(cust_id CHAR(9) NOT NULL PRIMARY KEY
CHECK (<< validation constraint>>),
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
birth_date DATETIME NOT NULL,
birth_location VARCHAR(20) NOT NULL);

The problem goes away with a valid design. I think what you might
have wanted to know is that INSERT INTO statements work on one and
only one base table. Put both inserts into a single transaction and
add a PRIMARY KEY and FOREIGN KEY constraints.

Last Day Of Previous Month...with a twist

2007-05-11T08:53:00.000-04:00

SQL Apprentice Question
have a requirement to design a query that identifies items sold
between two dates. There is a 'SoldDate' datetime field used to
register what date the item was sold.

The query needs to identify all sales between the last day of the
previous month and going back one year.

What I would like to do is to design a query / stored procedure that
will dynamically create the criteria to allow the client to simply run
the query or stored proc.

I know how to establish the last day of the previous month part, I'm
just not sure of how best to design the remainder of the query.

Celko Answers
>> I know how to establish the last day of the previous month part, I'm just not sure of how best to design the remainder of the query. <<

Instead of using procedural coding, why not use a table of the
reporting periods for a decade or two? A simple BETWEEN predicate
will classify each sale quickly and give you extra control over non-
operating days, etc.

difficulty with SQL to get view

2007-05-11T08:51:00.000-04:00

SQL Apprentice Question
am having difficulty in designing my SQL. :(

In this setup, an Invoice can have multiple Bills (installment
payments). I would like a query that returns invoices with overdue
bills

a bill is overdue if :: NOT B_Paid and Now() > B_DueDate --- how to
put this in the following '???'

SELECT B_Invoice, ??? As Overdue
FROM T_Bill
GROUP BY B_Invoice

CREATE TABLE T_Bill (
RefNo INTEGER IDENTITY PRIMARY KEY,
B_Invoice INTEGER NOT NULL REFERENCES
T_Invoice,
B_BillDate SMALLDATETIME DEFAULT CURRENT_TIMESTAMP
NOT NULL,
B_DueDate SMALLDATETIME DEFAULT (CURRENT_TIMESTAMP
+14) NOT NULL,
B_Paid BIT DEFAULT 0 NOT NULL,
B_PaidDate SMALLDATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
B_PaymentInfo VARCHAR(24) DEFAULT '???' NOT NULL
);

Celko Answers
Your table is not designed properly. Your assembly-style bit flag is
redundant when you have a payment date. IDENTITY cannot be a
relational key by definition and it is redundant given the invoice
number. You never put the table as a prefix on a data element names
because that would change the names from table to table. If the due
date is always 14 days after the billing date, then put compute it in
a VIEW or a query instead of wasting space. This also gives you more
control without having to alter the tables.

CREATE TABLE Billings
(invoice_nbr INTEGER NOT NULL
REFERENCES Invoices (invoice_nbr),
billing_date DATETIME NOT NULL,
PRIMARY KEY (invoice_nbr, billing_date),
paid_date DATETIME, -- null is unpaid
payment_note VARCHAR(24) DEFAULT '???' NOT NULL);

>> I would like a query that returns invoices with overdue bills <<

SELECT invoice_nbr, CURRENT_TIMESTAMP
FROM Billings
WHERE paid_date IS NULL
AND DATEADD (D, billing_date, 14) >= CURRENT_TIMESTAMP;

You need to get a book on basic data modeling. And one on RDBMS.
What you have is a badly designed punch card file system, with flag,
sequential numbering, etc. .

Simple update question

2007-05-08T12:54:00.000-04:00

SQL Apprentice Question
Greetings,
I have a simple update query in Sql 2k and want to update only
one field instead of all 3 fields.

Update customer set
Name = @inName,
City = @inCity
State = Case when @instate is not null then @instate else
State end
Where Customer = @inCust

I would like to know what's the best way to prevent not to update all
3 field when updating only one. If I use the case statement then check
for @instate if not null then itself - would this prevent
from updating the third field. Is there a better way to do this
update. Thanks in advance.

Celko Answers

>> The question is by updating all fields [sic] isn't too much overhead for SQL Server 2k to deal with? <<

You missed one of the major points of SQL. The unit of work is the
entire row and not the columns within a row. This is one of the many,
many ways that newbies confuse rows and records, fields and columns.
This is why we have OLD and NEW (called INSERTED and DELETED in local
dialect) in the SQL update model. Records are updated a field at a
time in file systems.

Stored Procedures---Stacking IFs

2007-05-04T13:31:00.000-04:00

SQL Apprentice Question
What is the proper syntax for stacking these IFs in a Stored Procedure???

Thanks in advance...

IF @Loan_Nbr IS NOT NULL
IF @MTM_Losses_Accrued IS NOT NULL
IF EXISTS ( SELECT 1 FROM [DMD_Data]..[UDF_WARRANTY] WARRANTY

Celko Answers
>> What is the proper syntax for stacking these IFs in a Stored Procedure <<

What the hell are you talking about?? IF-THEN-ELSE-ENDIF constructs in
any procedural programming language are nested or sequential. This
has nothing to why SQL. Don't you know how to program in ANY
language??!!

Please something that makes sense when you are sober

Naming Conventions

2007-05-04T13:27:00.000-04:00

SQL Apprentice Question
I'm coming from a MS Access background and so I'm very used to and
comfortable with the hungarian (Leszynski et al) naming conventions.
However, I'm getting started into my first SQL Server Database and
really want to use the appropriate up to date standard naming
convention (ISO compliant).

I think I have the general idea from raking though countless
conflicting sites and posts, but I'm a bit stuck on what to do
regarding pk / fk naming

For example, in my MS Access world I would have two tables:

tblOrders
=======
strOrderID
intCustomerID
dtOrderDate
...

tblCustomers
==========
intCustomerID
strCustomerName
...

So what would the appropriate and most up-to-date and standard naming
be for SQL Server? My Guess:

Orders
=====
Ord_Order_ID_Pk
Ord_Customer_ID_Fk
Ord_Order_Date
...

Customers
========
Cus_Customer_ID_Pk
Cus_Customer_Name
...

How close (or far) am I from "Celko Proof" naming here?
All help gratefully accepted!

Celko Answers

>> I'm getting started into my first SQL Server Database and really want to use the appropriate up to date standard naming convention (ISO compliant). <<

Get a copy of SQL PROGRAMMING STYLE. Back in the early 1980's I
worked for
AIRMICS (Army Institute for Research in Management Information &
Computer Sciences) and researched code formatting. I based the book
on the ISO-11179 rules and a set of postfixes from Teradata, the other
data warehouse vendor and other meta data projects.

>> I think I have the general idea from raking though countless conflicting sites and posts, <<

Get the book -- I give the DoD, ISO, etc. Standards.

>> .. but I'm a bit stuck on what to do regarding pk / fk naming <<

Same as any other data element; the principle to name it for what it
is. Not for how is implemented. Not for how it is used in one place.

>> For example, in my MS Access world I would have two tables:

tblOrders
=======
strOrderID
intCustomerID
dtOrderDate
...
<<

In a valid data model this would be:

CREATE TABLE Orders -- "tbl-" is silly and redundant
(order_id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
..);

Your prefixes deal with data types (implementations) and not the
nature of the data element. They are also redundant in many cases:
SQL has only one data structure, the Table (derived, CTE, virtual,
base, but still a table) and an order_date does not need a prefix to
repeat the postfix. All those prefixes have done is screw the hell
out of your data dictionary.

CREATE TABLE Customers
( customer_id INTEGER NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL,
..);

>> Ord_Order_ID_Pk

Ord_Customer_ID_Fk
Ord_Order_Date <<

NO! NO! NO! The " Ord_Customer_ID_Fk" is the same data element as
the "Cus_Customer_ID_Pk" and should have the same data element name in
the entire schema. Do you change your fingerprints when you walk from
room to room in your house? Same thing here. Different names would
mean they are logically different data elements and they are not. The
PK- FK- crap is how they are used locally and not what they are by
their nature. You qualify the location with "<table name>.<attribute
name>" when it is not clear.

>> How close (or far) am I from "Celko Proof" naming here? <<

You did get the idea that a table is a collective or plural noun
because it is a set of more than one entity (exception -- if it really
is just one entity, use a singular name, but you do not see many of
those).

And you did not do something really stupid like have a "type_id"
affix. How can an attribute be both an identifier and a type at the
same time? Hey, lets go all out for a "type_id_value_name" postfix!!

And you did not have a magical universal "id" on every table to mimic
a sequential file record number for a physical locater. Such people
are called "ID-iots" in my book :)

I'd give you a solid C+ , but not a C++ :)

Proper Case

2007-05-04T13:25:00.000-04:00

SQL Apprentice Question
I have a ProperCase function that works well. However, I'm constantly
adding "exceptions" into the funciton. For example, I want IPA uppercase,
but not when it's in constIPAtion. This got me to thinking that it might be
easier to add all my cases into a table, rather than constantly editing my
function. But it's not working quite the way I'd expect it to. So rather
than reinvent the wheel, I thought I'd come here and see if anyone has taken
a table approach to ProperCase. If so, can you please share how you did
this?

Celko Answers

>> So rather than reinvent the wheel, I thought I'd come here and see if anyone has taken a table approach to ProperCase. <<

Get a copy of SQL PROGRAMMING STYLE for more than you probably want to
know.. Formatting code was one of the big issues of the day when I
was a researcher at AIRMICS (Army Institute for Research in Management
Information & Computer Sciences) in the early 1980's. A summary is:

1) For people who read languages in a Latin alphabet, the eye tends to
jump to the uppercase letters -- they start sentences and proper
nouns, so they mark "special things" in the language. This is one
reason even MS dropped "camelCase" -- it jerks the eye around,
mentally and physically.

2) Uppercase words are read as Boumas (single units recognized by
shape). That is why the keywords in a program should be uppercase;
you wan to read them as a structure in which user words are held
(compilers will not let you misspell them, unlike written English
where all uppercase is horrible)

3) lowercase letters are actually read (as opposed to eating them as a
shape) and misspelling can be quickly seen. Newspapers and books knew
this centuries ago. That is why you use them for column names,
variables and so forth. But Capitalize (or ProperCase) schema
objects, like table and view names because they are proper nouns.

The underscore is much better than either camelCase or ProperCase.
The eye can see the two parts of the the ISO-11179 names easily and
read the postfix to learn the nature of the attribute without being
jerked to the uppercase letters.

These tested rules are much easier to implement in a language with
string functions than using a big look-up table in SQL.

Good naming conventions and formatting can reduce maintenance time by
8-12% and since that is where the real cost is, it is worth the
effort.

joining on same table multiple times on different values

2007-04-27T20:53:00.000-04:00

SQL Apprentice Question
Having perfomance problems with this query (returning to many rows and
takening to long), related I think
to joining on the DomainDetail table 3 times.
Is there a better way to do this?

SELECT SO.SystemOrderID AS Order_ID,
SO.SystemOrderTypeID AS Order_TypeID,
SO.OrderStatusID AS Order_StatusID,
SO.CreateDate AS Order_CreateDate,
DD.[Name] AS Detail_Status
DD1.Name AS Detail_Type
DD2.Name AS Order_Status
FROM MyDatabase3.dbo..SystemOrder SO
INNER JOIN MyDatabase2.dbo..SystemOrderDetail SOD
ON SOD.SystemOrderID = SO.SystemOrderID
INNER JOIN MyDatabase1.dbo.DomainDetail DD
ON SOD.DetailStatusID = DD.DomainValue
INNER JOIN MyDatabase1.dbo.DomainDetail DD1
ON SOD.DetailTypeID = DD1.DomainValue
INNER JOIN MyDatabase1.dbo.DomainDetail DD2
ON SO.OrderStatusID = DD2.DomainValue
WHERE SO.CreateDate > dateadd(month,-13,getdate())AND
SOD.CreateDate > dateadd(month,-13,getdate())AND
DD.DomainID IN(125,126,127)

thanks

Celko Answers
I first re-wrote your query to bring the names closer to ISO-11179,
remove the display headers in the SELECT list and get rid of redundant
predicates like "SOD.system_order_id = SO.system_order_id" (that
happens a lot with the infixed syntax ). Untested I got this.

SELECT SO.system_order_id, SO.system_order_type,
SO.order_status, SO.create_date,
DD.name AS detail_status
DD1.name AS detail_type
DD2.name AS order_status
FROM Mydatabase3.DBO..System_Order AS SO,
Mydatabase2.DBO..System_Orderdetail AS SOD,
Mydatabase1.DBO.Domaindetail AS DD,
Mydatabase1.DBO.Domaindetail AS DD1,
Mydatabase1.DBO.Domaindetail AS DD2
WHERE SOD.detail_status = DD.domain_value
AND SOD.detail_type = DD1.domain_value
AND SO.order_status = DD2.domain_value
AND SOD.system_order_id = SO.system_order_id
AND SO.create_date > DATEADD(MONTH, -13, CURRENT_TIMESTAMP)
AND SOD.create_date > DATEADD(MONTH, -13, CURRENT_TIMESTAMP)
AND DD.domain_id IN (125, 126, 127);

What you posted scares me. First of all, there is no such thing as a
"type_id" in a valid data model-- an attribute can be an identifier or
a type, but NEVER both at once. Ditto for "status_id" data element.

Is your order information spread across three databases (mydatabase3,
mydatabase2, mydatabase1)? That just does not work; a data model
needs to be in one place. I am still trying to make sense of the
"system-" prefix -- what is a "System_orders" entity and why is it
logically different from a plain "Orders" table?

There is a magical column with the uselessly vague name "name" that is
equivalent to "detail_status", "detail_type" and "order_status". But
a type and a status are very different kinds of attributes, just as
orders and details are very different kinds of entities. Ditto for
the equally magical "domain_value" data element. We need to know
"name of WHAT!?" to make sense of this.

I am going to make a guess that this schema:

1) Mixes data and metadata. The choice of names implies this strongly

2) Has non-scalar columns. We know that already from your magical
changing columns. Do you find the meaning of the magical domain_value
from the domain_id?

3) Has split entities across tables, which is probably why you have
multiple databases.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

dynamically change column name when displaying

2007-04-22T12:48:00.000-04:00

SQL Apprentice Question
have a table, after i query the table for displaying, several column names
will be changed based on the condition, but I don't want change the acutal
table column names, it will only be changed when displaying.
I tried CTE and virtual table with column aliases, but they doesn't work,
they require you know the column name first, i CANNOT pass @columnName to
them.

I can certainly create a temp table and copy all the data from the orginal
table, and then based on the condition to rename the column name of the temp
table, then display it, but that is not efficient.

Celko Answers
You have missed the ENTIRE CONCEPT OF A TIERED ARCHITECTURE!!

You NEVER query a table for display; you query a table for **data**
and the front end does their display. You are writing 1950's file
systems in SQL!!

Outline number sorting

2007-04-22T12:41:00.000-04:00

SQL Apprentice Question
Is there a way to in SQL to sort outline numbers to retain the
"natural order" state?

E.g.:

1.1
1.2
1.3
1.10

Shows up now as:
1.1
1.10
1.2
1.3

Is there any way to use recursive functionality to check for longer
outline numbers (i.e., like 1.1.1.1.1)?

Celko Answers
>> Is there a way to in SQL to sort outline numbers to retain the "natural order" state? <<

I use this system in my books and other things a lot. My solution is
to pad each section with leading zeros and hope I never have more than
99 headings.

00.00.
01.00.
01.01
01.01.02.
etc.

You enforce this with LIKE predicates and ORs in the DDL rather than
trying to do it in the DML.

Stored procedure returns duplicates

2007-04-22T12:39:00.000-04:00

SQL Apprentice Question
I am trying to create a report in Crystal Reports (v 8.5). I have a
stored procedure to pull data from two databases and parameters.
There are multiple one-to-many relationships and the stored procedure
returns duplicates; e.g., one schedule may have multiple resources,
supplies, and/or orders (and one order may have multiple foods). Is
there a way to stop the duplication?

The stored procedure looks like this:

************************************************************************************
SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE usp_rpt1 (
@start_date smalldatetime,
@end_date smalldatetime,
@rpt_type varchar(3),
@rpt_id int
)
AS

set nocount on

--Set up some string variables to build the selection query for the
parameters supplied

declare @fields varchar(255)
declare @tables varchar(255)
declare @where varchar(2000)

CREATE TABLE #tmp_sched(sched_id int, rpt_type_desc varchar(100),
rpt_id int)

set end_date = midnight of next day
SELECT @end_date = DATEADD(day,1,@end_date)
SELECT @end_date = CONVERT(smalldatetime,
CONVERT(varchar(4),YEAR(@end_date)) + '-'
+
CONVERT(varchar(2),MONTH(@end_date)) + '-'
+
CONVERT(varchar(2),DAY(@end_date))

IF @rpt_type = 'LOC'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, l.loc_desc, l.loc_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_date srd ON s.sched_id = srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
LEFT JOIN tbl_loc l ON g.loc_id = l.loc_id
WHERE l.loc_id = CONVERT(varchar(12),@rpt_id)
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
ANd l.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_date_local >=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < rpt_type =" 'GRP'" sched_id =" srd.sched_id" res_id =" r.res_id" grp_id =" g.grp_id" grp_id =" CONVERT(varchar(12),@rpt_id)" parent_grp_id =" CONVERT(varchar(12),@rpt_id))" obsolete_flag =" 0" obsolete_flag =" 0" deleted_flag =" 0">=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < rpt_type =" 'RES'" sched_id =" srd.sched_id" res_id =" r.res_id" res_id =" CONVERT(varchar(12),@rpt_id)" obsolete_flag =" 0" deleted_flag =" 0">=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < rpt_type =" 'REG'" sched_id =" srd.sched_id" res_id =" r.res_id" grp_id =" g.grp_id" loc_id =" l.loc_id" loc_id =" reg.region_id" region_id =" CONVERT(varchar(12),@rpt_id)" obsolete_flag =" 0" obsolete_flag =" 0" obsolete_flag =" 0" obsolete_flag =" 0" deleted_flag =" 0">=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < sched_id =" srd.sched_id" res_id =" r.res_id" grp_id =" g.grp_id" grp_id =" 0" parent_grp_id =" 0)" obsolete_flag =" 0" obsolete_flag =" 0" deleted_flag =" 0">=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < description =" ts.rpt_type_desc," date =" CONVERT(varchar(12),srd.mtg_start_date_local,101)," starttime =" srd.mtg_start_date_local," endtime =" srd.mtg_end_date_local," schedid =" s.sched_id," meetingtitle =" s.sched_desc," resourceused =" r.res_desc," resourcesetup =" su.setup_desc" numberofattendees =" Attendees.string_value," orderid =" ord.order_id," foodqty =" CONVERT" fooddesc =" i.item_name," side =" sidei.item_name," meetingdesc =" ord.order_desc," supplies =" suppliesudf.udf_desc," suppliesval =" supplies.value," accountcode =" ord.order_user_acct_code," cateringnotes =" ord.order_notes," foodnotes =" oi.order_notes" sched_id =" s.sched_id" sched_id =" srd.sched_id" res_id =" r.res_id" sched_id =" srs.sched_id" res_id =" srs.res_id" setup_id =" rs.setup_id" res_id =" rs.res_id" setup_id =" su.setup_id" sched_id =" supplies.sched_id" request_tab_id =" (SELECT" request_tab_hdr =" 'A)" request_tab_id =" (SELECT" request_tab_hdr =" 'Mtg" udf_id =" suppliesudf.udf_id" sched_id =" s.sched_id" udf_id =" (SELECT" udf_desc =" 'Number" sched_id =" s.sched_id" udf_id =" (SELECT" udf_desc =" 'Meeting" order_sched_id =" s.sched_id" order_id =" oi.order_id" menu_item_id =" mi.menu_item_id" item_id =" i.item_id" order_item_id =" side.order_item_id" item_id =" sidei.item_id" deleted_flag =" 0" deleted_flag =" 0">

Celko Answers
>> Any suggestion is greatly appreciated. <<

EVERYTHING you are doing is TOTALLY wrong. You have just been cussed
out by one of the people who wrote this language. If you have brain
instead of an ego, you might want to listen.

This is a (bad) COBOL program written in SQL! There is so much
formatting done in SQL code! The bad news -- for me-- is that this
code is so awful I cannot use it in my next book as a bad example
because it is too proprietary! You could be famous!

Your code is so awful, you even use the "tbl-" prefixes to tell us you
have no idea about RDBMS! You keep converting dates to strings because
you are writing COBOL in SQL and want strings!

Why do your have "CREATE TABLE #tmp_sched" when view would work?
Answer: because magnetic tape files have to be materialized

Why do you spit on ISO-11179 rules and use a "tbl-" prefix? Because
you know only BASIC programming, which needs the prefixes for the one
pass compiler.

You write SQL with flags like it was 1950's Assembly language! Flags
in SQL!! Ghod Damn!! Varying length identifiers!? And I loved the way
spit on ANSI/ISO Standards with "SET QUOTED_IDENTIFIER OFF", etc.?

You need help you cannot get on a newsgroup.

Month Year Date Comparison

2007-04-12T13:31:00.000-04:00

SQL Apprentice Question
am running into a slight issue with a month year comparison. I
wrote q SP thats user-input driven. User selects a Start Month, Start
Year and an End Month and End Year. The database that houses the
information has month and year columns.

Example:
Name Month Year

Me 1 2006
You 1 2006
Us 3 2005
He 5 2007
She 6 2005
It 7 2006
We 12 2005
They 11 2005

The query looks like this:

select cust.name, CASE rev.[month]
WHEN '01' THEN 'January'
WHEN '02' THEN 'February'
WHEN '03' THEN 'March'
WHEN '04' THEN 'April'
WHEN '05' THEN 'May'
WHEN '06' THEN 'June'
WHEN '07' THEN 'July'
WHEN '08' THEN 'August'
WHEN '09' THEN 'September'
WHEN '10' THEN 'October'
WHEN '11' THEN 'November'
WHEN '12' THEN 'December'
END AS [Month], rev.year
from rev
inner join cust ON rev.name = cust.name
where (rev.name = @ter) AND (rev.month >= @start_month) AND (rev.year

>= @start_year) AND (rev.month <= @end_month) AND (rev.year <=

@end_year)
group by blah blah blah
order by blay blah blah

This works great until they span years.
Example:
Start Month = 11
Start Year = 2005
End Month = 2
End Year = 2006

Any ideas on how to make this work?

Celko Answers
This design flaw is called attribute splitting. A date is a single
unit of information, but you have put it into multiple columns. We can
fake it for your current problem, but you need to learn to use
temproal data types in SQL -- this is not 1950's COBOL any more, which
is how you are writing code.

Convert = / = to outer joins (ANSI compliant)

2007-04-11T14:31:00.000-04:00

SQL Apprentice Question
I'm working on converting *= and =* to 'left outer join' and 'right outer
join'.

I noticed the difference in behavior between the =* and the phrase "right
outer join" and between *= and 'left outer join'. The result set is
different. Here is an example:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

I try to conver the above to:

select a.au_id, b.title, c.qty

from titleauthor a

right outer join titles b

on (a.title_id = b.title_id )

right outer join sales c

on (a.title_id = c.title_id )

The first results into 391 rows in pubs database of sql-server 2000 and the
second produces 34 rows. It seems not that straight forward to convert.

The question is: If I want to get 391 row, what should I change/add in the
second sql statement?

Thank you. Appreciate your help.

Celko Answers

There is no simple answer because the old proprietary *=, +=, OUTER 
<tbl name>, (+) syntaxes all behaved a little differently.  There were 
a lot of weird tricks to work around the fixed orders of execution and 
so forth. 

I am also not sure how to handle a "HAVING  x *= y" clause, which old 
Sybase people used to write. And then nesting subqueries could mess up 
things.  But if you understand how the ANSI syntax works, then you can 
take the **intent** of the old code and re-write the statements pretty 
quickly. 


Here is how OUTER JOINs work in SQL-92.  Assume you are given: 


Table1       Table2 
 a   b        a   c 
 ======       ====== 
 1   w        1   r 
 2   x        2   s 
 3   y        3   t 
 4   z 


and the outer join expression: 


 Table1 
 LEFT OUTER JOIN 
 Table2 
 ON Table1.a = Table2.a      <== join condition 
    AND Table2.c = 't';      <== single table condition 


We call Table1 the "preserved table" and Table2 the "unpreserved 
table" in the query.  What I am going to give you is a little 
different, but equivalent to the ANSI/ISO standards. 


1) We build the CROSS JOIN of the two tables.  Scan each row in the 
result set. 


2) If the predicate tests TRUE for that row, then you keep it. You 
also remove all rows derived from it from the CROSS JOIN 


3) If the predicate tests FALSE or UNKNOWN for that row, then keep the 
columns from the preserved table, convert all the columns from the 
unpreserved table to NULLs and remove the duplicates. 


So let us execute this by hand: 


 Let @ = passed the first predicate 
 Let * = passed the second predicate 


 Table1 CROSS JOIN Table2 
 a   b        a   c 
 ========================= 
 1   w       1   r @ 
 1   w       2   s 
 1   w       3   t * 
 2   x       1   r 
 2   x       2   s @ 
 2   x       3   t * 
 3   y       1   r 
 3   y       2   s 
 3   y       3   t @* <== the TRUE set 
 4   z       1   r 
 4   z       2   s 
 4   z       3   t * 


 Table1 LEFT OUTER JOIN Table2 
 a   b        a   c 
 ========================= 
 3   y     3      t      <= only TRUE row 
 ----------------------- 
 1   w     NULL   NULL   Sets of duplicates 
 1   w     NULL   NULL 
 1   w     NULL   NULL 
 ----------------------- 
 2   x     NULL   NULL 
 2   x     NULL   NULL 
 2   x     NULL   NULL 
 3   y     NULL   NULL  <== derived from the TRUE set - Remove 
 3   y     NULL   NULL 
 ----------------------- 
 4   z     NULL   NULL 
 4   z     NULL   NULL 
 4   z     NULL   NULL 


the final results: 


 Table1 LEFT OUTER JOIN Table2 
 a   b        a   c 
 ========================= 
 1   w     NULL   NULL 
 2   x     NULL   NULL 
 3   y     3      t 
 4   z     NULL   NULL 


The basic rule is that every row in the preserved table is represented 
in the results in at least one result row. 


There are limitations and very serious problems with the extended 
equality version of an outer join used in some diseased mutant 
products.  Consider the two Chris Date tables 


 Suppliers        SupParts 
 supno             supno partno qty 
 =========        ============== 
 S1               S1   P1    100 
 S2               S1   P2    250 
 S3               S2   P1    100 
                  S2   P2    250 


and let's do an extended equality outer join like this: 


 SELECT * 
  FROM Supplier, SupParts 
 WHERE Supplier.supno *= SupParts.supno 
   AND qty < 200; 


If I do the outer first, I get: 


 Suppliers LOJ SupParts 
 supno supno partno qty 
 ======================= 
 S1     S1   P1    100 
 S1     S1   P2    250 
 S2     S2   P1    100 
 S2     S2   P2    250 
 S3   NULL  NULL   NULL 


Then I apply the (qty < 200) predicate and get 


 Suppliers LOJ SupParts 
 supno supno partno qty 
 =================== 
 S1   S1   P1    100 
 S2   S2   P1    100 


Doing it in the opposite order 


 Suppliers LOJ SupParts 
 supno supno partno qty 
 =================== 
 S1   S1   P1    100 
 S2   S2   P1    100 
 S3   NULL NULL  NULL 


Sybase does it one way, Oracle does it the other and Centura (nee 
Gupta) lets you pick which one -- the worst of both non-standard 
worlds!  In SQL-92, you have a choice and can force the order of 
execution. Either do the predicates after the join ... 


 SELECT * 
   FROM Supplier 
        LEFT OUTER JOIN 
        SupParts 
        ON Supplier.supno = SupParts.supno 
 WHERE qty < 200; 


 ... or do it in the joining: 


 SELECT * 
  FROM Supplier 
       LEFT OUTER JOIN 
       SupParts 
       ON Supplier.supno = SupParts.supno 
          AND qty < 200; 


Another problem is that you cannot show the same table as preserved 
and unpreserved in the extended equality version, but it is easy in 
SQL-92.  For example to find the students who have taken Math 101 and 
might have taken Math 102: 


 SELECT C1.student, C1.math, C2.math 
  FROM (SELECT * FROM Courses WHERE math = 101) AS C1 
       LEFT OUTER JOIN 
       (SELECT * FROM Courses WHERE math = 102) AS C2 
       ON C1.student = C2.student;

need help switching a boolean value

2007-04-10T15:38:00.000-04:00

SQL Apprentice Question
what i want to acheieve is the following, in my table i have an "isactive"
field, i want to create a sql line something like

update tblProducts set isactive = NOT isactive where productID=123....but it
doesnt work.. is there any syntax that handles this in sql?

Celko Answers
>> in my table i have an "isactive" field [sic: fields and columns are TOTALLY different concepts!!!], I want to create a SQL line [sic: statement? that makes no sense!!] something like this: <<

Do you every read ISO Standards and know not to use "tbl" affixes?

UPDATE Products
SET isactive -- assembly lamnguage flag in SQL !!!
= NOT isactive
WHERE product_id = 123;

....but it doesn't work <<

DUH!! If you had bothered to read ANY SQL book before posting, you
would know we have no Boolean data types in SQL. If you had a better
education in math or RDBMS, you would know why.

Newsgroups are a bad place to get a basic education. We will give you
kludges to get rid of you because of your stupid questions, but they
will not make you a good programmer.

SQL: how to convert time from GMT to EST

2007-04-10T15:32:00.000-04:00

SQL Apprentice Question
In my Database, all date and time are stored in GMT, however, when it
is displayed, I need them in EST. How can this be done with Daylight
saving time taken into account as well?

Celko Answers

>> In my Database, all date and time are stored in GMT, .. <<

Interesting. GMT has not existed for a VERY LONG TIME! Google iot, so
you will not look so far behind. Did you mean UTC and just do not
bother to learn this decade's technology?

>> How can this be done with Daylight Saving Time taken into account as well? <<

This is called "local lawful time" and it varies quite a bit. Set up
a copy of the Standard SQL temporal information tables that MS has not
bothered with yet. Then set up VIEWs and use them. These auxiliary
tables will have (start_time, end_time, displacement off of UTC)
columns for the DST.

Data type in audit record

2007-04-09T14:27:00.000-04:00

SQL Apprentice Question
I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:

TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)

TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change

So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.

Any thoughts/recommendations/criticism would be greatly appreciated.

Thanks

Celko Answers

>> I want my application to audit any data changes (update, insert, delete) made by the users. Rather than have an audit table mirroring each user table, I'd prefer to have a generic structure which can log anything. <<

Any chance you might post DDL instead of your personal pseudo-code?
And I hope you know that auto-numbering is not a relational key.
Finally, Google "EAV design flaw" for tens of thousands of words on
why this approach stinks. There is no such magical shape shifting
table in RDBMS. Data Versus metadata, etc.? Freshman database
course, 3rd week of the quarter?

While you might like this kludge your accountants and auditors will
not. NEVER keep audit trails on the same database or even the same
hardware as the database.

>> Any thoughts/recommendations/criticism would be greatly appreciated. <<

Look at third party tools that follow the law and get a basic dat
modeling book.

Newbie: a query to get back the closest number to user input number.

2007-04-09T14:25:00.000-04:00

SQL Apprentice Question
have a situation where I need to return the name and salary of an
employee closest to the salary entered by the user.

Is there a "Nearest" or "Closest" type of operator in sql that will
help me instantly get the row that is closest to an arbitrary number?
If not, can anyone help me with an approach to this type of single
table problem?

Much appreciated.

Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Is this what you meant to post?

CREATE TABLE Personnel
(emp_name CHAR(25) NOT NULL PRIMARY KEY,
salary_amt DECIMAL (10,2) NOT NULL
CHECK (salary_amt > 0.00));

Try something like this:

SELECT emp_name, salary_amt, @my_salary AS search_salary
FROM Personnel AS P
WHERE ABS(P.salary_amt - @my_salary)
= (SELECT MIN (ABS(P2.salary_amt - @my_salary))
FROM Personnel AS P2);

The closest salary might be higher or lower than the search value, so
you need ABS(). In the real world, there will be a lot of ties, since
most companies have salary schedules rather than odd.

Update Status Field after Expiry Date

2007-03-31T13:39:00.000-04:00

SQL Apprentice Question
Consider the following table

Customer
custId char(10)
accountExpiryDate datetime
accountStatus bit

Now, I want to update the accountStatus to False as soon as the
current date becomes accountExpiryDate.

I think it can be done using "SQL Agent" but my webhost doesnt provide
me access to that. I have access only to the Query Analyzer.

Celko Answers

>> Consider the following table <<

Where is it? Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Sample data is also a good idea, along
with clear specifications. It is very hard to debug code when you do
not let us see it. Here is my guess:

CREATE TABLE Customers
(cust_id CHAR(10) NOT NULL PRIMARY KEY, --wild guess
acctexpiry_date DATETIME NOT NULL,
..);

Notice I dropped the redundant BIT column. Some newbie actually used
a proprietary, low-level BIT data type. You need to fix that at once
and teach the guy that SQL has no BOOLEAN data types -- that is just
sooooo fundamental!

>> Now, I want to update the account_status to FALSE as soon AS the current date becomes accountexpiry_date. <<

Just like you would do this in a punch card system 50 years ago!
Running updates to physical storage every day? You are missing the
fundamental concepts of RDBMS in this design. Each row of a table is
a fact that should stand by itself. Use a VIEW not an assembly
language bit flag!!

CREATE VIEW ActiveCustomers (..)
AS
SELECT cust_id, acctexpiry_date, ..
FROM Customers
WHERE CURRENT_TIMESTAMP < acctexpiry_date;

And then you need to consider how much history and account status
codes you want. Do you need to design an acct_status code? Etc.

Incorrect syntax near the keyword 'As'

2007-03-22T12:29:00.000-04:00

SQL Apprentice Question
I'm trying to complete an If, Then statement within a script that I put
together, and I keep getting an "Incorrect syntax near the keyword 'As'"
error toward the end of the statement.

The whole statement says that if you are taking the payroll codes from the
benefit table, then provide the payroll code's description such "health
insurance" or "dental insurance".

The very beginning of my clause, of course, says "SELECT ... CASE
UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
ELSE 0 END As [Payroll Description]

There must be something wrong with the ELSE portion. I tried to fix it, by
saying ELSE '' END As [Payroll Description]. But, I got the same error.

What am I doing wrong, and how can I fix this?

Celko Answers
Having blanks inside a column name is a bad idea, since the name will
not port to other languages, is very easy to mis-type and messes up
the data dictionary. You also used the proprietary square brackets
instead of the proper double quotes.

You can make your code a lot easier to read without the extra WHEN
clauses. A CASE expression has one and only one data type. You need
to be sure that the THEN clauses are all the same data type. That can
be done with implicit promotion or use a CAST() function.

CASE
WHEN UPR30300.payrol_cd
IN ('0SC2HF', 'SC2LE', 'SC2LS', 'SC2LC')
THEN UPR40800.dscriptn
ELSE '{{Unknown}}' AS payroll_description

SQL Conundrum

2007-03-22T12:19:00.000-04:00

SQL Apprentice Question
I have the following SQL schema

Bookings
-------------
BookID
Hours

BookingStatus
---------------------
BookStatusID
BookingId
StatusID
UserId
Date

Statues
-----------
StatusID

Statuses included Created, Covered, Cancelled, Not Covered, To Be
Arranged, etc.

The BookingStatus table contains a history of the statuses a booking
goes through. Whenever a user updates the status of a booking, a new
record is inserted into the Booking Status with the corresponding
data.

I'm trying to create a report to show the number of bookings each user
has covered over a given time period. However, i only want to count
bookings that are still covered (i.e. have not been cancelled since
they were covered) and i don't want it to count bookings that have
been covered more than once. If it did, a user would be credited for
covering an individual booking more than once.

In other words, i need to fetch only the top 1 status of each booking
and count only those that are covered, if you see what i mean!

I could record the current status of the booking in my Bookings table,
but i'm worried about data integrity and keeping the booking status
consistant.

Anyone got any bright ideas about how i can either design my schema
better, or come up with an efficient SQL query to select my report? I
also need to sum the hours each user has covered, as well as a count
of bookings covered.

Many thanks for any suggestions

Celko Answers
>> Many thanks for any suggestions

Here is a "cut & paste" on history tables. In your case, I would also look at my article on Transition Constraints at www.dbazine.com for more ideas.

===============
The start and stop times are what you should have been catching in the first place and not the computed hours. Think raw data and single facts when designing a table. Let me use a history table for price changes. The fact to store is that a price had a duration:

CREATE TABLE PriceHistory (upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date <> 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

While loop?

2006-11-24T10:30:00.000-05:00

SQL Apprentice Question
So I am just starting to learn how to use Database. I need the help of a SQL
guru! I have these three tables:
CREATE TABLE SB3_ScheduleChange
(
emp_num EmpNumType:nvarchar(7) NOT NULL PRIMARY KEY,
Monday char(35) NULL,
Tuesday char(35) NULL,
Wednesday char(35) NULL,
Thursday char(35) NULL,
Friday char(35) NULL,
Saturday char(35) NULL,
Sunday char(35) NULL,
Name char(35) NOT NULL;
)

CREATE TABLE SB3_ScheduleChange
(
emp_num EmpNumType:nvarchar(7) NOT NULL PRIMARY KEY,
sched_type_id nt, NOT NULL,
start_date DateType:datetime NOT NULL,
end_date DateType:datetime NOT NULL,
comments varchar(8000) NULL,
)

CREATE TABLE SB3_ScheduleType
(
sched_type_id int, NOT NULL PRIMARY KEY,
reason char(20) NULL,

)

I am trying to come up with a query that will iterate through each employee
with the emp_num primary key in the emp_shcedule table. I would like the
iteration to check if the employee has an exception or "change" from their
regular default schedule in the table emp_schedule checking it against the
"SB3_ScheduleChange" table. And if the query finds a difference in schedule
to update or insert it into that employees default schedule until the change
is over on a certain date. My problem is I am new to SQL and I dont know how
to do this. Do I need a Calendar table? Use a while loop? Any examples,
help, or suggestions would be very much appreciated. Not that this will help
but here are some queries I have come up with. Just dont know how to tie it
all together.

This would list name, emp_num, sched_type_id, for each employee who has
A schedule type difference. Query shows every employee with a schedule type
difference.

SELECT es.Name, es.emp_num, sc.sched_type_id
FROM emp_schedule AS es JOIN SB3_ScheduleChange AS sc
ON(es.emp_num = sc.emp_num)

List name, emp_num of employees that are not assigned to any schedule
change. Query shows every employee without a schedule type difference.

SELECT name, emp_num
FROM emp_schedule AS es
WHERE NOT EXISTS
(SELECT emp_num
FROM SB3_ScheduleChange AS sc
WHERE es.emp_num = sc.emp_num);

And if necessary a Calendar CREATE TABLE. But once again I am not sure.
CREATE TABLE dbo.SB3_Calender
(
ActualDate DATETIME NOT NULL PRIMARY KEY,
MonthName CHAR(15) NULL,
DayNumber INT NULL,
YearNumber INT NULL,
DayOfWeek CHAR(15) NULL
CHECK (DayOfWeek IN ('Sunday', 'Monday', 'Tuesday',
'Wednesday','Thursday','Friday','Saturday')),
DayType CHAR(15) NULL
CHECK ( DayType IN ('Business','Weekend','Holiday')),
)

GO

SET NOCOUNT ON
DECLARE @Counter INT
DECLARE @ActualDate DATETIME
DECLARE @FirstDate DATETIME
SET @Counter = 1
SET @FirstDate = '1/1/2006'
SET @ActualDate = @FirstDate
WHILE @Counter < 1096
BEGIN
INSERT INTO Calender (ActualDate)
values(@ActualDate)
SET @ActualDate = DATEADD(day, @Counter, @FirstDate)
SET @Coutner = @Counter + 1
END

GO

UPDATE Calender
SET DayOfWeek = DateName(DW, ActualDate)

GO

UPDATE Calender
SET DayNumber = DateName(DD,ActualDate)

GO

UPDATE Calender
SET MonthName = DateName(MM,ActualDate)

GO

UPDATE Calender
SET YearNumber = DateName(YY,ActualDate)

GO

UPDATE Calender
SET DayType = 'Business'
WHERE DayOfWeek <> 'Saturday' AND DayOfWeek <> 'Sunday'

GO

UPDATE Calender
SET DayType = 'Weekend'
WHERE DayOfWeek = 'Saturday' OR DayOfWeek = 'Sunday'

GO

UPDATE Calender
SET DayType = 'Holiday'
WHERE (MonthName ='January' AND DayNumber = 1 ) OR
(MonthName ='July' AND DayNumber = 4) OR
(MonthName ='December' AND DayNumber = 25)

GO

Celko Answers

>> So I am just starting to learn how to use Database. <<

1) What you are doing is mimicing a paper schedule form, not designing
a normalized schema.

2) data element names like the days of the week are values and not
attributes. Tha tis the output of a cross tabs report, not a table.

3) Column names like "sched_type_id" are absurd; an attribute can a key
(identifier, points to a unique *entity* in the data model) or a type
(non-key *attribute*, holds a value) but NEVER both.

4) You are still thinking about procedural code instead of declarative
programming and data. YOu have designed a magentic tape file merge,
complete with record at a time processing. Welcom to 1957!

Why not something like this?

CREATE TABLE PersonnelSchedules
(emp_num CHAR(7) NOT NULL
REFERENCES Personnel(emp_num)
ON UPDATE CASCADE
ON DELETE CASCADE,
activity_date DATETIME NOT NULL,
PRIMARY KEY (emp_num, activity_date),
activity_code INTEGER NOT NULL
CHECK (activity_code IN (...)),
..); ,

I am assuming one activity per day; if not this can be modified by
adding an activity number. You might need other attributes, but I
don't know you business rules. A single fact should be modeled witha
single row in a single table, not split all over the schema.

>> I am trying to come up with a query that will iterate through each employee with the emp_num primary key in the emp_shcedule table. I would like the iteration to check if the employee has an exception or "change" from their regular default schedule in the table emp_schedule checking it against the "SB3_ScheduleChange" table. <<

Why not go to the PersonnelSchedules and make this change, without
storing it in multiple locations? If you need to mark something as a
schedule change, then add a column for that attribute to the table.

When you get a new guy, fill out his schedule for 5-10 years in
advance. It will use less disk space than a hi-res employee badge
photograph. Now you can use a VIEW to get the weekly schedules, you
can do manpower projections, etc.

Most queries are easy if you have the right DDL. You should never use
a cursor in your SQL; we had to in the old days because we did not have
CTEs, CASE expressions, etc.

complex(?) query

2006-11-24T10:24:00.000-05:00

SQL Apprentice Question
I'm a novice sql writer and need some help in writing a query to
extract applicable data from the following table (titled EMPLOYEE):

--
ID_NUMBER CODE DATE
------------------ --------- --------
12 VO 20060914
12 XD 20060913
12 AD 20060912
12 WR 20060911
12 AT 20060910
45 VO 20060914
45 XR 20060913
45 AT 20060912
45 AD 20060911
45 AT 20060910
78 AD 20060914
78 AT 20060913
78 VO 20060912
78 AD 20060911
78 AT 20060910

I need to select ID_NUMBER
from EMPLOYEE
where CODE = 'VO'

caveat: I only want the ID_NUMBER(s) where the CODE = 'VO'
and the previous CODE (by DATE) = 'AD'
or the previous CODE (by DATE) = 'AD' with any CODE in between
except 'AT';

E.g., in the above example, the appropriate code should select
ID_NUMBER(s) 12 and 78 because
1. a VO code exists
2. an AD code (by DATE) precedes it
3. although 'AD' does not come immediately before 'VO' (in the
case of ID_NUMBER 12) 'AT' cannot be found in between

I hope I haven't confused anyone. Any help would be appreciated.

Celko Answers

>>I'm a novice sql writer and need some help in writing a query to extract applicable data from the following table (titled EMPLOYEE): <<

First, let's clean up your missing DDL. The table name should tell us
what set of entities is modeled in the table; do you really have one
employee? Small firm! Try Personnel -- the collective name of the set
or something that tells us what the set is. Code is too vague --
postal code? Date is both too vague *and* a reserved word. A name
like "id_number" is also uselessly general; emp_id would be a better
choice. Since you did not post DDL, we have to guess at constaints and
keys. A skeleton of what you need is something like this:

CREATE TABLE PersonnelActions
(emp_id INTEGER NOT NULL,
action_date action_dateTIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (emp_id, foobar_date),
action_code CHAR(2) NOT NULL
CHECK (foobar_code IN ('VO', 'XD'))
);

You need to read a book on data modeling and ISO-11179 rules for names.
I would also look up the use of UPPERCASE for names -- it is the worst
way to code, being about 8-12% harder to detect misspellings. That is
why books and newspapers use lowercase.

>> I only want the emp_id(s) where the action_code = 'VO'

and the previous action_code (by action_date) = 'AD'
or the previous action_code (by action_date) = 'AD' with any
action_code in between
except 'AT'; <<

SELECT DISTINCT emp_id
FROM PersonnelAction AS PVO,
PersonnelAction AS PAD
WHERE PVO.emp_id = PAD.emp_id
AND PVO.action_code = 'VO'
AND PAD.action_code = 'AD'
AND PAD.action_date < PVO.action_date
AND NOT EXISTS
(SELECT *
FROM PersonnelAction AS PAT
WHERE PAT.action_code = 'AT'
AND PAT.emp_id = PVO.emp_id
AND PAT_action_date BETWEEN PAD.action_date AND
PVO.action_date);

one to one random mapping between int

2006-11-24T10:20:00.000-05:00

SQL Apprentice Question
I need to one-to-one map a range of integer (A), say 1 to 10000, randomly to
1 to 10000 (B). And I need to get A if a B is given. Any existed good
algorithm in T-SQL?

Celko Answers
Here is an implementation of the additive congruential method of
generating values in pseudo-random order and is due to Roy Hann of
Rational Commerce Limited, a CA-Ingres consulting firm. It is based on
a shift-register and an XOR-gate, and it has its origins in
cryptography. While there are other ways to do this, this code is nice
because:

1) The algorithm can be written in C or another low level language for
speed. But math is fairly simple even in base ten.

2) The algorithm tends to generate successive values that are (usually)
"far apart", which is handy for improving the performance of tree
indexes. You will tend to put data on separate physical data pages in
storage.

3) The algorithm does not cycle until it has generated every possible
value, so we don't have to worry about duplicates. Just count how many
calls have been made to the generator.

4) The algorithm produces uniformly distributed values, which is a nice
mathematical property to have. It also does not include zero.

Generalizing the algorithm to arbitrary binary word sizes, and
therefore longer number sequences, is not as easy as you might think.
Finding the "tap" positions where bits are extracted for feedback
varies according to the word-size in an extremely non-obvious way.
Choosing incorrect tap positions results in an incomplete and usually
very short cycle, which is unusable. If you want the details and tap
positions for words of one to 100 bits, see E. J. Watson, "Primitive
Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962,
p.368-369. Here is code for a 31-bit integer, which you can use:

see the details at:

http://www.rationalcommerce.com/resources/surrogates.htm

UPDATE generator31
SET keyval
= keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/2, 2), 2) * 8;

T-SQL version:

CREATE TABLE Generator31 (keyval INTEGER NOT NULL);
INSERT INTO Generator31 VALUES (1); -- any start value

UPDATE Generator31
SET keyval =
keyval/2 + ((keyval % 2) + (keyval/8 % 2) % 2)* 8
SELECT * FROM Generator31;

Or if you prefer, the algorithm in C:

int Generator31 ()
{static int n = 1;
n = n >> 1 ((n^n >> 3) & 1) << 30;
return n;

oining on "between x and y"

2006-11-24T10:13:00.000-05:00

SQL Apprentice Question
I have these tables:

Sales (SaleId, ProductId, QTY)
Bandwidth (ProductId, RangeFrom, RangeTo, Price)

A RangeTo field is included in the Bandwidth table cause an "open end"
is possible; 1-10, 11-20, 21>

Now i would like to get a table like:

SalesView(SaleId, ProductId, Qty, Price)

Where price has to be NULL if it cannot be matched in the bandwith
table; i always need all the records from the Sales table...

I tried something like this

SELECT s.SaleId, s.ProductId, s.QTY, s.QTY*b.Price AS Price
FROM tblSales s
INNER JOIN tblBandwidth b
ON b.ProductId = s.ProductID
AND
(
s.QTY BETWEEN b.RangeFrom AND b.RangeTo
OR
(s.QTY >= b.RangeFrom AND b.RangeTo IS NULL)
)

With inner join i don't get all sales records (there out of range),
with outer join i get them doubled or more (and i don't want to use a
DISTINCT)

Any great ideas?

Celko Answers

>> A RangeTo field [sic] is included in the Bandwidth table cause an "open end" is possible; 1-10, 11-20, 21 <<

That usually means that the price for that range holds for all
quantities over the from range value. In your example, any ordr over
21 units gets that same unit price. But then you say:

>> price has to be NULL if it cannot be matched in the bandwith table; i always need all the records [sic] from the Sales table... <<

You can use DRI to force all sales to be of actual products you stock,
and for every bandwidth to reference a real product. You should not
have any missing data if you design the schema properly. Here is a
guess at what you want:

CREATE TABLE Sales
(sale_id INTEGER NOT NULL PRIMARY KEY,
product_id INTEGER NOT NULL
REFERENCES Products(product_id),
sold_qty INTEGER NOT NULL
CHECK(sold_qty > 0));

CREATE TABLE Bandwidth -- weird name, why not Discounts?
(product_id INTEGER NOT NULL
REFERENCES Products(product_id),
from_range INTEGER DEFAULT 1 NOT NULL
CHECK (from_range > 0),
to_range INTEGER,
CHECK(from_range <= to_range),
unit_price DECIMAL (10,4) NOT NULL,
PRIMARY KEY (product_id, from_range) );

If you have a single price for an item, then the DEFAULT values will
give you a (1, NULL) row in the Bandwidth table. Hide the work in a
VIEW that will always be current:

CREATE VIEW SalesRevenue(sale_id, product_id, sale_qty, sale_total)
AS
SELECT S.sale_id, S.product_id, S.sale_qty,
S.sale_qty*B.unit_price
FROM Sales AS S, Bandwidth AS B
WHERE B.product_id = S.product_id
AND S.sale_qty BETWEEN
B.from_range AND COALESCE (B.to_range, S.sale_qty);

The COALESCE() will handle any quantity not explicitly in the Bandwidth
table.

SQL Apprentice Question
guess i didn't explain myself in such a good way. I might also used
some strange descriptions like bandwidth, probably cause english is not
my native language. But, let's not blame it on that; in the situation i
have now, referential integrity by the database is not possible, for as
far as i know, mainly because of:

* the actual quantity for 1 line item (i will use this term instead of
sale) is determined by a group of line items (if 1 is updated the whole
lot can switch into another range)
* the bandwidth/ranges/discounts may have a closed range: "1-10, 11-20,
21-30" (dont ask me why, commercial reasons probably)
* the line items are gathered over a period of time; at time of closing
the period the definete prices/tariff should be set; the system should
indicate where bandwidth/ranges do not fit the quantities
* different 'pricelists' can be applied to a set of 'line items'/sales

this is the reason why i stated "Where price has to be NULL if it
cannot be matched in the bandwith
table; i always need all the records from the Sales table... ", but
maybe i should have used the word tariff, because i don't mean the
qty*tariff result here, just the tariff.

Celko Answers
>> * the actual quantity for 1 line item (i will use this term instead of sale) is determined by a group of line items (if 1 is updated the whole lot can switch into another range)<<

I would use a VIEW with a GROUP BY to build these groups.

>> * the bandwidth/ranges/discounts may have a closed range: "1-10, 11-20, 21-30" (dont ask me why, commercial reasons probably) <<

That is still strange to me, but okay ...

>> * the line items are gathered over a period of time; at time of closing the period the definite prices/tariff should be set; the system should indicate where bandwidth/ranges do not fit the quantities <<

And having a VIEW would let you keep the correct totals

>> * different 'pricelists' can be applied to a set of 'line items'/sales <<

I would put all the price lists into one table, with the ranges and
price list name ("A list" customers, "B list" customers, etc.)

Here is a slight re-write; see if it helps

CREATE TABLE Sales
(sale_id INTEGER NOT NULL PRIMARY KEY,
product_id INTEGER NOT NULL
REFERENCES Products(product_id),
sold_qty INTEGER NOT NULL
CHECK(sold_qty > 0));

CREATE TABLE Bandwidth -- weird name, why not Discounts?
(product_id INTEGER NOT NULL
REFERENCES Products(product_id),
from_range INTEGER DEFAULT 1 NOT NULL
CHECK (from_range > 0),
to_range INTEGER NOT NULL, -- fix this constraint
CHECK(from_range <= to_range),
unit_price DECIMAL (10,4) NOT NULL,
PRIMARY KEY (product_id, from_range) );

Use an OUTER JOIN to presrve the sales data.

CREATE VIEW SalesRevenue(sale_id, product_id, sale_qty, sale_total)
AS
SELECT S.sale_id, S.product_id, S.sale_qty,
S.sale_qty*B.unit_price
FROM Sales AS S
LEFT OUTER JOIN
Bandwidth AS B
ON B.product_id = S.product_id
AND S.sale_qty BETWEEN
B.from_range AND B.to_range;

query question...

2006-11-24T10:12:00.000-05:00

SQL Apprentice Question
hi, not sure if db2 has a aggregate function to 'sum (concat)' a string
column in a group by?
i have a table like this:
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string3'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string4'
......

i want to group CatKye, SubCatKey, GrpKey, SubGrpKey
so, the result set to be like this (in sorted order):
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1,string4'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2,string3'

any idea? i heard there is something like ROWCONCAT... didn't find it
in db2 reference though.
thanks a lot.

Celko Answers
What you are looking for exists in Sybase as LIST() and one of the open
source products (Posttgres? I cannot remember). But that is not the
real question. Why do you wish to destroy First Normal Form (1NF) with
a concatendated list structure? It is the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.

Yes, there are kludges in SQL to do this. You can also still write
procedural code with GOTOs and get "spaghetti code", but it does not
mean you should.

dynamic cursor - sorting in declaration

2006-11-24T10:09:00.000-05:00

SQL Apprentice Question
I have a small table "ABC" like this:

id_position | value
---------------------------
1 | 11
2 | 22
3 | 33

I try to use a dynamic cursor as below.
When the statement "order by id_position" in declare part of the cursor_abc
is omitted - cursor work as it should.
But when the statement "order by id_position" is used, cursor behave as
static one.
What's the matter, does anybody know?

Code:

declare @id_position as int, @value as int

DECLARE cursor_abc CURSOR
FOR
select id_position, value from abc
order by id_position

set nocount on
open cursor_abc
FETCH NEXT FROM cursor_abc
INTO @id_position, @value

WHILE @@FETCH_STATUS = 0
BEGIN

print @id_position
print @value
print '----------------------------'

update abc set value=666 --next reading should give value=666

FETCH NEXT FROM cursor_abc
INTO @id_position, @value

END

CLOSE cursor_abc
DEALLOCATE cursor_abc
GO

Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

In many years of writing SQL, I have seldom found a need for a cursor.
They usually run 1-2 orders of magnitude slower than a relational
solution.

When someone uses one, it is generally becasue they are mimicing a
magnetic tape file system, and probably violating the basic principle
of a tiered architecture that display is done in the front end and
never in the back end. This a more basic programming principle than
just SQL and RDBMS.

Finally, id_position is not an ISO-11179 data element name and it makes
no sense. Identifier of what? Position of what? You have two
adjectives without a noun. But I bet you mant it to be PHYSICAL
location because you are mimicing a magnetic tape file system, instead
of using SQL for an RDBMS.

What is your real problem? Show us and perhaps we can help you.

Totals Help

2006-11-16T13:23:00.000-05:00

SQL Apprentice Question
Based on this query..... how can I total just the starred SYS_OP's produced
column Compute by will sum them all.
I want daily totals of just the starred operations but still list them all.

DECLARE @sDate SmallDateTime
DECLARE @sEnd SmallDateTime
DECLARE @sPart Char(15)
SET @sDate = '20061002'
SET @sEnd = '20061005'
set @sPart = '24277'

SET NOCOUNT ON

SELECT CONVERT(CHAR(10),C_DATE,101) as JT_DATE,
JT.DEPT, JT.PART, OP AS JT_OP,
CASE
WHEN LOD.LAST_OP IS NULL THEN ''
ELSE
'***'
END
AS SYS_OP,
SHIFT, C_COUNT AS PRODUCED
FROM Label_Audit..JOB_TICKETS_RAW JT
LEFT JOIN LABEL_AUDIT..V_LAST_OP_DEPT LOD
ON LOD.PART = JT.PART
AND JT.OP = LOD.LAST_OP
WHERE C_DATE BETWEEN @sDate AND @sEnd
AND JT.PART = @sPart
ORDER BY C_DATE, OP, SHIFT

JT_DATE DEPT PART OP SYS_OP SHIFT PRODUCED
---------- ---- --------------- ----- ------ ----- -----------
10/02/2006 8800 24277 020 1 134
10/03/2006 8800 24277 020 1 201
10/03/2006 8800 24277 020 2 405
10/03/2006 8800 24277 020 3 500
10/04/2006 8800 24277 020 1 625
10/04/2006 8800 24277 020 2 620
10/04/2006 8800 24277 020 3 100
10/04/2006 8800 24277 025 2 634
10/04/2006 8800 24277 025 3 950
10/04/2006 8800 24277 030 *** 1 891
10/04/2006 8800 24277 030 *** 2 634
10/04/2006 8800 24277 030 *** 3 950
10/04/2006 8800 24277 030A 1 891
10/05/2006 8800 24277 020 3 401
10/05/2006 8800 24277 025 3 821
10/05/2006 8800 24277 030 *** 3 821

Celko Answers
1) start using ISO-8601 formats for dates, not local dialect

2) start using valid data element names (part?? Unh?! part_name?
part_nbr? part_wgt?) Is c_date "completion_date?" or what? Write code
as if another human being has to maintain it after you win the lottery.

3) Real SQL programmers do not write proprietary, deprecated COMPUTE BY
clause. This is a decades old legacy kludge from the Sybase days when
there were no report writers. Welcome to the year 2006 (actually, this
was bad code in 1996). You ought to be doing this in a proper tool and
not trying to fake a 1950's break and total file system report.

4) Your spec does not tell us what to sum! Production, maybe? And
what do we group by? Here is a skeleton query:

SELECT ..
SUM (CASE WHEN LOD.last_op IS NULL THEN c_count ELSE 0 END) AS
star_stuff
SUM (CASE WHEN LOD.last_op IS NOT NULL THEN c_count ELSE 0 END) AS
other_stuff
FROM ..
WHERE ..
GROUP BY .. ;

sql statement

2006-11-16T10:52:00.000-05:00

SQL Apprentice Question
What is wrong int this SQL statement?

select top 10 DOCInt.*, DOCDet.* , Cate.*, Arti.*, [Ar An].*
from
DOCInt INNER JOIN DOCDet ON DOCInt.CodDoc=DOCDet.CodDoc
LEFT JOIN Cate ON DOCDet.IDCategory=Cate.[ID Category]
LEFT JOIN Arti ON DOCDet.IDArti=Arti.[ID Arti]
INNER JOIN [Ar An] ON DOCInt.IDAnag=[Ar An].[ID An]
GROUP BY DOCInt.IDDoc

Celko Answers

>> What is wrong in this SQL statement? <<

Well, your names are a nightmare that violate common sense and
ISO-11179 rules, but for now, you do not understand how a SELECT (GROUP
BY in particular) work:

Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The [table expression] AS [correlation name] option allows you
give a name to this working table which you then have to use for the
rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, partiton the original table
into groups and reduce each grouping to a *single* row, replacing the
original working table with the new grouped table. The rows of a
grouped table must be only group characteristics: (1) a grouping column
(2) a statistic about the group (i.e. aggregate functions) (3) a
function or constant(4) an expression made up of only those three
items. The original table no longer exists and you cannot reference
anything in it (this was an error in early Sybase products).

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The AS
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause have been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.

As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;

That is why such nonsense is illegal syntax.

Help me convince them this is bad!!!

2006-11-16T10:50:00.000-05:00

SQL Apprentice Question
I have an IT manager that thinks he knows everything when it comes to
database design. Unfortunately, he's not familiar with relational
databases but thinks he knows it all. He's come up with a really crazy
design, and I've got to come up with all the reasons it's not right. I
want to have as much ammo as possible so we're not stuck supporting
something that will never perform well.

We're in the process of moving some functionality from a Z/OS DB2
database to a RS/6000 DB2. Currently the Z/OS database has a table
that has a four column primary key. We've determined that one of these
keys is not needed on the new system, which would leave us with a three
column key. However, the manager wants to make it a two column key, be
concatenating two of the source columns (separated by double commas)
into just one column on the new system. Searches can be done on either
of the source columns or both.

I know searching this will be horrific, with tons of LIKE statements,
and indexing will be next to useless. But I need as many reasons as
possible to not take this approach. I don't want to end up being
blamed for poor performance down the road.

Celko Answers
>> He wants to make this a generic table, that can be used for different types of data in the future. <<

Pretty clearly the concatenated column is a total violation of 1NF.
Likewise, a "generic table" a violation of logic and RDBMS. I call
such things "Britney Spears, Automobiles and Squid" tables to show that
they cannot even have a basic ISO-11179 data element name. "To be is
to be something in particular; to be nothing in particular is to be
nothing at all" -- Aristotle

Here is a little "cut *& paste" I give people who want ot do a EAV
design. Someone like your boss posted this:

CREATE TABLE EAV -- no key declared
(key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL);

INSERT INTO EAV VALUES ('LOCATION','Bedroom');
INSERT INTO EAV VALUES ('LOCATION','Dining Room');
INSERT INTO EAV VALUES ('LOCATION','Bathroom');
INSERT INTO EAV VALUES ('LOCATION','courtyard');
INSERT INTO EAV VALUES ('EVENT','verbal aggression');
INSERT INTO EAV VALUES ('EVENT','peer');
INSERT INTO EAV VALUES ('EVENT','bad behavior');
INSERT INTO EAV VALUES ('EVENT','other');

CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI
(id INTEGER IDENTITY (1,1) NOT NULL,
bts_id INTEGER NULL,
key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL );

INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');
INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression');
INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');

Ideally, the result set of the query would be Location Event count
(headings if possible)

Bedroom verbal aggression 1
Bedroom peer 0
Bedroom bad behavior 0
Bedroom other 2
Dining Room verbal aggression 0
Dining Room peer 0
Dining Room bad behavior 0
Dining Room other 0
Bathroom verbal aggression 0
Bathroom peer 0
Bathroom bad behavior 0
Bathroom other 0
courtyard verbal aggression 0
courtyard peer 1
courtyard bad behavior 0
courtyard other 1

Also, if possible, another query would return this result set. (I think
I know how to do this one.)

Location Event count
Bedroom verbal aggression 1
Bedroom other 2
courtyard peer 1
courtyard other 1

Here is an answer From: Thomas Coleman

SELECT Locations.locationvalue, Events.eventvalue,
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue, EventData.eventvalue

FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event'
) AS EventData
ON LocationData.bts_id = EventData.bts_id
) AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events
ORDER BY Locations.locationvalue, Events.eventvalue ,
SELECT Locations.locationvalue, Events.eventvalue
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue, EventData.eventvalue

FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event') AS EventData
ON LocationData.bts_id = EventData.bts_id)
AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue)
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events;

Is the same thing in a proper schema as:

SELECT L.locationvalue, E.eventvalue, COUNT(*)
FROM Locations AS L, Events AS E
WHERE L.btd_id = E.btd_id
GROUP BY L.locationvalue, E.eventvalue;

The reason that I had to use so many subqueries is that those entities
are all plopped into the same table. There should be separate tables
for Locations and Events.

The column names are seriously painful Don't use "key" and "value" for
column names. It means that the developer *has* surround the column
name with double quotes for everything which is a serious pain.

There is such a thing as "too" generic. There has to be some structure
or everything becomes nothing more than a couple of tables called
"things". The real key (no pun intended) is commonality. Is there a
pattern to the data that they want to store? It may not be possible to
create one structure to rule them all and in the darkness bind them.

"To be is to be something in particular; to be nothing in particular is
to be nothing." --Aristole

All data integrity is destroyed. Any typo becomes a new attribute or
entity. Entities are found missing attributes, so all the reports are
wrong.

Try to write a single CHECK() constraint that works for all the
attributes of those 30+ entities your users created because you were
too dumb or too lazy to do your job. It can be done! You need a case
expression almost 70 WHEN clauses for a simple invoice and order system
when I tried it as an exercise.

Try to write a single DEFAULT clause for 30+ entities crammed into one
column. Impossible!

Try to set up DRI actions among the entities. If you thought the WHEN
clauses in the single CASE expression were unmaintainable, wait until
you see the "TRIGGERs from Hell" -- Too bad that they might not fit
into older SQL Server which had some size limits. Now maintain it.

Best way to force a varchar column to have no whitespace

2006-11-16T10:48:00.000-05:00

SQL Apprentice Question
I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Celko Answers
CONSTRAINT no_white_space
CHECK ( LEW(foo) = LEN (REPLACE (foo, ' ', '')))

You can then nest calls to REPLACE() for tabs,newlines, etc. easily.

Algorithm Question

2006-10-30T11:01:00.000-05:00

SQL Apprentice Question
I have user table named TBL_USER which has userid and username fields.

I have another table named TBL_PRODUCT which need user info in it

Which way you offer me to follow .

1. Having Userid Field in TBL_Product
or
2. Having Username field in TBL_PRODUCT

to have relationship between 2 tables.

Could you explain which way i should follow and wht i should follow ?
You might give a web url ..

Celko Answers
First, stop using uppercase names that violate ISO-11179 rules. Those
prefixes are redundant, mess up the data dictionary.

>> I have user table named TBL_USER which has userid and username field [sic] <<

Columns are not fields. The source of your design problem is that you
do not know what a table, coluymn or row are. Let's try guess at the
proper DDL, since you did not bother to post anything:

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY
CHECK (<>,
user_name VARCHAR(35) NOT NULL,
etc);
.

>> I have another table named TBL_PRODUCT which need user info in it <<

NO! Why is a user attribute part of a set of products??

CREATE TABLE Products
(product_id INTEGER NOT NULL PRIMARY KEY
CHECK (<>,
product_name VARCHAR(35) NOT NULL,
etc.);
.

>> Which way you offer me to follow .. to have relationship between 2 tables. <<

Of course.

CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Products(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE, -- guess at the rules
user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON UPDATE CASCADE
ON DELETE CASCADE, -- guess at the rules
PRIMARY KEY (user_id, product_id),
etc.);

>> Could you explain which way I should follow and why i.. <<

Any book on basic data modeling will tell you. A table is not a file;
it represents a set of entities of the same kind and their attributes
or it models a relationship -- never both in one table.

SQL search returning duplicate values

2006-10-30T10:55:00.000-05:00

SQL Apprentice Question
I am having some trouble. I am doing some db search tests before
moving from access to mysql/sql server. Before I get into the Full
Text Search battle.....

If I try to do the following and there are multiple stores who have the
same book, all values are returned, which make duplicate entries in the
results. How do I accomplish the following but get a single price
value? How do I get the lowest, highest, middle, n'th etc value?

SELECT DISTINCT tblBook.*, tblStore.Price
FROM tblBook INNER JOIN tblStore ON tblBook.ID = tblStore.BookID
WHERE (((tblBook.Description) Like '%value%'));

tblBook tblStore
======== ========
ID------------| ID
Value |------BookID
Description Price

Celko Answers

>> Any help would be greatly appreciated. <<

Why did you make the book price an attribute of a store instead of the
book??? Think about it.

Also, read something about ISO-11179 metadata rules, so you will stop
using things like "tblBook" (a scalar value because it is singular and
it belongs to a piece of furniture -- I would have modeled a set of
book titles in my schema). Why does the book_id attribute change names
from table to table? Did you know that the ISBN-13 is the standard
identifier for books?

In the real world, you would have several prices for a book (wholesale,
retail, discounted, etc.) and they would be with the book. The min and
max prices would be known and then you can use a weighted average to
get a "middle" value. That means you need a quantity at each price
point.

Your current design is too simple for your query. Perhaps you use a
discount scale based on volume, or classes like this:

CREATE TABLE BookSales -- plural name for a set!! no redundant prefixes
(isbn CHAR(13) NOT NULL PRIMARY KEY, -- industry standards
wholesale_price DECIMAL (8,4) NOT NULL,
wholesale_qty INTEGER DEFAULT 0 NOT NULL,
retail_price DECIMAL (8,4) NOT NULL,
retail_qty INTEGER DEFAULT 0 NOT NULL,
etc.);

Start over and get some help from someone who knows how to do the model.

Default IN

2006-10-18T09:57:00.000-04:00

SQL Apprentice Question
I want to pass parameters to a sql query. For example:

WHERE ANS IN (" & MyString & ")

So if MyString = 'This', 'That', 'The Other'

It will populate the Where clause.

But I also want to be able to pass an empty string and have it return
all values for ANS.

Any ideas?

Celko Answers

>> I want to pass parameters to a sql query. For example: <<

This comes up all the time from Newbies who do not work with a compiled
language and have no idea what a parameter is. You will get a FAQ to
the standard kludges for this -- parsing routines without safety
checks, dynamic SQL that can crash, etc.

One answer is that T-SQL can handle up to 1024 parameters (REAL
parameters, not things parsed off a single string in procedural code)
and you will not need more than 100 in the real world. Suddenly, you
have all the power of the compiler to check data, to optimize, etc.

Another answer is to load a working table with a single column. The
bad news is that in the year 2006, SQL is missing basic parts of the
VALUES() clause that would make this very, very easy.

Using one stored procedure to update table created in another?

2006-10-18T09:45:00.000-04:00

SQL Apprentice Question
I have a stored procedures which creates a new table and then runs a
specific query. The results of said query are then inserted into the
newly created table. Now, I have another stored procedure which
should, in theory, update that new table with extra data.

I thought I could just do an update in the second sp but I'm hitting
some problems. Mainly, it's telling me that there's an invalid column
name, error 207, although I copied and pasted the name so the spelling
should match. It tells me that "observation_report_id" is the invalid
column name.

If it;s not the spelling of the name that's wrong, then what else could
be it be? Is there something else I'm missing that could bring up the
207 error?

If I can't get this working, could I do it with parameters? How would
I go about passing the unique key for each row from sp1 to sp2?

Thanks for any help,
-M-

Code example:

CREATE PROCEDURE frm_CNE_BasicInfo_pt2
AS

/** run query and insert results **/
update tbl_CNE_BasicInfo
set mainname=[mainname],
heading=[heading],
title=[title],
where obsRepID=observation_report_id

Select obstable.n_number, obstable.taskname as taskname,
obstable.observation_report_id, obstable.stream_id, mainname.mainname,
heading.heading, title.title
from (select p.n_number, tt.name as taskname,
orr.observation_report_id, pp.stream_id
from
parent p, parentpast pp, pastor pr, observationreport orr, trequest tr,
ttype tt
where p.parent_id = pp.parent_id
and pp.pastor_id = pr.pastor_id
and p.record_status = 'A'
and p.parent_id = orr.parent_id
and p.parent_id = tr.parent_id
and orr.trequest_id = tr.trequest_id
and tr.ttype_id = tt.ttype_id
and pp.stream_id = tr.stream_id
)as obstable
left outer join
(select ao.observation_report_id, dt.name, ao.value as mainname
from
atomicobservation ao
inner join dtype dt
on dt.dtype_id=ao.dtype_id
where dt.name = 'mainname')
as mainname
on mainname.observation_report_id=obstable.observation_report_id

etc etc

Celko Answers

>> I have a stored procedure which creates a new table and then runs a specific query. The results of said query are then inserted into the newly created table. Now, I have another stored procedure which should, in theory, update that new table with extra data. <<

This is not how to write an RDBMS. The schema is supposed to be a data
model of some real world situation. Creating tables on the fly is like
elephants appearing out of the sky.

What you seem to be doing is mimicking a file system. Hang a scratch
tape, dump some data to it. In the next *procedural* step, update the
scratch tape. In declarative programming, we try to get the desired
results in one step.

Given column names like "heading" and "title", it looks like you are
formatting a report on the database instead of in your application.
File systems and 3GL programming languages blend data and application
code into the same module, but RDBMS is supposed to be a tiered
architecture.

You can probably kludge your way thru this, but I would re-think how
you code in SQL.

Multiple bit data type in a table

2006-10-18T09:40:00.000-04:00

SQL Apprentice Question
We are going to have a table that store about 300 bit fields (answers to
about 300 yes/no question for a client). I am curious as to whether I need
to split them based on the size of each row. How are mulitiple bit fields
in a table stored in SQL server 2000? Also if each field allows null (or
does not allow), how would that change the size of each row?

Any insight, recommendation, advice would be appreciated.
Thanks

Celko Answers

>> a table that store about 300 bit fields [sic] (answers to about 300 yes/no question for a client). <<

Bits are a low-level assembly language things that are a bitch to do
anything high-level with -- liker stat analysis. Columns are nothing
like fields. You are still thinking in assembly language, not SQL.

>> Any insight, recommendation, advice would be appreciated. <<

Do not design questionaires this way. First of all, there are very few
independent yes/no questions. You are looking for dependencies in the
data; that is the whole point of gathering data.

There are unanswered questions -- people do that! There are
interdependent questions -- "Are you female? No. Have you had cancer of
the uterus? Yes" is clearly wrong and needs a N/A answer. This means a
decision table for the validation rules (you do have those, don't you?)
and flow of questions chart.

Frankly, you probably want to get a questionaire package like RaoSoft
and use it. But if you have to use SQL, then get a normalized schema
and use an encoding for the answers that can be expanded to several
values as needed. Consider multiple choice questions -- nobody like to
do 300 questions.

need help with insert into table from two other tables

2006-10-13T09:59:00.000-04:00

SQL Apprentice Question
have a table with 3000 Client_id's.

I have another table with 56 different order types. The order types
primary key is two fields (search_id, item_id)

I need to insert into another table each client_id, along with each
unique instance (56 instances) of search_id, item_id.

So the new table would have 3000 (client_id's) x 56 (distinct order
types) = 168000 rows.

What would be the easiest to go about this?

Celko Answers
>> I have another table with 56 different order types. The order types primary key is two fields [sic] (search_id, item_id) I need to insert into another table each client_id, along with each unique instance (56 instances) of search_id, item_id. <<

The quick answer is to look up a CROSS JOIN. You can materialize it or
put it in a VIEW.

But a better question is why are you doing this? Does each row (NOT
field) actually represent a fact in the reality of the data model?
That is, does everyone really have all the order types? My guess would
be that you are printing out a form of some kind and want to show all
the options to the users to get them to check off what they really use.
Cross joins do not happen very often in the real world.

Question About Revenue

2006-10-13T09:53:00.000-04:00

SQL Apprentice Question
I have a reports table in my database where i am storing revenue
collected for each particular month.

Table structure
ID Int(4)
Hotel Number char(6)
Month char(1)
Year char(4)

each row can be something like 1001, 1894, 1,2006

I need to write a query which should return text 'N/A' for months that dont
contain data. For example if march 2006 has no revenue then it should return
n/a.

Can someone please assist me?

Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

>> I have a reports table in my database where i am storing revenue collected for each particular month. <<

Your narrative did not have a revenue column :) We will ignore the
fact that computing values and storing them in a table is a bad
programming practice for OLTP apps and should only be done with a data
warehouse app.

CREATE TABLE ComputableSummary
(hotel_nbr CHAR(6) NOT NULL
CHECK (??),
year_month CHAR(7) NOT NULL
CHECK (year_month LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]'),
hotel_revenue DECIMAL(12,2) NOT NULL);

This next one can be part of a general Calendar table

CREATE TABLE ReportPeriods
( cal_date DATETIME NOT NULL PRIMARY KEY,
year_month CHAR(7) NOT NULL
CHECK (year_month LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]'),
etc.);

>> I need to write a query which should return text 'N/A' for months that don't contain data. <<

SELECT S.hotel_nbr, R.year_month, S.hotel_revenue
FROM ReportPeriods AS R
LEFT OUTER JOIN
ComputableSummary AS S
ON R.year_month = S.year_month
WHERE .. ;

And then following the basic rules of a tiered architecture, you would
do the display in the front end for NULL revenues. However, if you
want to be a bad programmer, kludge it with:

COALESCE (CAST(S.hotel_revenue AS CHAR(12)), 'N/A') AS revenus

Update Priority Field

2006-10-13T09:48:00.000-04:00

SQL Apprentice Question
I am trying to create a priority field to organize a list of tasks.
The following 3 fields are being used: EventID (PK, int, not null),
Name (nvarchar(50), not null), pri_ss (int, null).
It would be nice if the code did several things at once:
1. Automitically add items to the list with the max pri_ss number +
1.
(i.e. if you have 50 prioritized items in the list and you add
another one, it adds it as pri_ss = 51)
2. If I change the priority of an item I would like the priority of
all the numbers below it have 1 added to their initial number. (i.e.
if i change item 25 to item 5, i want item 5 to become item 6, and item
6 to become item 7, and so on)
My environment is SQL Server 2000, and i am somewhat fimiliar with.
Any code from SQL experts would be a great help!

Celko Answers

Here is an old posting about manipulating such numberings:

Given a motorpool with numbered parking spaces, you want to move the
automobiles around.

CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(17) NOT NULL);

Re-arrange the display order based on the parking_space column:

CREATE PROCEDURE SwapVehicles (@old_parking_space INTEGER,
@new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space
WHEN @old_parking_space
THEN @new_parking_space
ELSE parking_space + SIGN(@old_parking_space - @new_pos)
END
WHERE parking_space BETWEEN @old_parking_space AND @new_parking_space
OR parking_space BETWEEN @new_parking_space AND @old_parking_space;

When you want to drop a few rows, remember to close the gaps with this:

CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);

append results of a simple select to table without a cursor?

2006-10-06T15:32:00.000-04:00

SQL Apprentice Question
I supect it can be done but I don't see it

scenario:

User has Items and the first user is the Default user

I want all new Users to have the same list of items as the default user

Select * from Users
right join Items
on User.UserID = Items.UserID
where User.LocalID = 0 -- This is the default user

the above gets me the list of x items for the default Customer

Is it possible to append this list to Items for the new User, without using
a cursor?

Thanks

Celko Answers

>> I want all new users to have the same list of items as the default user <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. ISO-11179 prefers that you use collective or plural names for
tables because they are sets, unless they really do model single
entites. Here is a skeleton of what you need:

CREATE TABLE Users
(user_id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY
CHECK (user_id >= 0),
user_name VARCHAR(35) DEFAULT '{{DEFAULT}}' NOT NULL, --usps
standard length
etc.)

--set up the default user with little known trick
INSERT INTO Users(user_id, user_name, ..)
DEFAULT VALUES;

--table of items needed
CREATE TABLE Items
(item_nbr INTEGER NOT NULL PRIMARY KEY, -- needs standard code
item_name VARCHAR(35) NOT NULL,
etc.);

-- ownership is a relation so it has its own table!
CREATE TABLE Ownership
(user_id INTEGER NOT NULL
REFERENCES Users (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
item_nbr INTEGER NOT NULL
REFERENCES Items(item_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (user_id, item_name),
etc.);

-- Now you need to proc to add new users and get them a default package
to start
CREATE PROCEDURE AddNewGuy
(@new_user_id INTEGER, @new_user_name VARCHAR(35), etc.)
AS
BEGIN
INSERT INTO Users (user_id, user_name, etc.)
VALUES (@new_user_id, @new_user_name, etc.);

INSERT INTO Ownership
SELECT @new_user_id, O.item_nbr
FROM Ownership AS O
WHERE O.user_id = 0;
END;

>> Is it possible to append this list to Items for the new User, without using a cursor? <<

Of course. Why did you even think of using a cursor?

3 value logic. Why is SQL so special?

2006-10-06T15:29:00.000-04:00

SQL Apprentice Question
3 value logic. Why is SQL so special?

what would be the consequences of NULL=NULL being true?

Celko Answers

>>, what would be the consequences of NULL=NULL being true?

NULL=NULL should not be true.
NULL=NULL should not be false.
NULL=NULL should not be UNKNOWN.
NULL=NULL should be NULL. <<

NULL is a missing *attribute* value; UNKNOWN is a *logical* value. The
first rule of NULLs is that they propagate. You can easily set up
contradictions that depend on the order evaluation when you have a
BOOLEAN data type. All SQLK data types must allow NULLs by definition.

NULL OR TRUE = NULL -- by definition
UNKNOWN OR TRUE = TRUE -- by definition

NULL AND TRUE = NULL -- by definition
UNKNOWN AND TRUE = UNKNOWN -- by definition

This is why we have the IS [NOT] [TRUE | FALSE |UNKNOWN]
predicate in SQL-92

Cursor Performance

2006-10-06T15:27:00.000-04:00

SQL Apprentice Question
Cursor performance for a real world application.

I have a table that holds records for a stops along a bus route. Each
records has a number of people getting on, number of people getting
off, a spot check, and a current load column.
The spot check column is the to verify that the sensors on the bus are
working correctly; the most reliable number to be use in the is
SPOT_CHECK.

Table Structure as follows:

ID (identity column)
ROUTE (Description of the bus route)
ONS (# of people getting on)
OFFS (# of people getting off)
SPOT_CHECK (visual spot count of people on the bus)
LOAD (Calculated load on the bus)

ID ROUTE ONS OFFS SPOT_CHECK LOAD
1 AAAA 5 0 null
2 AAAA 0 0 null
3 AAAA 2 1 null
4 AAAA 0 2 5
5 AAAA 6 3 8
6 AAAA 0 5 null
7 AAAA 1 2 null
8 AAAA 0 1 null
9 AAAA 0 1 null
10 AAAA 0 0 null

Now, I want to calculate the load at each stop along the way.

Load = Previous stops load + current stop ONS - Current stop's OFFS if
SPOT_CHECK is null, otherwise LOAD = SPOT_CHECK

So the results of the above table will be as follows:

ID ROUTE ONS OFFS SPOT_CHECK LOAD
1 AAAA 5 0 null 5
2 AAAA 0 0 null 5
3 AAAA 2 1 null 6
4 AAAA 0 2 5 5
5 AAAA 6 3 10 10
6 AAAA 0 5 null 5
7 AAAA 1 2 4 4
8 AAAA 0 1 null 3
9 AAAA 0 1 2 2
10 AAAA 0 2 null 0

Current programming, we using a cursor and seeing much much slower
performance than in our Oracle app. Does anyone see a way of doing
this load calculation without using a cursor and improving performance?

I can't do simple sums of ons and offs to calculate load, because of
the SPOT CHECK column.

Celko Answers
>> I have a table that holds records [sic] for a stops along a bus route. <<

Please post DDL and do not confuse records and rows.

>> records [sic] has a number of people getting on, number of people getting off, a spot check, and a current load column. <<

The current load is a computed column and we do not store such
computations in a good schema design.

>> The spot check column is the to verify that the sensors on the bus are working correctly; the most reliable number to be use in the is spot_check<<

Why did you use a vague, magical, non-relational, universal "id" column
instead of a relational key? The stops are a known attribute of a
route, but you left them out of the data model in spite of talking
about them.

CREATE TABLE BusTraffic
(route_nbr INTEGER NOT NULL ,
stop_nbr INTEGER NOT NULL,
bus_nbr INTEGER NOT NULL
REFERENCES Buses(bus_nbr),
PRIMARY KEY (route_nbr, stop_nbr),
boarding_cnt INTEGER DEFAULT 0 NOT NULL,
disembark_cnt INTEGER DEFAULT 0 NOT NULL,
spotcheck_cnt INTEGER -- null means not done
);

other stuff not shown might be ..

CREATE TABLE Buses
(bus_nbr INTEGER NOT NULL PRIMARY KEY,
bus_capacity INTEGER NOT NULL,
etc.);

>> Now, I want to calculate the load at each stop along the way... I can't do simple sums of ons and offs to calculate load, because of the SPOT CHECK column. <<

Probably something like this

SELECT T2.route_nbr, T2.stop_nbr,
SUM (CASE WHEN spotcheck_cnt IS NULL
THEN (boarding_cnt - disembark_cnt)
ELSE spotcheck_cnt END) AS bus_load,
(CASE WHEN spotcheck_cnt IS NULL
THEN 'spot checked'
ELSE 'computed' END) AS verification
FROM BusTraffic AS T1, BusTraffic AS T2
WHERE T1.route_nbr = T2.route_nbr
AND T1.stop_nbr <= T2.stop_nbr;

If you can use the OLAP SUM() OVER() functions in SQL, this will be
easier and faster than a self-join.

scramble ssn with sql server

2006-10-06T15:24:00.000-04:00

SQL Apprentice Question
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.

Example #1:
ssn: 123456789 converts to 987654321

Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.

Is there a way to do this?

Celko Answers
>> create table SSN_MASK

(EW_SSN integer identity primary key not null,
OLD_SSN varchar(9)); -- not sure how you defined it <<

SSN is always CHAR(9). Simply numbering it with a proprietary feature
is not that good; you have destroyed the data type. Here is one we
used in procedural code with arrays.

CREATE TABLE SSN_masks
(shift_id INTEGER NOT NULL PRIMARY KEY -- cols 8 & 9
CHECK (shift_id BETWEEN 00 AND 99),
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
col4 INTEGER NOT NULL,
col5 INTEGER NOT NULL,
col6 INTEGER NOT NULL,
col7 INTEGER NOT NULL);

You take the last two digits of the SSN (fastest changing values) and
look up a vector that tells you how to shift the remaining seven
digits.

shift(x) = ABS((x + col_n) % 11-10)

Since you have 100 different masks, the data gets scrambled pretty good
and it destroys the area digits which would otherwise repeat and give
information about the population geographic distribution. This is also
reversible because we preserve the shift identifier in the output;
shift all of them and it is a bitch to unscramble without the shift
array. Then you change the array when you get the next sample.

Table with Foreign and running total

2006-10-05T09:31:00.000-04:00

SQL Apprentice Question
sql server noob here. class Relational DB question - I wanted to see
if my thinking is right and bounce this very simple design question off
you guys.. In particular using ASP.NET as the client and wanted to know
if I should address this on the db side or the client/business layer
side.

3 tables:

Master:
ssn varchar (10) (PK)
phone nchar(10)

Detail
ssn varchar (10) FK ?
datetime datetime ?
amt numeric

Totals:
ssn varchar (10) FK
amt numeric (should hold a running total by ssn of detail.amt)

Whenever a new detail record is entered, of course the master key must
be there, I want to keep Totals.amt current by ssn. Three questions:

1. What's the best way to keep Totals current? A trigger? Call it
automatically using db dependendancy setup? would dotNet or ADO.net
need to know or do anything.

2. I have not tried, but I can set up a compound unique key for detail
that covers ssn and datetime right? and can ssn still be a foreign key
to Master? That did not seem trivial in sql server 2000.. at a high
level how?

3. I've know some look down on the idea of running totals living inside
the db, afterall Totals.amt can be calculated at any given time. I've
seen in other designs like this, Is this terribly unacceptable? Even if
a poor design, I'm still looking for how to maintain integrity for this
design. Also wonder, would it be terrible to maintain integrity with
client code?

If you have any code or samples that would be greatly appreciated.

Celko Answers
CREATE TABLE Callers
(ssn CHAR(9) NOT NULL PRIMARY KEY, --you got the size wrong!
phone_nbr CHAR(10) NOT NULL -- us only, fixed length?
);

CREATE TABLE CallLog
(ssn CHAR(9) NOT NULL
REFERENCES Callers(ssn)
ON UPDATE CASCADE,
call_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
call_amt DEICMAL (8,2) NOT NULL
PRIMARY KEY (ssn, call_time));

CREATE VIEW CallerAcctTotals (ssn, caller_amt_tot)
AS
SELECT C.ssn, COALESCE (SUM(L.caller_amt), 0.00)
FROM Callers AS C
LEFT OUTER JOIN
CallLog AS L
ON C.ssn = L.ssn
GROUP BY C.ssn;

>> Whenever a new detail record [sic] is entered, of course the master key must

be there, I want to keep Totals.amt current by ssn. <<

Rows are not records-- nothing like them at all. Youa re still
thinking in terms of files -- names like Master and Details really show
that mindset -- tape file terms!

>> 1. What's the best way to keep Totals current? A trigger? <<

Triggers? You mean proceudral file system code! No, use a VIEW that
will always be up to date when it is invoked, not an other file thinly
disguised as a table.

>> 2. I have not tried, but I can set up a compound unique key for detail that covers ssn and datetime right? <<

DATETIME is a reserved word and tooooo vague to be a data element name.
And, yes these two columns should be the key for the log of calls
made.

>> can ssn still be a foreign key to Callers? <<

See DDL for use of UNIQUE()

>> 3. I've know some look down on the idea of running totals living inside the db, afterall Totals.amt can be calculated at any given time. I've seen in other designs like this, Is this terribly unacceptable? <<

Might okay for a data warehouse, but not for a production DB.

>> Even if a poor design, I'm still looking for how to maintain integrity for this design. <<

Constantly firing triggers that will drag performance into the ground
and kill it when it gets to production size

>> Also wonder, would it be terrible to maintain integrity with client code? <<

YES. That defeats the whole purpose of RDBMS as the one central tool
for data integrity. The minute someone uses QA or another tool to get
around you app code, the game is over. Oh, and how did you plan on
being sure that ALL zillion app programs do integrity checks the same
way? or at all?

SQL is declarative and not procedural. Your whole approach is wrong.

Creating a view do display normalised data flattened

2006-10-05T09:21:00.000-04:00

SQL Apprentice Question
I have created the following schema to illustrate my question:

CREATE TABLE [dbo].[tblCustomerProperty] (
[cpcusID] [int] NOT NULL ,
[cpproID] [int] NOT NULL ,
[cpValue] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblCustomers] (
[cusID] [int] IDENTITY (1, 1) NOT NULL ,
[cusName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProperties] (
[proID] [int] NOT NULL ,
[proName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCustomers] WITH NOCHECK ADD
CONSTRAINT [PK_tblCustomers] PRIMARY KEY CLUSTERED ([cusID]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblProperties] WITH NOCHECK ADD
CONSTRAINT [PK_tblProperties] PRIMARY KEY CLUSTERED ([proID]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblCustomerProperty] ADD
CONSTRAINT [FK_tblCustomerProperty_tblCustomers] FOREIGN KEY
([cpcusID]) REFERENCES [dbo].[tblCustomers] ([cusID]) ON DELETE CASCADE
,
CONSTRAINT [FK_tblCustomerProperty_tblProperties] FOREIGN KEY
([cpproID]) REFERENCES [dbo].[tblProperties] ([proID]) ON DELETE
CASCADE
GO

This is essentially a very normalized customer database. A number of
properties are defined in the tblProperties table. One record per
customer exists in the tblCustomer table, and one record per
customer/property combination exists in the tblCustomerProperty table.

I imagine this is fairly common in systems that need to be highly
configurable. Obviously it comes with it's performance overheads but
it's very flexible.

Assume the following data
INSERT INTO tblProperties(proID, proName) VALUES(1, 'Occupation')
INSERT INTO tblProperties(proID, proName) VALUES(2, 'Email')
INSERT INTO tblCustomers(cusName) VALUES('Fred Bloggs')
DECLARE @ID int
SELECT @ID = @@IDENTITY
INSERT INTO tblCustomerProperty(cpcusID, cpproID, cpValue) VALUES(@ID,
1, 'Computer Engineer')
INSERT INTO tblCustomerProperty(cpcusID, cpproID, cpValue) VALUES(@ID,
2, '...@bloggs.com')

My question is how can I create a view that will return a flat view of
customers? The view needs to dynamically include 'columns' specified
in the tblProperties table without having to be changed.

eg.

cusID cusName Occupation Email
1 Fred Bloggs Computer Engineer f...@bloggs.com

Many thanks!

Celko Answers

>> This is essentially a very normalized customer database. <<

Not only is it un-normalized (it never made it to normalized to be
denormalized!), it is full of ISO-11179 violations, lack of relational
keys, data and meta-data are mixed, etc.

What you have is called a EAV (entity-attribute-value) design flaw. It
is a very common design error among people who were asleep in their
database classes :) Google it; it is a disaster that will fall apart
from the lack of data integrity in about one year of production work.

Also, get rid of that silly "tbl-" prefix and camel case -- it makes
you look like an OO programmer who does not know that even MS gave up
on camel case and Hungarian notation.

Seriously, you need to start over from scratch with a real data model
instead a vague "thingies have properties" view of the world. "To be
is to be something in particular; to be nothign in particular or
everything in general is to be Nothing" -- Aristotle

Using GO in stored proc

2006-10-05T09:19:00.000-04:00

SQL Apprentice Question
I need to alter a temp table to add column, and then access that column
in next line.As it cant be possible in a same batch , I need to put a
GO between the lines.But the problem is, these are the lines of a
stored proc,and as soos as query analyzer(sqlserver2000) finds a GO,it
takes it as a last line of the stored proc.

Can't I add column and access the same in a same sp?Is it technically
possible?

Should you have any comments or any other work around , pls share with
me.

Apologies for any inconvenience in understnding the above points.

thanks,

Celko Answers
>> Should you have any comments or any other work around , pls share with me. <<

Do not program this way at all. This is like wanting to modify an
automobile in the middle of a race. You should start the race with a
complete car and not create it on the fly. Temp tables are also a
symptom of a procedural approach to a problem rather than a relational
approach.

Until we have actual specs (i.e. What you want to do) instead of a
kludge request (I.e. how you have decided to do it already), we cannot
help you.

Using Variables & Updating tbl: Update STATUS field from CASE exp

2006-09-28T09:07:00.000-04:00

SQL Apprentice Question
I need help.
I am fairly new in declaring variables & can be put into one SP.

--> Update the [Status] aliased as OLD to the [NEW] value pulled from a CASE
statement.

[MyTable] table to update
[NEW] (PULLS NEW VALUES BY USING CASE EXPRESSIONS)
[OLD] (VALUE IN MYTABLE NEEDING UPDATING)
[Expire Date] Column in MyTable

-- This is what I have so far.. :
SELECT Status AS OLD, (CASE WHEN [Expire Date] < GETDATE() THEN
'Expired' ELSE CASE WHEN [Expire Date] >= getdate()
THEN 'Active' ELSE 'undefined' END END) AS NEW
FROM MyTable
WHERE (Status <> (CASE WHEN [Expire Date] < GETDATE() THEN 'Expired'
ELSE CASE WHEN [Expire Date] >= getdate()
THEN 'Active' ELSE 'UNK' END END))

I want to
-have one SP to declare these as variables
-Then update the table to something like this:

UPDATE MyTable
Set @Old = @New

---
CREATE TABLE MyTable(
[ID] [int] IDENTITY(1,1)
[Status] [char](10),
[Expire Date] [datetime])

Celko Answers

is this what you meant to post so as to have a valid name, with proper
data element names?

CREATE TABLE Items
(item_id INTEGER NOT NULL PRIMARY KEY,
expiry_date DATETIME NOT NULL,
etc.);

Unlike a deck of punch cards, we can use a VIEW (or a computed column
if you really want to be proprietary instead of portable and
maintainable):

CREATE VIEW ItemStatus(item_id, .., item_status)
AS
SELECT item_id, ..,
CASE WHEN expiry_date < CURRENT_TIMESTAMP
THEN 'expired' ELSE 'active' END
FROM Items;

Your syntax for the CASE expression was wrong. The 'unk' path will not
be executed unless you have NULLs, which I assume that you do not.

You are approaching this problem as if you were using punch cards that
have to PHYSICALLY store the data. Your "old" and "new" (reserved
words in SQL!) look like tapes in a 1950's file merge program.

Start thinking LOGICALLY, with predicates and not procedures. Start
using Standard SQL -- SQL Server has most of SQL-92 these days;
CURRENT_TIMESTAMP and not the old UNIX-based getdate(). Read ISO-11179
and learn how to name data elements.

Reply

For SQL Buffs

2006-09-28T09:06:00.000-04:00

SQL Apprentice Question
I have MyTable (ID, BUSINESS_UNIT_ID, CUSTOMER_NUMBER). I need a list
of customer numbers that exist in more than one BUSINESS_UNIT_ID.

Celko Answers
>> I have MyTable (ID, BUSINESS_UNIT_ID, CUSTOMER_NUMBER). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

My guess is that "id" is a totally redundant, non-relational rowing
numbers that newbies who never read anything on RDBMS use. Did you
mean

CREATE TABLE CustomerAssignments
(business_unit INTEGER NOT NULL
REFERENCES OrgChart(business_unit_id)
ON UPDATE CASCADE,
customer_id NTEGER NOT NULL
REFERENCES Customers (customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE ,
PRIMARY KEY (business_unit_id, customer_id));

>> I need a list of customer numbers that exist in more than one BUSINESS_UNIT_ID.<<

SELECT customer_id
FROM CustomerAssignments
GROUP BY customer_id
HAVING COUNT(*) > 1;

Without the key, you would have to use COUNT(DISTINCT business_unit_id)

Good Technique For Removing Dupes?

2006-09-28T08:58:00.000-04:00

SQL Apprentice Question
I have a table full of records, some of which are duplicates
and I need to go through the table and remove the duplicate records.
My technique works, but it is extremely slow, and I was wondering if
anyone can tell me a faster method.

Here is a description of my current technique:

1. Declare a cursor and fetch all fields from first record from
TABLE1.
2. SELECT COUNT(*)
FROM TABLE1
WHERE Field1 = @Value1, Field2 = @Value2
3. If COUNT <= 1 THEN
This is not a duplicate record, go to the next record
ELSE
BEGIN
Must be a duplicate record.
DELETE FROM TABLE 1 where Field3 <> @Value 3
Go to the next record.
END

I am wondering if attempting to delete a record from the table
while it is being looped through and SELECTED from is causing it to be
unusually slow.

Does anyone have a different and possibly faster de-duping
algorithm?

Celko Answers
>> I have a table full of records [sic], some of which are duplicates and I need to go through the table and remove the duplicate records [sic].

1) Rows are not records; tables are not files, nor are columns
anything like fields. You are in trouble now because you did not
bother with the constraints that you needed. As soon as you scrub this
data, add those constraints and quit implementing files in SQL.

2) If you want to use cursors, then look up the "DELETE FROM [table
name] .. WHERE CURRENT OF [cursor] ;" syntax.

3) Your pseudo_code was a bit vague as to what makes a duplicate so I
would guess something like this will work:

DELETE FROM Table1
WHERE field3 <
(SELECT MAX(field3) FROM Table_1 AS T2
WHERE Table_1.field1 = T2.field1
AND Table_1.field2 = T2.field2 ;

This says find groupings based on (field1, field2) and keep the single
rows that have the max value in field3. Pretty common problem when
clenang out a history and fiedl3 is a datetime.

Time recording query

2006-09-20T13:47:00.000-04:00

SQL Apprentice Question
Here is an interesting problem:
Lets say I own some imaginary company. Like for all companies, my
employees report their time to some time reporting application.
Following table stores these time recording activities:

EmpId Date Project No_of_hours
1 08/01 P1 4
1 08/01 P2 3
1 08/01 P3 1
1 08/02 P3 7
1 08/02 P1 1
1 08/03 P1 4
1 08/03 P1 4

- In given month, employees spend hours on multiple projects

I want to write a query which entering the begin and end dates and the
emp id will return the projects the employee worked on during that
period and the no_of_hours he worked on each
Output I am looking should be something similar to this:

EmpId Project No_of_hours
1 P1 30
1 P2 15
1 P3 20

Any help is appreciated ...

Celko Answers
The start and stop times are what you should have been catching in the
first place and not the computed hours. Think raw data and single
facts when designing a table. Let me use a history table for price
changes. The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a BETWEEN
predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

SQL Query - A better way?

2006-09-20T13:38:00.000-04:00

SQL Apprentice Question
I have a query that I have written and it works, but it seems a bit
redundant and I am wondering if there is a better way to write it.

My basic problem is I want to pull only 1 record, and that record is
the newest one based on the LASTUPDATE date field.

Here is my attempt at the query

SELECT * FROM ACTION_HISTORY
WHERE CASE_ID = '534623'
AND EVENTNAME='AssignedChanged'
AND LASTUPDATE = ( SELECT MAX(LASTUPDATE) FROM ACTION_HISTORY WHERE
CASE_ID = '534623' AND EVENTNAME='AssignedChanged')
ORDER BY LASTUPDATE DESC;

Celko Answers
>> My basic problem is I want to pull only 1 record [sic], and that record [sic] is the newest one based on the LASTUPDATE date field [sic]. <<

You are confusing rows and records, fields and columns, so yoiu wind up
with a data model that shows events and not facts. Each row should be
complete fact, and temporal facts have durations.

Let me use a history table for price changes. The fact to store is
that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date), -- actaualloy needs more checks
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You then use a BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

SORT THE PHYSICAL ORDER ON A TABLE'S COLUMNS

2006-09-20T09:00:00.000-04:00

SQL Apprentice Question
I'm working in SQL2000 . how can I move the physical position of a column
in a table by script?
for example:

select * from table1

f1 f2 f3
---- ----- -----

desired result:
f3 f2 f1
---- ----- -----

any help would be greatly appreciated.

Celko Answers
>> how can I move the physical position of a column in a table by script? <<

You have the wrong mental model of how RDBMS and SQL work. Think
logical and not physical models.

One of the many reasons that columns are not like fields is that they
have no physical location and are not required to be contigous. You
use a column name to get their data. Likewise, the rows in a table have
no ordering, unlike the records in a file. You locate them by a key.

In a file system, 3GL commands like "READ (a,b,c) FROM FileA" give you
different results than "READ (c,a,b) FROM FileA" while "SELECT a,b,c
FROM TableA" has the same data as "SELECT c,a,b FROM TableA".

What am I missing with this simple query?

2006-09-20T08:59:00.000-04:00

SQL Apprentice Question
I am trying to add the results of this query to a new table using the
procedure below:

INSERT into db.TempCheckHistGN (EmpID,GrossPay,NetPay)
SELECT EmployeeID, SUM(GrossPay), as Expr1, Sum(NetPay) as Expr2 FROM
dbo.tblPACheckHist
WHERE (CheckDate Between '11/1/2005' AND '12/31/2010') AND (EmployeeID >=''
AND EmployeeID <='zzzzzz')
GROUP BY EmployeeID

I get a duplicate key error (EmpID is the key) but I shouldn't since the
records are being summed by EmployeeID

Celko Answers
Are the two data elements emp_id and employee_id actually the same data
element? If so, then use the same name for them. If not, check that
they converted properly on insertion. Let's clean up the code a bit
- ISO-8601 dates, consistent use of BETWEEN and removal of silly
prefixes, etc:

INSERT INTO db.TempCheckHistgn (emp_id, gross_pay, net_pay)
SELECT emp_id, SUM(gross_pay), SUM(net_pay)
FROM dbo.PayCheckHistory
WHERE check_date BETWEEN '2005-11-01' AND '2010-31-2010'
AND emp_id BETWEEN '' AND 'ZZZZZZ' -- is this redundant?
GROUP BY emp_id;

>> I get a duplicate key error (emp_id is the key) but I shouldn't since the records [sic] are being summed by emp_id. <<

Making a guess at the DDL that you did not post, I would assume that
the INSERT INTO has been run twice. I would also guess that emp_id
BETWEEN '' AND 'ZZZZZZ' covers all the personnel identifiers, but
there might ones with numerics and perhaps the all-blank one is
special. It looks weird, tho. That predicate looks more like a
CHECK() than a search condition.

You talked about "records"; you materialize and store computed
data; this is a symptom of still having a file system mindset. This
problem used to happen in file systems when the same transaction tape
was hung by the next shift.

In the relational world, we avoid temp tables of all kinds by using
virtual tables, such as a VIEW, which is guaranteed to be up-to-date
when it is invoked:

CREATE VIEW PaycheckHistorySummary (emp_id, gross_pay_tot, net_pay_tot)

AS
SELECT emp_id, SUM(gross_pay), SUM(net_pay)
FROM dbo.PaycheckHistory
WHERE check_date BETWEEN '2005-11-01' AND '2010-31-2010'
AND emp_id BETWEEN '' AND 'ZZZZZZ' -- is this redundant?
GROUP BY emp_id;

Delete with inner join

2006-09-10T14:46:00.000-04:00

SQL Apprentice Question
Is this the proper way of doing a join in a delete or update?
Is the a better way?
delete useraccount where useraccountid in (
select useraccountid
from UserAccount with (nolock )
inner join employee with (nolock) on employee.personId = UserAccount.PersonId
where EmployerId =66 )

Celko Answers

>> Is this the proper way of doing a join in a delete or update? <<

There is no proper way of oing a join in a delete or update; that
syntax is proprietary and subject to errors.

>> Is the a better way? <<

Assuming that you actually have more than one user account and that you
are concerned with the set of Personnel and not a single employee,
let's use names that follow ISO-11179 rules. You might be able to do
this:

DELETE FROM UserAccounts
WHERE EXISTS
(SELECT *
FROM Personnel AS P
WHERE UserAccounts.person_id = P.person_id
AND P.employer_id = 66);

This can use indexes instead of being forced to materialize a JOIN
result. You can drop the WITH (NOLOCK) options since a statement is
treated as a transaction. Think about predicates and not about
creating table. That is how we did data processing with scratch tapes
in the old days.

Please explain Ternary relationship to me.

2006-09-10T14:44:00.000-04:00

SQL Apprentice Question
There is a very basic, simple ternary relationship.
http://explorer.x-y.net/data/f1.gif

The question is to get choose a possible combination of each A,B,C
entity count, and the number of triples in R.

I thought that since there should be 1 B for each A, max number of A->B
is the number of entities in A. And since there is a C for each A->B,
(A->B)->C is the number of A->B

But I was wrong. But the correct answer is not explained, and I
couldn't find any information about these kind of problem on the text
book. The text book just gives very simple concept explanation nothing
more. Would you please tell me more about ternary relationship and how
to count the number of triples? Thank you in advance.

Celko Answers

>> I couldn't find any information about these kind of problem on the text book. The text book just gives very simple concept explanation nothing more. Would you please tell me more about ternary relationship and how to count the number of triples? <<

You cannot break it into binary relations like you are trying to do.

Fifth Normal Form (5NF), also called the Join-Projection Normal Form or
the Projection-Join Normal Form, is based on the idea of a lossless
JOIN or the lack of a join-projection anomaly. This problem occurs
when you have an n-way relationship, where n > 2. A quick check for
5NF is to see if the table is in 3NF and all the candidate keys are
single columns.

As an example of the problems solved by 5NF, consider a table of house
notes that records the buyer, the seller, and the lender:

HouseNotes
buyer seller lender
==================================
'Smith' 'Jones' 'National Bank'
'Smith' 'Wilson' 'Home Bank'
'Nelson' 'Jones' 'Home Bank'

This table is a three-way relationship, but because many CASE tools
allow only binary relationships it might have to be expressed in an E-R
diagram as three binary relationships, which would generate CREATE
TABLE statements leading to these tables:

BuyerLender
buyer lender
=============================
'Smith' 'National Bank'
'Smith' 'Home Bank'
'Nelson' 'Home Bank'

SellerLender
seller lender
=======================
'Jones' 'National Bank'
'Wilson' 'Home Bank'
'Jones' 'Home Bank'

BuyerSeller
buyer seller
================
'Smith' 'Jones'
'Smith' 'Wilson'
'Nelson' 'Jones'

The trouble is that when you try to assemble the original information
by joining pairs of these three tables together, thus:

SELECT BS.buyer, SL.seller, BL.lender
FROM BuyerLender AS BL,
SellerLender AS SL,
BuyerSeller AS BS
WHERE BL.buyer = BS.buyer
AND BL.lender = SL.lender
AND SL.seller = BS.seller;

you will recreate all the valid rows in the original table, such as
('Smith', 'Jones', 'National Bank'), but there will also be false rows,
such as ('Smith', 'Jones', 'Home Bank'), which were not part of the
original table. This is called a join-projection anomaly.

There are also strong JPNF and overstrong JPNF, which make use of JOIN
dependencies (JD for short). Unfortunately, there is no systematic way
to find a JPNF or 4NF schema, because the problem is known to be NP
complete. This is a mathematical term that means as the number of
elements in a problem increase, the effort to solve it increases so
fast and requires so many resources that you cannot find a general
answer.

working with gaps in date series

2006-09-10T14:31:00.000-04:00

SQL Apprentice Question
I've seen some pretty creative SQL statements that locate
first/last/missing elements in a series but I haven't been able to
adapt any of them to work speedily with my data set.

Here's the problem:

We have a table of services for our clients (about 2 million rows).
The rows are simply the client's ID and the date of service. Rows may
dupe as clients may have more than one service per date.

We need to be able to define a case start and end date for each client
ID. The end date is defined as the last service date with no further
activity within some number (usually 180) of days. Furthermore, I'd
need to enumerate the cases per client.

So using the example below, I'd be looking for results such as:

Client Start End Case
55577 2/01/2004 5/11/2004 1
55577 1/09/2005 1/09/2005 2
55577 3/04/2006 OPEN 3
72395 4/04/2006 OPEN 1

In these cases, the OPEN dates indicate that there has not been a 180
day period of inactivity since the most recent date.

I currently do this in VB, looping through the ServList dataset and
populating a CaseList recordset. It takes about 30mins to run the job.
However, I'd prefer doing it all in a stored procedure and I'd prefer
to do it without use of cursors. Possible?

Thanks for any help/thoughts,
Steve

CREATE TABLE #ServList (
ClientID int
, ServDate smalldatetime)

INSERT INTO #Servlist VALUES (55577, '2/01/2004')
INSERT INTO #Servlist VALUES (55577, '2/01/2004')
INSERT INTO #Servlist VALUES (55577, '5/11/2004')
INSERT INTO #Servlist VALUES (55577, '1/09/2005')
INSERT INTO #Servlist VALUES (55577, '3/04/2006')
INSERT INTO #Servlist VALUES (55577, '5/17/2006')
INSERT INTO #Servlist VALUES (72395, '4/04/2006')
INSERT INTO #Servlist VALUES (72395, '4/05/2006')
INSERT INTO #Servlist VALUES (72395, '4/06/2006')

Celko Answers
>> We have a table of services for our clients (about 2 million rows). The rows are simply the client's ID and the date of service. Rows may dupe as clients may have more than one service per date. <<

Clear specs, thank you!! But weak DDL and you do not seem to know that
SQL uses ISO-8601 date formats, like all other ISO standards do, in the
sample data. Here is my guess at the real DDL:

CREATE TABLE ServiceTickets
(client_id CHAR(5) NOT NULL
REFERENCES Clients(client_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
service_code CHAR(5) NOT NULL
REFERENCES Services(service_code)
ON UPDATE CASCADE,
service_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
service_period INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY (client_id, service_code, service_date));

>> We need to be able to define a case start and end date for each client ID. The end date is defined as the last service date with no further activity within some number (usually 180) of days. Furthermore, I'd need to enumerate the cases per client. <<

Change the way you think for a minute. Data and declarations, not
procedures and computations. SQL, not VB.

>> I currently do this in VB, looping through the ServList dataset and populating a CaseList recordset. It takes about 30 mins to run the job. <<

Build a table with (n) years of these reporting periods of 180 (or
whatever) days. A spreadsheet is great for this kind of thing.

CREATE TABLE CasePeriods -- needs better name
(service_period INTEGER NOT NULL PRIMARY KEY
CHECK (case_period_nbr > 0),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL);

Now we can find the first service_period that a client has.

SELECT T.client_id, MIN(C.service_period)
FROM ServiceTickets AS T, CasePeriods AS C
WHERE T.service_date BETWEEN C.start_date AND C.end_date
AND end_date < CURRENT_TIMESTAMP -- completed periods only
AND service_period = 0 -- unassigned periods
AND ??
GROUP BY T.client_id
HAVING COUNT(*) > 0;

I have a question about the rules. If the guy comes in on day 1, then
comes back on day 180 and 181, are we in the same case or not? If the
guy comes in on day 1, then comes back on day 180 and 182, are we in
the same case or not?

Test this, then use it in an UPDATE statement to put a value in the
service_period column for those rows that are within your (n) day
range. We could hide this in a VIEW, but it looks complex enough to
materialize the service_period values.

>> In these cases, the OPEN dates indicate that there has not been a 180 day period of inactivity since the most recent date. <<

Ther ain't no such date as "Open"; this is why I used a zero reporting
period for the things in process.

An interesting Lastname, Firstname, Middlename challenge

2006-09-10T14:28:00.000-04:00

SQL Apprentice Question
I'm sure you all have seen situations where a field has a combined name
in the format of "Lastname, Firstname Middlename" and I've seen
examples of parsing that type of field. But what happens when the data
within this type of field is inconsistent? Here are some examples

Apple, John A.
Berry John B.
CherryJohn C
Donald John D

How does one parse the data when the data isn't consistent?

Celko Answers
>> How does one parse the data when the data isn't consistent? <<

I get a mailing package and save myself a lot of problems -- why
re-invent the wheel?

Group 1 Software , Melissa Data Corporation and SAA are such
companies. They can scrub mailing lists MUCH better than you can --
unless you want to make a major project of it for a few years.

Change Query Table

2006-09-10T14:27:00.000-04:00

SQL Apprentice Question
have a series of tables in the database that hold orders for of the
months
i.e.
Tbl: June06
OrderNo, Amount
0001 100
102 150

Tbl: July06

What I want to do is, when a user passes in the month say July06, I
should query from table call June06.
Instead of putting the entire query in a string, is there a way to
switch the table names?

My query is long and I have tables for couple years.

Thanks

Celko Answers

>> I have a series of tables in the database that hold orders for of the months <<

"series of tables"??? No such animal in RDBMS. A table models a set
of entities or relationship of the same -- the whole damn set, not
parts of it. This is just basic math and elementary school set theory,
not advanced stuff.

This total screw up has a name -- Attribute Splitting. You take the
values of an attribute and make them into columns or tables in the
schema. Do you also have separate table for employees based on gender
or religion? Same stupid error!

>> What I want to do is, when a user passes in the month say July06, I should query from table call June06. <<

That is one of the MANNNNNY reasons this is screwed up, non-relational
design. You are mimicing a 1950's magentic tape system! LITERALLY!!
The tape labels had "yyddd" so you could keep track of them.

Put everything in one table, create a reporting period table:

CREATE TABLE ReportPeriods
(period_name CHAR(10) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
etc.);

Use a BETWEEN predicate JOIN to classify your data.

Also, name the periods in alphabetic order for sorting. That means
"2006-06" and not "Jun06"; this is a basic programming trick and not
just SQL.

Best way to insert data into tables without primary keys

2006-09-10T14:22:00.000-04:00

SQL Apprentice Question
I am working on a SQL Server database in which there are no primary
keys set on the tables. I can tell what they are using for a key. It
is usually named ID, has a data type of int and does not allow nulls.
However, since it is not set as a primary key you can create a
duplicate key.

This whole thing was created by someone who is long gone. I don't
know how long I will be here and I don't want to break anything. I
just want to work with things the way they are.

So if I want to insert a new record, and I want the key, which is
named ID, to be the next number in the sequence, is there something I
can do in an insert sql statement to do this?

Celko Answers
>> I am working on a SQL Server database in which there are no primary keys set on the tables. <<

By definition, it is not a table at all, but a simple file written with
SQL ..

>> I can tell what they are using for a key. It is usually named ID, has a data type of int and does not allow nulls.<<

Ah yes, the Magical, Universal "id" that God put on all things in
creation. To hell with ISO-11179 and metadata, to hell with Aristotle
and the law of identity!

>> However, since it is not set as a primary key you can create a duplicate key. <<

Duplicate key is an oxymoron

>> This whole thing was created by someone who is long gone. I don't know how long I will be here and I don't want to break anything. <<

A better question; how long can an enterprise with a DB like this
survive? I'd be updating the resume and stealing office supplies.

>> > So if I want to insert a new record [sic], and I want the key, which is named ID, to be the next number in the sequence, is there something I can do in an insert statement to do this? <<

Rows are not anything like records; the failure of the first guy to
understand this is why he mimiced a magnetic tape file system's record
numbers instad of providing a relational key.

The stinking dirty kludge is to use "SELECT MAX(id)+1 FROM Foobar" in
the INSERT INTO statements. Oh, you also need to check for dups and add
a uniqueness constraint (mop the floor and fix the leak).

The right answer is to re-design this system properly.

Update Query in SQL 2005 with inner join

2006-09-10T14:20:00.000-04:00

SQL Apprentice Question
I have the following update query

UPDATE Employee
SET Deactivated = 1
FROM Employee AS Employee_1 INNER JOIN
Assignment ON Employee_1.SSN = Assignment.SSN CROSS
JOIN
Employee
WHERE (Assignment.SCHOOLID = '0') AND (Assignment.TERM IS NULL)

and it updates, but the problem is it is updating the complete table and not
filtering with the where statement.

Any ideas?

Celko Answers
Do you really have just one Employee, as you said with your data
element name? Are you really using assembly language bit flags in SQL?
SQL programmers do not set flags; they use predicates and VIEWs to
find the state of their data. Programmers who work with punch cards
set flags.

SQL programmers also know not to use the proprietary UPDATE.. FROM..
syntax. Here is what I think you were trying to do in Standard,
portable, predictable SQL. I also cleaned up your data element names
to look more like ISO-11179:

UPDATE Personnel
SET deactivated_flag = 1
WHERE EXISTS
(SELECT *
FROM Personnel AS P, Assignments AS A
WHERE P.ssn = Personnel.ssn
AND A.ssn = Personnel.ssn
AND A.school_id = '0'
AND A.school_term IS NULL);

One of the MANY reasons that we do not use bit flags or even have
Booleans in SQL is that when someone modifes Assignments.school_term
your deactivated_flag is wrong. Now you need procedural code in a
trigger to fix this, or to run a stored procedure whenever there is any
doubt.

If you use a VIEW and quit thinking like a punch card programmer, then
the data is *always* correct:

CREATE VIEW ActivePersonnel (..)
AS
SELECT ..
FROM Personnel AS P
WHERE WHERE EXISTS
(SELECT *
FROM Assignments AS A
WHERE A.ssn = P.ssn
AND A.school_id = '0'
AND A.school_term IS NULL);

Identity column as a foreign key - help needed in logic

2006-09-10T14:17:00.000-04:00

SQL Apprentice Question
have these tables as shown below. Say I want to duplicate a condition
group with ID = 10. Notice that there are identity columns in the Conditions
and Values tables. I can use a INSERT INTO ... SELECT FROM to insert new
rows into the ConditionGroups table. But when I get to Conditions and Values
subsequently I will need to get the generated identity value first before I
insert values.

What's the best way to do this? I want to do this in the database itself.
Are cursors avoidable?

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

CREATE TABLE [dbo].[ConditionGroups] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conditions] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ConditionGroupID] [int] NULL ,
[Lhs] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Operator] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Values] (
[ID] [int] NOT NULL ,
[ConditionID] [int] NOT NULL ,
[RhsValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

Celko Answers

>> Can it be better modeled? <<

SQL is for data and **not** for rules. You ought to be using Prolog or
LISP. You are trying to drive nails with a pumpkin. Having said all
that, people will call me mean if I do not give you this kludge:

I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL:

skill = Java AND (skill = Perl OR skill = PHP)

becomes the disjunctive canonical form:

(Java AND Perl) OR (Java AND PHP)

which we load into this table:

CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));

INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');

Assume we have a table of job candidates:

CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));

INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');

The query is simple now:

SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);

You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relational division.

Query Problem

2006-09-10T14:15:00.000-04:00

SQL Apprentice Question
I have an orders table and an order_items table. Simply, they look like
this:

Orders:
ID | Status
-------
0 | New
1 | InProgress
2 | InProgress

Order_Items:
ID | Ord_ID | Supplier | Status
-------------
0 | 0 | Fred | New
1 | 1 | Fred | New
2 | 1 | Fred | Complete
3 | 2 | Fred | New
4 | 2 | Joe | Complete

When Joe wants to view his 'Complete' Orders, he should see order 2,
because all his items for order 2 are complete (even though its
orderstatus is inprogress)

When Fred wants to view his new orders, he should see order 0 and 2
(because all his items for 2 are new), and order 1 should be seen as
inprogress.

How can i write a query which given a supplier and status (either new,
inprogress or complete) will return all the relevant orders?

Thanks

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

Look at that Orders table -- vague "id" that might or might not be a
key (you are not using IDENTITY for order numbers, are you!!??); vague
"status" attribute (what kind of status??). Cann't this be done in a
view off of the OrderItems table instead of mimicing a file? Look up
how to name a data element.

Here is my guess and corrections to your "pseudo-code" non-table

CREATE TABLE Order_Items
(order_nbr INTEGER NOT NULL
CHECK (<< validation rule here>>),
item_nbr INTEGER NOT NULL
CHECK (item_nbr > 0),
supplier_name CHAR(10) NOT NULL
REFERENCES Suppliers (supplier_name)
ON UPDATE CASCADE,
item_status CHAR(1) DEFAULT 'N' NOT NULL
CHECK (item_status IN ('N', 'C'), -- new, completed
PRIMARY KEY (order_nbr, item_nbr));

Notice the use of a relational key, instead of mimicing a tape file
record number? The use of IDENTITY for items in a bill of materials or
order problem screw up things. Use an item number within the order
number.

INSERT INTO Order_Items VALUES (0, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (1, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (1, 2, 'Fred', 'C');
INSERT INTO Order_Items VALUES (2, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (2, 2, 'Joe', 'C');

That is, in Order #2, Fred supplied item #1 and Joe supplied item #2.
Lot easier to track things with a proper design. Now throw out your
redundant table:

CREATE VIEW OrderStatus (order_nbr, supplier, order_status)
AS
SELECT order_nbr, supplier,
CASE WHEN MIN(item_status) = 'New'
THEN 'New'
WHEN MAX(item_status) = 'Complete'
THEN 'Complete'
ELSE 'In Progress' END;
FROM Order_Items
GROUP BY order_nbr, supplier;

The VIEW is always current and you do not have to keep writing to disk
to mimic a physical file.

Why do I get different results with this?

2006-09-10T14:12:00.000-04:00

SQL Apprentice Question
This is taken from a large but not very complex SQL statement.

,SUM (CASE ELUBECOUPONS.TICKETID WHEN NULL THEN 0 ELSE 1 END)

when I execute the statement with the one above the answer is 0 rows.

If I change that statement to:

,SUM (CASE WHEN ELUBECOUPONS.TICKETID IS NULL THEN 0 ELSE 1 END)

the answer is 624,510 rows

Same database, I only changed this one result column.

I clearly don't understand something about the case statement.

Celko Answers
What is the basic rule about NULLs? They cannot be compared to
anything, even eaqch other! You mean to use the other form of CASE
expression:

SUM (CASE WHEN ElubeCoupons.ticket_nbr IS NULL THEN 0 ELSE 1 END)

Since SUM() drops out NULLs, you could use this with numeric ticket
numbers.

SUM (SIGN (ElubeCoupons.ticket_nbr))

Little data modeling thing; a "_nbr" implies a sequence or other
generating rule for the numeric or pseudo-numeric value. "_id" just
says that the value is unique. That is why we talk about ticket
numbers and not ticket identifiers.

Graph Representations

2006-09-09T13:48:00.000-04:00

SQL Apprentice Question
have a problem that's twisting my mind up. The summary of the problem is
that I have table of organizations, each of which can function in one of two
roles at any given time - call them Role A and role B. These organizations
will have relationships between them (I imagine it programatically as a
directed graph or linked list)...possibly to an infinite degree. For
example - representing the organizations by numerals (maybe their primary
key in the table) and the roles as defined above - we might have the
following:

(org 1 in role A -> org 2 in role B -> org 3 in role A...)

1A->2B->3A->4B->5A
|->6A

(org 2 in role A -> org 3 in role B...)

2A->3B->5A
|->2B->4A
|->6A
|->7A

As you can see, each organization will be a "root" node, but then the path
can take nearly progression to and from the other organizations, having an
infinite number of traditional "edges" in a graph. The graph would
ultimately end at one or more "leaf" nodes (as represented above). This
graph represents the relationships between the associated organizations.
There is no mutual exclusion between the paths: in other words, multiple
organizations may have a relationship with 5A (org 5 in role A) - as shown
above.

How in the world do I represent these relationships in a database structure?
Please help.

Celko Answers
Try a modifed nested sets model. Nodes have a compound key:

CREATE TABLE Nodes
(node_id INTEGER NOT NULL
CHECK(node_id > 0),
node_type CHAR(1) NOT NULL
CHECK(node_type IN ('A', 'B'),
PRIMARY KEY (node_id, node_type),
etc.);

The forest of various arrangements of nodes has to identify each tree
in that forest:

CREATE TABLE Forest
(tree_id INTEGER NOT NULL,
node_id INTEGER NOT NULL,
node_type CHAR(1) NOT NULL,
REFERENCES Nodes (node_id, node_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (tree_id, node_id)
lft INTEGER NOT NULL,
rgt INTEGER NOT NULL,
UNIQUE (tree_id, lft),
etc.
);

hierarchical query 2005

2006-09-09T13:31:00.000-04:00

SQL Apprentice Question
I am working with visual studio 2005 and sql server 2005 workgroup edition.
I have three tables where each row has an ID and a PID.
What I want to do is create either a hierarchical query to fill a data set
or create the dataset itself as an hierarchial dataset.
Is this possible with the tools I`m using and if so, how?

Celko Answers
>> I have three tables where each row has an ID and a PID. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Also, why did you think that "id" and "pid" have any meaning to other
people? "id" is a postfix in ISO-11179 which is too vague to stand by
itself -- it begs the question "identiifer of what?" by its very
nature.

>> What I want to do is create either a hierarchical query to fill a data set or create the dataset itself as an hierarchial dataset. <<

What is a " hierarchical query"? That term was never used in SQL while
I was on the Committee. You might want to get a copy of TREES &
HIERARCHIES IN SQL for *several* different ways to model a hierarchy or
a tree. But first, you might want to learn some basics.

SQL Apprentice Question
I`m sorry for being a bit unclear.
the tables are:

category:
cat_id int (PK)
cat_name nvarchar

subcategory:
subcat_id int (PK)
parent_id int
subcat_name nvarchar

items:
item_id int (PK)
parent_id int (can be category or sub-category)
item_name nvarchar
item_desc nvarchar

I want to display the data (in some control, tree or other, I`m open to
suggestions)
so that each category contains the items and sub-categories where the parent
id is the id of the category and so on going into the hierarchy.

Celko Answers

>> I`m sorry for being a bit unclear. <<>> );

Google "nested sets model" explain the following

CREATE TABLE FishTaxonomy
(fish_id INTEGER NOT NULL
REFERENCES Fishes (fish_id)
ON UPDATE CASCADE,
lft INTEGER NOT NULL UNIQUE,
rgt INTEGER NOT NULL UNIQUE,
CHECK(lft < rgt) );

A given fish_id and all their superiorss, no matter how deep the tree.

SELECT F2.*
FROM FishTaxonomy AS F1, FishTaxonomy AS F2
WHERE F1.lft BETWEEN F2.lft AND F2.rgt
AND F1.fish_id = :my_fish_id;

2. The fish_id and all their subordinates. There is a nice symmetry
here.

SELECT F1.*
FROM FishTaxonomy AS F1, FishTaxonomy AS F2
WHERE F1.lft BETWEEN F2.lft AND F2.rgt
AND F2.fish_id = :my_fish_id;

Passing parameter into SP for permissions?

2006-09-09T13:25:00.000-04:00

SQL Apprentice Question
How can I pass a user name into a stored procedure I've created that
assigns certain table and SP permissions? The EXECUTE doesn't seem to
allow variables when permissions are involved. It wants literals, i.e.
'John' instead of @username.

Celko Answers
>> How can I pass a user name into a stored procedure I've created that assigns certain table and SP permissions? <<

You can probably do it with dynamic SQL, but why not use the DCL and
keep your system secure?

You do have a security officer who creates and monitors the user
accounts, don't you? Or do you really do it at the application level
on the fly? If so, have you told the security officer and the auditors
about this "feature" to subvert authority?