working with gaps in date series

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

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

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

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

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

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

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?

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

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

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?

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?

How to erase hundreds of DEFAULT values?

SQL Apprentice Question
I'm newbie (yet) in MS SQL2k and I have a big problem, so I would like
to please for help:


There are 9 big databases with a _lot_ of user tables.


I had to insert 2 new fields into _every_ user tables (at the end). It
succeeded, but noticed that I made a mistake: set a Default value for
them. But they should had been empty :-/


These are the new fileds:


Modify_vC varchar (50) NULL Default: suser_sname()
Modify_Dt datetime NULL Default: getdate()


So I wrote (mainly copy-pasted from this newsgroup Thanks for it! :) a
script, but it does not change DEFAULT's value.


(
I tried also to attach "DEFAULT NULL" at the end of this line, but it
throws an error.
exec ('ALTER TABLE ' + @table_name + ' ALTER COLUMN Modosito_vC
varchar (50) NULL DEFAULT NULL')
)


How can I erase DEFAULT's value for these two fields? By hand, it would
take for a year...
(to run 9x (9 DB's) is OK, but to table to table would be horrible)


Thanks for your help.
Bálint


The script:
------------------------- script --------------------------
USE database1


declare @table_name sysname
declare tables_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name)


from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0


open tables_cursor


while 1 = 1
begin
fetch next from tables_cursor into @table_name


if @@error != 0 or @@fetch_status != 0 break


exec ('ALTER TABLE ' + @table_name + ' ALTER COLUMN Modosito_vC
varchar (50) NULL')
exec ('ALTER TABLE ' + @table_name + ' ALTER COLUMN Modositas_Dt
datetime NULL')


end


close tables_cursor
deallocate tables_cursor


go


Celko Answers
>> had to insert 2 new fields [sic] into _every_ user tables (at the end). It succeeded, but noticed that I made a mistake: set a Default value for them. But they should had been empty :-/ <<


No, they should not have been added to the schema at all. First of
all, they are not attributes in a proper data model. They have nothing
to do wiht the entities to which they are attached.

Secondly, their names include their data type in violation of ISO-11179
conventions and good programming. Thaty is a pure newbie thing where
you carry over old programming habits to the new language. Also, not
knowing the columns and fields are totally different concepts.


Third, it is illegal under SOX and several other laws have audit
information in the same schema as the data. The audit trail has to be
external to the data and requires at least two independent
confirmations. Any single user with full rights on your tables can
change or destroy the audit trail.

problem with select

SQL Apprentice Question
I'm trying to do a select and I'm having a problem with it (code below)


declare @teste_varchar2 as varchar(20)
declare @teste_varchar as varchar(500)


set @teste_varchar2 = "valor_fact"
exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
@cont_descCursor)


What is odd with the above code is that if I use a similar code but not
dynamic sql it works.


select valor_fact from ##CONTENC where contracto = @cont_descCursor


Celko Answers
>> I'm trying to do a select and I'm having a problem with it (code below) <<


Oh yes, the old "Britney Spears, Automobile and Squid" code Module!!

The short answer is use slow, proprietrary dynamic SQL to kludge a
query together on the fly with your table name in the FROM clause.


The right answer is never pass a table name as a parameter. You need to
understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A model
of a set of entities or relationships. EACH TABLE SHOULD BE A DIFFERENT
KIND OF ENTITY. When you have many tables that model the same entity,
then you have a magnetic tape file system written in SQL, and not an
RDBMS at all.


If the tables are different, then having a generic procedure which
works equally on automobiles, octopi or Britney Spear's discology is
saying that your application is a disaster of design.


1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.


2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember the
basics of Software Engineering? Modules need weak coupling and strong
cohesion, etc. This is far more fundamental than just SQL; it has to
do with learning to programming at all.


3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.


4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.

View limitations?

SQL Apprentice Question
I was trying to do a complex view with declares, sets that use selects, and
the main query which uses case statements. I've tried to create this view
using Enterprise manager and VS, but the result is always the same.

Once I have the whole shebang down, I run it to make sure it works, which it
does. However, when I try to save the view, I get two different errors:


VS: Incorrect syntax near the keyword declare
the view starts with the following lines:
DECLARE @roomId int, @cabinetSort tinyint
SET @roomId = ...


EM: Does not execute; I get no results, just a message box telling me one
row was affected by the query. Attempting to save, I get the error: View
definition includes no output columns or includes no items in the from clause


What am I missing here?



Celko Answers

>> What am I missing here? <<


The correct definition of a VIEW.

It is not a procedure with parameters or a code module with local
variables It is a virtual table that is defined by a single SELECT
statement, and it also allows the WITH CHECK OPTION clause.


You are still thinking that it is an executable procedural code module.
It is declarative, not procedural.

Indexing question

SQL Apprentice Question
I have a database table that stores the history of a data readings
taken from hardware devices. The hardware device is queried by software
once a minute (or more) and the value stored in the database for
trending an analysis.

The table structure is:
CREATE TABLE DeviceData (
[DataID] [bigint] IDENTITY (1, 1) NOT NULL ,
[DeviceID] [int] NOT NULL ,
[DataValue] [decimal](18, 4) NOT NULL ,
[DataTimestamp] [datetime] NOT NULL
)


After running for a few weeks, the number of rows in the table is
getting large as expected. I started to notice the queries against this
table are taken longer to run. There is currently a primary key
(DataID) and an index on DeviceID. I later realized that since most of
the queries search for a particular date range, an index on
DataTimestamp would definitely help.


Once I added the index, query times for newly added data greatly
improved. The older data, however, still takes longer then I would
like. My question is, once I added the index, is only new data that
gets added to the table indexed? Is there any way to optimize the
queries for the older data.


Being a software developer, not a DBA, any other adivce is greatly
appeciated.


Celko Answers
What have seen, but apparently do not realize is that your IDENTITY
column is redundant and not a key at all. It is an attribute of the
hardware and has nothing to do with the data model. The data_timestamp
is the natural key and should be so delcared:

CREATE TABLE DeviceData
(reading_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
NOT NULL PRIMARY KEY,
device_id INTEGER NOT NULL
REFERENCES Devices(device_id),
device_reading DECIMAL (18,4) NOT NULL );


Names like :"data_value" are vague; isn't what you really have the
device readings in that column?



>> Once I added the index, query times for newly added data greatly improved. The older data, however, still takes longer then I would like. My question is, once I added the index, is only new data that gets added to the table indexed? Is there any way to optimize the queries for the older data.<<


If all the data are in the same table, they all are indexed. First
check the data to be sure that you really have unique timestamps. Drop
the redundant, exposed locator IDENTITY column. Drop your indexes.
Add a primary key constraint.

I think that you have the wrong model of the world. In SQL, there are
DRI constraints which you **must have** if you want to have data
integrity. In SQL Server the uniqueness constraints are enforced by
indexes. Other products do it in other ways (hashing, bit vectors,
etc.) These are called primary indexes. But implement a LOGICAL
concept -- a relational key.


To improve performance, you can add optional indexes (secondary
indexes) to a table. This is vendor dependent and not part of the SQL
Standard at all.


You are still thinking about a file system which does not have the
concept of relational keys and whose indexes are all of the same kind.
In a sequentail file, you locate records (which are not rows) by a
physical position number (which newbies fake with IDENTITY).


It takes at least a year of full time SQL programming, a lot of reading
and a good teacher to get the hang of it. Could be worse; could be APL
or LISP :)

Help with CASE syntax

SQL Apprentice Question
Can anyone tell me what is wrong with the following syntax?

CASE {Invoicing Transaction Amounts History:Item Number}
WHEN like 'GP%'
THEN {Invoicing Transaction Amounts History:Extended Price}
ELSE 0
END


Celko Answers
>> Can anyone tell me what is wrong with the following syntax? <<


well, you made it up :)

The CASE expression is an *expression* and not a control statement;
that is, it returns a value of one datatype. SQL-92 stole the idea and
the syntax from the ADA programming language. Here is the BNF for a
:


::= |


::=
CASE
...
[]
END


::=
CASE
...
[]
END


::= WHEN THEN


::= WHEN THEN


::= ELSE


::=


::=


::= | NULL


::=


The searched CASE expression is probably the most used version of the
expression. The WHEN ... THEN ... clauses are executed in left to
right order. The first WHEN clause that tests TRUE returns the value
given in its THEN clause. And, yes, you can nest CASE expressions
inside each other. If no explicit ELSE clause is given for the CASE
expression, then the database will insert a default ELSE NULL clause.
If you want to return a NULL in a THEN clause, then you must use a CAST
(NULL AS ) expression. I recommend always giving the ELSE
clause, so that you can change it later when you find something
explicit to return.


The is defined as a searched CASE expression
in which all the WHEN clauses are made into equality comparisons
against the . For example


CASE iso_sex_code
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
WHEN 9 THEN 'N/A'
ELSE NULL END


could also be written as:


CASE
WHEN iso_sex_code = 0 THEN 'Unknown'
WHEN iso_sex_code = 1 THEN 'Male'
WHEN iso_sex_code = 2 THEN 'Female'
WHEN iso_sex_code = 9 THEN 'N/A'
ELSE NULL END


There is a gimmick in this definition, however. The expression


CASE foo
WHEN 1 THEN 'bar'
WHEN NULL THEN 'no bar'
END


becomes


CASE WHEN foo = 1 THEN 'bar'
WHEN foo = NULL THEN 'no_bar' -- error!
ELSE NULL END


The second WHEN clause is always UNKNOWN.


The SQL-92 Standard defines other functions in terms of the CASE
expression, which makes the language a bit more compact and easier to
implement. For example, the COALESCE () function can be defined for
one or two expressions by


1) COALESCE () is equivalent to ()


2) COALESCE (, ) is equivalent to


CASE WHEN IS NOT NULL
THEN
ELSE END


then we can recursively define it for (n) expressions, where (n >= 3),
in the list by


COALESCE (, , . . ., n), as equivalent to:


CASE WHEN IS NOT NULL
THEN
ELSE COALESCE (, . . ., n)
END


Likewise, NULLIF (, ) is equivalent to:


CASE WHEN =
THEN NULL
ELSE END


It is important to be sure that you have a THEN or ELSE clause with a
datatype that the compiler can find to determine the highest datatype
for the expression.


A trick in the WHERE clause is use it for a complex predicate with
material implications.


WHERE CASE
WHEN
THEN 1
WHEN
THEN 1
...
ELSE 0 END = 1

SP for poll table

SQL Apprentice Question
This is my poll table:
CREATE TABLE [dbo].[Poll](
[Id] [int] NOT NULL,
[Statement] [nvarchar](500) COLLATE Latin1_General_CI_AI NULL,
[Answer1] [nvarchar](500) COLLATE Latin1_General_CI_AI NULL,
[Score1] [int] NULL,
[Answer2] [nvarchar](500) COLLATE Latin1_General_CI_AI NULL,
[Score2] [int] NULL
)


I have a statement with two answers. If users select answer1 the score1
value has to be updated with + 1.


The stored procedure to update the score will get a '1' (for answer1) or a
'2' (for answer2) (and Id).
The stored procedure should update the Score1 or Score2 column. But how do I
know the correct column name?
And second, I first have to select the current score of that column.


Maybe, I'm thinking the wrong way. But I don't know how to do this.


Thanks for your help,


Celko Answers
Let's fix the DDL first. All those nulls made no sense, you had no
key, etc.

CREATE TABLE Poll
(question_nbr INTEGER NOT NULL PRIMARY KEY,
question_txt NVARCHAR(500) NOT NULL,
answer1 NVARCHAR(500) NOT NULL,
score1 INTEGER DEFAULT 0 NOT NULL
CHECK (score1 >= 0),
answer2 NVARCHAR(500) NOT NULL,
score2 INTEGER DEFAULT 0 NOT NULL
CHECK (score2 >= 0));


CREATE PROCEDURE UpdatePollScores
(@my_question_nbr INTEGER, @my_answer_nbr INTEGER)
AS
UPDATE Poll
SET score1
= score1 + CASE @my_answer_nbr WHEN 1 THEN 1 ELSE 0 END,
score2
= score2 + CASE @my_answer_nbr WHEN 2 THEN 2 ELSE 0 END
WHERE question_nbr = @my_question_nbr;

Why does this query work but that one does not

SQL Apprentice Question
Why does this query work;

SELECT e1.emp_no, e1.emp_lname, e1.domicile, d1.location
FROM employee_enh e1 JOIN employee_enh e2
ON e1.domicile = e2.domicile
JOIN department d1 JOIN department d2
ON d1.location = d2.location
ON e1.dept_no = d1.dept_no
WHERE e1.emp_no <> e2.emp_no


but this one does not?;


SELECT e1.emp_no, e1.emp_lname, e1.domicile, d1.location
FROM employee_enh e1 JOIN employee_enh e2
ON e1.domicile = e2.domicile
JOIN department d1 JOIN department d2
ON e1.dept_no = d1.dept_no
ON d1.location = d2.location
WHERE e1.emp_no <> e2.emp_no


All statements contained in both queries are identical. The change
between the two is that the order of the last two ON statements are
switched.


Query Analyzer gives me the following error;
The column prefix 'e1' does not match with a table name or alias name
used in the query.


What is the rule?


Celko Answers
>> What is the rule? <<


Infixed joins are executed from left to right. The ON clause (if any)
is associated with the most recent JOIN clause. Parentheses are
executed in their order of nesting. No great surprises here -- pretty
much like other scoping rules in 3GLs

A derived table can be constructured with parens and an AS clause.
Only the alias is available to containing queries, not the contained
table names. This catches people.

Easy way to compare the contents of 2 tables ?

SQL Apprentice Question
I've got a couple tables with identical structure...

I would like to create an exception report indicating any differences in the
content of any of the columns (even in the case where one value may be null
and its counterpart a space)... Any ideas on how to build such a query ?

Celko Answers
>> I've got a couple tables with identical structure...<<

That should not happen. A table should contain all the entities of the
same kind in one and only one table.


>> I would like to create an exception report indicating any differences in the content of any of the columns (even in the case where one value may be null and its counterpart a space)... Any ideas on how to build such a query ? <<


If you have SQL-2005

(SELECT * FROM Foo
EXCEPT
SELECT * FROM Bar)
UNION
(SELECT * FROM Bar
EXCEPT
SELECT * FROM Foo)


This does not require that you know the structure of the tables, just
that they are alike.
This is called a OUTER UNION (not the same as an OUTER JOIN!) and is
defined in the SQL-92 Standards. Nobody implements it.

Filtering a query by date threshold

SQL Apprentice Question
The locations of vehicles are received and stored in a database as a lat/lon
value, and accompanied by the datetime timestamp of when the position was
taken. Because of the technology, sometimes vehicles will submit their
position multiple times within a minute, sometimes they are unable to report
(because of visibility) for a few minutes.

I need to write a query that only contains records of vehicle location that
are more than a minute older than the previous record. To clarify, heres
the DDL for my example:


CREATE TABLE [Locations] (
[location_id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[vehicle_id] [int] NOT NULL ,
[timestamp] [datetime] NOT NULL ,
[latitude] [numeric](12, 9) NOT NULL ,
[longitude] [numeric](12, 9) NOT NULL ,
CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
(
[location_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO


insert into locations(vehicle_id, timestamp, latitude, longitude)
values(1, '1/1/2006 11:21:00', 1.1, 2.3)


insert into locations(vehicle_id, timestamp, latitude, longitude)
values(1, '1/1/2006 11:21:15', 1.1, 2.3)


insert into locations(vehicle_id, timestamp, latitude, longitude)
values(1, '1/1/2006 11:21:19', 1.1, 2.3)


insert into locations(vehicle_id, timestamp, latitude, longitude)
values(1, '1/1/2006 11:24:00', 1.1, 2.3)


insert into locations(vehicle_id, timestamp, latitude, longitude)
values(1, '1/1/2006 11:24:49', 1.1, 2.3)


insert into locations(vehicle_id, timestamp, latitude, longitude)
values(1, '1/1/2006 11:27:11', 1.1, 2.3)
go


My intended query for this sample data would only return the records 1, 4
and 6, because the others would be within a minute of the previous record
used. Is this possible within a query or will I need to make use of a
stored procedure for such filtering?



Celko Answers
Time is best modeled as durations, not chronons. IDENTITY cannot ever
be a relational key by definition. TIMESTAMP is a reserved word in SQL
as well as too vague. This is one of the few times I would use FLOAT
over NUMERIC(s,p) because the trig libraries are all in floating point.
I hope the vehicle id is really the VIN, so you can verify and
validate it that will be CHAR(17) with a fancy constraint.


>> I need to write a query that only contains records [sic] of vehicle location that are more than a minute older than the previous record [sic]. <<


Rows are not records and when you use the wrong mental model, you are
going to have problems. The column pairs (arrive_time, depart_time) and
(latitude, longitude) are atomic, but not scalar -- that, they make
sense only as pairs. Some products would let you create or use such
built-in data types; we have to fake it in SQL server.

You are mimicing a log in a procedural system, not the fact you want to
capture. Try this schema, with a more accurate table name:


CREATE TABLE LocationHistory
(vehicle_id INTEGER NOT NULL, -- the VIN, I hope
arrive_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
depart_time DATETIME,
CHECK (arrive_time <= DATEADD (mm, -5, depart_time)),
vehicle_latitude FLOAT NOT NULL,
vehicle_longitude FLOAT NOT NULL,
PRIMARY KEY (arrive_time, depart_time, latitude, longitude)
);


Now you need a procedure that will close out the prior vehicle location
(i.e. the row with the (depart_time IS NULL; use a VIEW to display
these rows as "last known location") and create a new row for the now
current vehicle location. A simply UPDATE and INSERT in one
transaction -- no fancy self-joins at all.


A constraint simply prevents you from storing data that you did not
want to have anyway. Think in non-procedural terms, not in
step-by-step "capture data, filter data" procedures.


Do not whine about the looooong primary key; without it, you would have
no data integrity at all. If it does not have to be right, the answer
is always 42 :)