uninstall SQL: multiple

Showing posts with label multiple. Show all posts

Thursday, March 29, 2012

Case statement with multiple conditions

Obviously the below Case expression does not work. I have conditions on two
fields. If it is true then proceed.
CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
GETDATE()
ELSE
tbl.end_dt
END
Please Help,
Culammy bad,
Solution:
CASE WHEN su.deleted_flag = 1
THEN
CASE WHEN su.end_dt IS NULL THEN
CONVERT(VARCHAR(11), GETDATE() - 1, 101)
ELSE su.end_dt
END
ELSE
su.end_dt
END
"culam" wrote:

> Obviously the below Case expression does not work. I have conditions on t
wo
> fields. If it is true then proceed.
>
> CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
> GETDATE()
> ELSE
> tbl.end_dt
> END
> Please Help,
> Culam|||> Obviously the below Case expression does not work. I have conditions
> on two fields. If it is true then proceed.
> CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
> GETDATE()
> ELSE
> tbl.end_dt
> END
> Please Help,
> Culam
Apart from = NULL, this should work like you expect it to.
Lasse Vgsther Karlsen
http://usinglvkblog.blogspot.com/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x2A42A1C2

Case Statement in a Stored Procedure

Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
statements in a stored Procedure?
I'm trying to do the following:
CASE
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
cast(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) + '-' +
RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) < 1 THEN cast('BLDG: ' +
RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) AS char(20))
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) < 1 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
ELSE 'no room number'
END
Is there another way of doing this?
Any help would be appreciated.
Thanks>> Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
statements in a stored Procedure? <<
Yes, you can do it. It is well documented in SQL Server Books Online with
examples too. Did you come across any problems using it?
--
- Anith
( Please reply to newsgroups only )|||HI Berny,
This looks fine, but it's hard to tell if there's an alternative without
knowing what the business rules are, what the tables look like, what the
data within those tables looks like, and what the expected output is
supposed to be. A case statement with multiple WHEN statements will work
fine in a stored procedure, but you might be able to accomplish the same
thing using a UNION and specific WHERE Clauses for each UNION. It might be
better, but then again, it might not be...without knowing all of the
details, who knows...
HTH
--
Regards,
Don R. Watters
Data Group Manager
PhotoWorks, Inc.
"Berny" <BlancoB at msn Dot com> wrote in message
news:OrJK7f4uDHA.2148@.TK2MSFTNGP12.phx.gbl...
> Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
> statements in a stored Procedure?
> I'm trying to do the following:
> CASE
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
> cast(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) + '-' +
> RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) < 1 THEN cast('BLDG: ' +
> RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) AS char(20))
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) < 1 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
> RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
> ELSE 'no room number'
> END
> Is there another way of doing this?
> Any help would be appreciated.
> Thanks
>

Tuesday, March 27, 2012

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via SQLMonster.com" wrote:
> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:

> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200603/1
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:

> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1
>sql

Monday, March 19, 2012

Cascading path problem

Hello,
The following SQL code produces the famous multiple cascading paths problem.
How should I design the tables to have the below functionality, but keep the
cascading paths? A Doc doesn't necessarily have to be related to a Folder,
but must be related to a Cust.
Changing ON UPDATE to NO ACTION would solve it partly, but it just doesn't
feel right.
Thanks for any help!
cheers,
Jonah
CREATE TABLE Cust (
usr_name varchar(20) NOT NULL,
usr_pwd varchar(40) NOT NULL,
customer_name nvarchar(50) NOT NULL,
created_date datetime default getdate() NOT NULL,
change_date datetime default getdate() NOT NULL,
deactivate_date datetime default getdate() NULL
) ON [PRIMARY]
GO
ALTER TABLE Cust ADD CONSTRAINT
PK_Cust PRIMARY KEY CLUSTERED
(
usr_name
) ON [PRIMARY]
GO
CREATE TABLE Folders (
folder_id int NOT NULL ,
folder_name nvarchar(20) NOT NULL ,
folder_description nvarchar(150) NULL ,
usr_name varchar(20) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Folders ADD CONSTRAINT
PK_Folders PRIMARY KEY CLUSTERED
(
folder_id
) ON [PRIMARY]
GO
ALTER TABLE Folders ADD CONSTRAINT
FK_Folders_Cust FOREIGN KEY
(
usr_name
) REFERENCES Cust
(
usr_name
) ON UPDATE CASCADE
GO
CREATE TABLE Docs (
doc_id int NOT NULL,
header nvarchar(255) not null,
created_date datetime default getdate() NOT NULL,
updated_date datetime default getdate() NOT NULL,
usr_name varchar(20) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Docs ADD CONSTRAINT
PK_Docs PRIMARY KEY CLUSTERED
(
doc_id
) ON [PRIMARY]
GO
ALTER TABLE Docs ADD CONSTRAINT
FK_Cust_Docs FOREIGN KEY
(
usr_name
) REFERENCES Cust
(
usr_name
) ON UPDATE CASCADE
ON DELETE NO ACTION
GO
CREATE TABLE DocsInFolders
(
doc_id int NOT NULL,
folder_id int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
PK_DocsInFolders PRIMARY KEY CLUSTERED
(
doc_id,
folder_id
) ON [PRIMARY]
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
FK_DocsInFolders_Folders FOREIGN KEY
(
folder_id
) REFERENCES Folders
(
folder_id
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
FK_DocsInFolders_Docs FOREIGN KEY
(
doc_id
) REFERENCES Docs
(
doc_id
) ON UPDATE CASCADE
ON DELETE CASCADE
GOYou say 'A Doc *doesn't necessarily have to be* related to a Folder,
but *must be* related to a Cust.'
Keep cascading on FK_DocsInFolders_Docs, but manage the Cust<--Docs
relationship in procedures. You make it sound as if the importance of the
Cust<--Docs relationship supercedes the importance of the Folders<--Docs
relationship.
Vital relationships should not be cascading.
ML|||Correct. The Cust<--Docs relationship is more importance, but that's why I
have only ON UPDATE CASCADE (to make possible usr_name changes up to date)
and not ON DELETE because I don't want Custs having Docs deleted by mistake.
An SP takes care of that.
So what you are saying is that I should change the ON CHANGE to NO ACTION as
well?
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:601F4976-E669-4865-9D92-FBAF9A16CA8F@.microsoft.com...
> You say 'A Doc *doesn't necessarily have to be* related to a Folder,
> but *must be* related to a Cust.'
> Keep cascading on FK_DocsInFolders_Docs, but manage the Cust<--Docs
> relationship in procedures. You make it sound as if the importance of the
> Cust<--Docs relationship supercedes the importance of the Folders<--Docs
> relationship.
> Vital relationships should not be cascading.
>
> ML|||If usr_name can be changed then using it as a primary key (and/or referencin
g
it from a foreign key table) is really bad practice. Either disallow usr_nam
e
changes or use a better kandidate key.
I wouldn't allow cascades for this one.
ML|||OK. So if I disallow cascades for usr_name, you would consider the design to
be correct?
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:90F300A3-7104-4924-A594-E28AC05D645D@.microsoft.com...
> If usr_name can be changed then using it as a primary key (and/or
> referencing
> it from a foreign key table) is really bad practice. Either disallow
> usr_name
> changes or use a better kandidate key.
> I wouldn't allow cascades for this one.
>
> ML|||As far as I can see, the design is fine. I would, however, do something abou
t
the Folders and Docs entities. Right now you allow a single document to exis
t
in more than one folder, which can lead to problems. The same goes for
folders - you should focus on preventing circular references.
Oh, and if any given document cannot exist in more than one folder, then the
DocsInFolders table is obsolete. You could simply add a nullable folder_id
foreign key to the Docs table (nullable since you've mentioned that a
document need not exist in any folder).
I hope you started on paper. :) And in case you haven't, maybe you'll do it
next time.
ML|||My design question applied to the cascading paths, not the business rules
themselves. One Doc may actually exist in several Folders.
- One Cust may have zero or more Folders
- One Cust may have zero or more Docs not connected to a Folder
- One Folder may have zero or more Docs related
Thus, my question was only related to if there's a better way of designing
the relations and tables to avoid circular references, which now occurs.
FYI, I didn't start on paper. I use Visio.
Thank you,
Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:F981A87A-893F-4940-9EBD-566C385CF1CB@.microsoft.com...
> As far as I can see, the design is fine. I would, however, do something
> about
> the Folders and Docs entities. Right now you allow a single document to
> exist
> in more than one folder, which can lead to problems. The same goes for
> folders - you should focus on preventing circular references.
> Oh, and if any given document cannot exist in more than one folder, then
> the
> DocsInFolders table is obsolete. You could simply add a nullable folder_id
> foreign key to the Docs table (nullable since you've mentioned that a
> document need not exist in any folder).
> I hope you started on paper. :) And in case you haven't, maybe you'll do
> it
> next time.
>
> ML|||No pun intended.
You are right - there is a better way to avoid circular references. You
might find more answers studying trees and hierarchies. Consider this model:
ItemInstance : ItemID : BelongsToInstance : ItemType
ItemInstance is unique.
ItemID can be either cust_id, folder_id or doc_id.
BelongsToInstance is a foreign key referencing ItemInstance.
ItemType designates whether ItemID is customer, folder or document.
Valid relationships are:
1) Customer/Folder/Document
2) Customer/Document
3) Folder/Folder (<-- not sure about this one, but seems logical, however:
parent folder_id must should be equal to child folder_id).
A customer can only exist as a root element (BelongsToInstance is null).
A Document can only exist as a leaf element (its ItemInstance is never
referenced in a BelongsToInstance).
The above constraints could be reinforced through the use of indexed views.
ItemInstance and BelongsToInstance prevent circular references while still
allowing all possible relationships between the three entities.
ML|||Thank you for your detailed answer.
I do have a copy of a trees and hierarchies book which I could take a closer
look into (Joe Celko's Trees and Hierarchies in SQL for Smarties). Maybe I
can find some more answers and examples there.
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:D5B9F5D8-4790-4088-BF18-14DE5119891D@.microsoft.com...
> No pun intended.
> You are right - there is a better way to avoid circular references. You
> might find more answers studying trees and hierarchies. Consider this
> model:
> ItemInstance : ItemID : BelongsToInstance : ItemType
> ItemInstance is unique.
> ItemID can be either cust_id, folder_id or doc_id.
> BelongsToInstance is a foreign key referencing ItemInstance.
> ItemType designates whether ItemID is customer, folder or document.
> Valid relationships are:
> 1) Customer/Folder/Document
> 2) Customer/Document
> 3) Folder/Folder (<-- not sure about this one, but seems logical, however:
> parent folder_id must should be equal to child folder_id).
> A customer can only exist as a root element (BelongsToInstance is null).
> A Document can only exist as a leaf element (its ItemInstance is never
> referenced in a BelongsToInstance).
> The above constraints could be reinforced through the use of indexed
> views.
> ItemInstance and BelongsToInstance prevent circular references while still
> allowing all possible relationships between the three entities.
>
> ML|||What about using a nested sets model for the hierarchy? I am not a big
fanof misxed node trees, but this case is pretty easy:
1) If it is the root node, it is a Customer
2) If it is a leaf node, it is a document
3) other it is a folder
CREATE TABLE DocumentHierarchy
(customer_id INTEGER NOT NULL
REFERENCES Customers
ON UPDATE CASCADE
ON DELETE CASCADE,
folder_id INTEGER -- null means no folder
REFERENCES Folders (folder_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
document_id INTEGER -- null means no document
REFERENCES Documents(doc_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
lft INTEGER NOT NULL CHECK (lft >0),
rgt INTEGER NOT NULL CHECK (rgt >lft),
PRIMARY KEY (customer_id, lft, rgt));
untested. You can also get a copy of TREES & HIERARCHIES IN SQL for
more ideas.

Thursday, March 8, 2012

Cascaded SELECT for multiple fields

Consider 2 tables Tabe1, Table2 which both have int fields fld1, fld2.
I can do a Cascaded Select on 1 field:
SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
or
SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
and I can rewrite it as a INNER JOIN, no problem. So far so good.
But now what happens when it is _BOTH_ pair of fields I wish to check
whether they exist/not exist in Table 2?
For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
Table2)
I have to write this is a INNER JOIN which will work. At least there is a
solution here.
But consider the 4th case. I am stuffed here regardless of doing Cascaded
Select or Join. You want to say
SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 NOT IN (SELECT fld1, fld2 FROM
Table2)
And I cannot rewrite this as an inner join because rows are only considered
when both fields, _jointly_ are absent from Table2.
There must be a solution here for multiple fields.
In real life I do have 2 tables. And I can gurantee that fld1 is present on
both tables. But I am interested in listing the pairs of fields (fld1, fld2)
present on one table but absent on the other.
Question1) Can it be done with Cascade Selects? How?
Question2) Can it be done with Joins? How?
Thanks
Stephen HoweThis should work:
SELECT Table1.fld1, Table1.fld2
FROM Table1 Table1
LEFT JOIN Table2 Table2
ON Table1.fld1 = Table2.fld1 AND Table1.fld2 = Table2.fld2
WHERE Table2.fld1 IS NULL
If the pair exists in table2, then Table2.fld1 won't be NULL.|||> This should work:
> SELECT Table1.fld1, Table1.fld2
> FROM Table1 Table1
> LEFT JOIN Table2 Table2
> ON Table1.fld1 = Table2.fld1 AND Table1.fld2 = Table2.fld2
> WHERE Table2.fld1 IS NULL
> If the pair exists in table2, then Table2.fld1 won't be NULL.
I was not clear in my labeling of cases. They are
Case 1: SELECTing on single fields present on Table 1 that should be
present on Table 2
Case 2: SELECTing on single fields present on Table 1 that should not be
present on Table 2
Case 3: SELECTing on multiple fields present on Table 1 that should be
present on Table 2
Case 4: SELECTing on multiple fields present on Table 1 that should not be
present on Table 2
As far as I know, JOINs can handle Cases 1-3 but not case 4
As far as I know, cascaded Selects can handle Cases 1-2 but not cases 3-4
Hrrrmmm after examination, you are right. Thanks
Stephen Howe|||Does this do what you want?:
select t1.fld1, t1.fld2
from Table1 t1
left outer join Table2 t2
on t1.fld1 = t2.fld1
and t1.fld2 = t2.fld2
where t2.fld1 is null;
HTH
Vern
"Stephen Howe" wrote:

> Consider 2 tables Tabe1, Table2 which both have int fields fld1, fld2.
> I can do a Cascaded Select on 1 field:
> SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
> or
> SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
> and I can rewrite it as a INNER JOIN, no problem. So far so good.
> But now what happens when it is _BOTH_ pair of fields I wish to check
> whether they exist/not exist in Table 2?
> For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
> Table2)
> I have to write this is a INNER JOIN which will work. At least there is a
> solution here.
> But consider the 4th case. I am stuffed here regardless of doing Cascaded
> Select or Join. You want to say
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 NOT IN (SELECT fld1, fld2 FR
OM
> Table2)
> And I cannot rewrite this as an inner join because rows are only considere
d
> when both fields, _jointly_ are absent from Table2.
> There must be a solution here for multiple fields.
> In real life I do have 2 tables. And I can gurantee that fld1 is present o
n
> both tables. But I am interested in listing the pairs of fields (fld1, fld
2)
> present on one table but absent on the other.
> Question1) Can it be done with Cascade Selects? How?
> Question2) Can it be done with Joins? How?
> Thanks
> Stephen Howe
>
>
>|||Stephen Howe (stephenPOINThoweATtns-globalPOINTcom) writes:
> I can do a Cascaded Select on 1 field:
> SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
> or
> SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
> and I can rewrite it as a INNER JOIN, no problem. So far so good.
> But now what happens when it is _BOTH_ pair of fields I wish to check
> whether they exist/not exist in Table 2?
> For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
> Table2)
There is in ANSI SQL I believe, but ont implemented in SQL Server.
Anyway, all you need to learn is the EXISTS clause:
SELECT fld1, fld2
FROM Table1 a
WHERE EXISTS (SELECT *
FROM Table2 b
WHERE a.fld1 = b.fld1
AND a.fld2 = b.fld2)
For the corresponding to NOT IN, use NOT EXISTS.
EXISTS/NOT EXISTS is in my opinion superior to IN/NOT IN. There is a
trap with nullable columns that you easily can fall into with NOT IN.
Does not happen with NOT EXISTS.
Performancewise, IN/NOT IN queries are usually rewritten internally
to EXISTS /NOT EXISTS, so there is no difference.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Performancewise, IN/NOT IN queries are usually rewritten internally
> to EXISTS /NOT EXISTS, so there is no difference.
Thanks Erland and everyone else.
My mind has been opened.
Stephen Howe

Saturday, February 25, 2012

Capturing value of identity column for use later?

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @.@.IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets pushed up to the DB by some VB code.
Definition below the sample coding.

The "@.WeekReportedID" value was successfully captured when
previous coding inserted six records into that table: one for
each date range (i.e. column in the UI screen)

Sounds like I'm approaching this wrong.

Suggestions on the right way to go about it?

-------
INSERT INTO tblWeekReportedLine
(
WeekReportedID,
RelativeLineNumber,
ProjectID
)
SELECT
@.WeekReportedID1,
#EstimateLines.RelativeLineNumber,
#EstimateLines.ProjectID
FROM#EstimateLines;

SET@.CurWeekReportedLineID = @.@.IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@.WeekReportedID1,
@.CurWeekReportedLineID,
@.BeginDate1,
Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM#EstimateLines;
--------

The #Temp table create via VB:
--------

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNumber int, " & _
" Available1 decimal(5,2) Default 0, Estimate1
decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _
" Available2 decimal(5,2) Default 0, Estimate2
decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _
" Available3 decimal(5,2) Default 0, Estimate3
decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _
" Available4 decimal(5,2) Default 0, Estimate4
decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _
" Available5 decimal(5,2) Default 0, Estimate5
decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _
" Available6 decimal(5,2) Default 0, Estimate6
decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _
" );"
--------

--
PeteCresswell"(Pete Cresswell)" <x@.y.z> wrote in message
news:dhmtov0kgau16c4opg7ktc2d69agtieh0t@.4ax.com...
> This doesn't work because the first INSERT is creating multiple
> records for multiple projects. @.@.IDENTITY, then, contains the Identity
> column value for the last tblWeekReportedLine record inserted.
> Consequently, all the hours records are then associated with
> that last value.

<snip
Without full DDL (including keys) and knowing how you populate your
variables, this is a guess, but it may be along the right lines - you can
join onto the tblWeekReportedLine table to get the identity values:

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
w.WeekReportedID,
w.IdentityColumn,
@.BeginDate1,
e.Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM #EstimateLines e
join tblWeekReportedLine w
on e.ProjectID = w.ProjectID and
e.RelativeLineNumber = w.RelativeLineNumber
WHERE w.WeekReportedID = @.WeekReportedID1;

Simon|||RE/
>this is a guess,

Pretty good guess!

I'm still an SQL novice, and haven't learned to stop thinking sequential
processing yet...

Thanks. I may make my Monday deadline yet....

--
PeteCresswell

Friday, February 10, 2012

Cant update a record using a DetailsView and SqlDataSource

Hi,

I'm trying to create a registration page that I've divided into multiple pages (first page for basic details, next page for address, etc.). I insert the record in the first page, and update it in the other pages. I pass the newly created ID to the other pages using the Page.PreviousPage property.

In the second page, I have the SqlDataSource configured as "SELECT * FROM [Table] WHERE ID = ?", and the UpdateCommand is "UPDATE ... WHERE ID = ?".

In Page_Load, I am updating the SelectCommand to "SELECT ... WHERE ID = " & intID, and the UpdateCommand similarly. The I do a dtlsvw.Databind()

But when I go to the next page (the newly created ID is being passed properly), the update doesn't do anything. The new record doesn't contain the values in the detailsview. Can somebody help me out?

Thanks,

Wild Thing

Does your ASP page looks like in this example from .Net help:

This section contains two code examples. The first code example demonstrates how to set theUpdateCommand property of theSqlDataSource control and update data in a Microsoft SQL Server database using theGridView control. The second code example demonstrates how to update data in an ODBC database using theGridView control.

The following code example demonstrates how to set theUpdateCommand property of theSqlDataSource control and update data in a SQL Server database using theGridView control. TheGridView automatically populates theUpdateParameters collection, inferring the parameters from theBoundField objects, and calls theUpdate method when theUpdate link on the editableGridView is selected. This example also includes some post-processing: after a record is updated, a notification e-mail message is sent.

<%@.Page Language="VB" %><%@.Import Namespace="System.Web.Mail" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><SCRIPT runat="server"> Sub OnDSUpdatedHandler(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs) If e.AffectedRows > 0 Then ' Perform any additional processing, ' such as setting a status label after the operation. Label1.Text = Request.LogonUserIdentity.Name & _ " changed user information successfully!" Else Label1.Text = "No data updated!" End If End Sub 'OnDSUpdatedHandler</SCRIPT><HTML> <BODY> <FORM runat="server"> <asp:SqlDataSource id="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees" UpdateCommand="Update Employees SET FirstName=@.FirstName,LastName=@.LastName,Title=@.Title WHERE EmployeeID=@.EmployeeID" OnUpdated="OnDSUpdatedHandler"> </asp:SqlDataSource> <asp:GridView id="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" AutoGenerateEditButton="True" DataSourceID="SqlDataSource1"> <columns> <asp:BoundField HeaderText="First Name" DataField="FirstName" /> <asp:BoundField HeaderText="Last Name" DataField="LastName" /> <asp:BoundField HeaderText="Title" DataField="Title" /> </columns> </asp:GridView> <asp:Label id="Label1" runat="server"> </asp:Label> </FORM> </BODY></HTML>

|||

Hi,

Thanks for your help! I'll go through the code you've attached.

I managed to solve the problem by binding the detailsview to the data source from scratch, and asking it to retrieve the ID parameter for the select statement from the Session Field. I don't know if it's the best alternative, but it's working!

Thanks!

Wild Thing

Can't uninstall or reinstall nonfunctioning sql server

In previous post in the Getting Started section, I discussed problems trying to run queries on multiple databases:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299240#299240&SiteID=1

Nobody could help with the problem. I eventually got a query to run after several attempts at deleting and recreating databases. Then, I was hit with the following error message:

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Nobody could help with that either but based on posts with the same message, I tried uninstalling sql server. It wouldn't uninstall nor can I repair or reinstall. Uninstalling thru Add or Remove Programs only resulted in the program listing being removed from Add or Remove Programs. Most of the sql server programs are still installed. They can be run but continue to malfunction. When I try a new installation, I get a message that the programs are already installed.

Is a complete reinstall of windows xp, programs and data the only solution? I hate to believe it; but, at this point, I see no other choice.

If your machine is of x86, please email me so I can forward a script to you to clean those unwanted SQL Server 2005 Components. What you can do is as follows.

1. In Add/Remove Program, remove all visible SQL Server 2005 components.

2. Run command sc, kill all unwanted SQL Server services such as SQL, OLAP, RS, Agent, ...

3. Run command regedit, remove all of those registry keys related to those unwanted SQL Server 2005 components. Of course, this operation is time-consuming and error-prone. That is why the script is created.

4. Reboot your machine and rerun SQL Server 2005 setup.

|||Thanks for the info! I sure it will come in handy in the future. Unfortunately, I've already done a complete reinstall of winxp and sql server. Hopefully, it will run better this time.|||I also had same problem , Could you give me the SQL 2005 uninstall script ? My server is x86, Please sent to jeffery_jean@.msn.com
|||

I'm having a similar problem and would like to get the script please.

-morten

|||I'm trying to cleanly uninstall SQL Server 2003 (I think) and .NET 1.1. Is there a script for this or uninstall series of steps that need be taken? Please advise.|||I also have problem with removing SQL Server2005. Using Add/Remove Programs does not remove all files.Now I have problem with reinstalling SQL - <Workstation components, Books on line and Development Tools> stage fails with the message: 'There is a problem with Windows Installer package . A program run as part of the setup did not finish as expected.'
Can you forward me the script to completely remove all SQL Server files and registry references? There is not also a way to uninstall SQL Express.
My email: jurekba@.optusnet.com.au
It's a pity that Microsoft again did not do its job to deliver working uninstall software for SQL Server 2005.

|||

this is a problem related to the Windows installer MSI database. It believes that the install is part-finished and gets itself a little screwed up. I'd recommend downloading the Windows Installer CleanUp Utility from here http://support.microsoft.com/default.aspx?scid=kb;en-us;290301 and removing the relevant SQL Server database entries.

Following this retry the install, it should go through fine.

|||I also had same problem after using Remote Desktop to install/Uniinstall SQL 2005 , Could you give me the SQL 2005 uninstall script ? My server is x86, Please sent to andy.seymour@.bigroup.co.uk asap please. regards Andy.|||I'm another one that needs the script. I tried using the MS Uninstall cleanup utility but it still didn't work. The SQLServer 2005 install thinks I have several components installed -- most notably the database itself -- and fails when it can't start the database engine. I don wonder why MS couldn't have accounted for this in the original install program. Oh well.
Thanks
rchrismon@.patmedia.net
|||I am running an x86 install that I need to remove can you send me the script to vaparicio@.cpisolutions.com
thx.
|||I also am having the same problem. Please email the script to James@.azmiller.com|||

Hi,

Can you please send me that cleaning script, I'm having the same problem.

thanks

Bob

|||I have same problem at hand, Could you kindly give me the SQL 2005 uninstall script ? My server is x86, Win XP SP2 Please send to tmp74in at yahoo.com
|||I think microsoft should supply this, since so many people need it! Could you send it to me too? I've tried all of the above and it still won't install.