How do I manage this mess? Thanks!

Say for example I have the following 2 database tables, the first one contains the old employee data, and has the fields shown below:

oldEmployeeID

FirstName

LastName

DateOfBirth

HiringDate

TerminationDate

and another one containing the new employee data with similar fields but instead of oldEmployeeID, it is showing the newEmployeeID.

During the conversion process, something were messed up and instead of putting in the original hiring date of the workers into the new employee database, the conversion date was put in, which, depending on the mood of HR ladies, could be any date, and at the same time, of course, new employee join the company, and we assume their hiring dates were entered correctly. On top of that, there are some employee who were terminated before the conversion took place but we still need to keep a record of that.

And I created a third table, say, emplyeeAll with similar fields to the employee data tables.

So here is what I need to do: if the firstName, lastName and DateOfBirth in the old employee data table and the new employee data table matches, I would assume they are the same employee, hence I would put the information for the employee obtained from the new employee data table to the employeeAll table, with the Hiring Date changed to the Hiring Date of the old employee data table (and do not copy the record from the old employee table to prevent duplicates), otherwise, I would simply copy and paste the data in new and old employee table to my employeeAll table.

I know this is really confusing, but...well...hope you know what I am saying...

Is it possible to have a SQL statement for all these? If so, how should the statement looks like?

Thanks a lot!

Regards,

Anyi

Hi,

If I understand this correctly, (I think I do....perhaps) ... if you have data in two tables (and I know you have three, the last would be the destinationtable employeeAll) and you wish to collect the difference between table 1 and 2 (employeeOld, employeeNew) then the left outer join is your answer...

--If you wish to collect differential rows from old table

select employeeOld.*

from employeeOld

left outer join employeeNew on employeeOld.FirstName = employeeNew.FirstName and employeeOld.DateOfBirth = employeeNew.DateOfBirth

where employeeNew.DateOfBirth is null

Similarly you can mix 'n match the above to collect differetial data from the other two tables...note the 'select' is table where the additional data lies and the where clause (is null) is the comparison table.

Hope it helps

|||

I thinks that something like this is what you want:

Code Snippet

-- This collects the data
INSERT INTO EmployeeAll
SELECT
n.EmployeeID,
n.LastName,
n.FirstName,
n.DateOfBirth,
coalesce( o.HiringDate, n.HiringDate ),
coalesce( o.TerminationDate, n.TerminationDate ),
n.{RemainingColumns}
FROM NewEmployees n
LEFT JOIN OldEmployees o
ON ( n.LastName = o.LastName
AND n.FirstName = o.FirstName
AND n.DateOfBirth = o.DateOfBirth
)

|||

But would this actually copy the rest of the record into EmployeeAll, i.e., the records that only showed up in the old employee table (the employees terminated before the conversion took place) or the records that only showed up on the new employee table (i.e., the employees hired after the conversion was completed)?

Thanks!

Regards,

Anyi

Arnie Rowland wrote:

I thinks that something like this is what you want:

Code Snippet

-- This collects the data
INSERT INTO EmployeeAll
SELECT
n.EmployeeID,
n.LastName,
n.FirstName,
n.DateOfBirth,
coalesce( o.HiringDate, n.HiringDate ),
coalesce( o.TerminationDate, n.TerminationDate ),
n.{RemainingColumns}
FROM NewEmployees n
LEFT JOIN OldEmployees o
ON ( n.LastName = o.LastName
AND n.FirstName = o.FirstName
AND n.DateOfBirth = o.DateOfBirth
)

|||

If you want both the records in the old table that don't exists in the new table, the records in the new table that don't exists in the old table, and the records that are in both (with the corrected HiringDate), then change the JOIN from a LEFT JOIN to a FULL JOIN. (This works in SQL 2005 -NOT SQL 2000.)

If you are using SQL 2000, you will need three queries to accomplish the same task.

How do I make sure only one record is checked?

I have a table of magazine issues. The table are defined as below:

issueID int Unchecked
name varchar(50) Unchecked
title varchar(100) Checked
description varchar(500) Checked
crntIssue bit Checked
archived bit Checked
navOrder int Checked
dateCreate datetime Checked

And here is what I want. Is there a way when inserting/updating or on the table itself to make sure that there is only one record that is marked as the current issue? The way I have it here in my table, any records can have the current issue (crntIssue) field checked. I only want one crntIssue field checked regardless of how many records or issues are in the table. If there is no way to automatically have SQL Server to manage that then that means I must check all the records before hand before the update/insert query, correct?

Hi Charlie,

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS
(
SELECT * FROM SomeTable S
WHERE crntIssue = 0x1 AND --Select all current checked ones
NOT EXISTS
(
SELECT * FROM Inserted I
WHERE I.IssueId = S.IssueId --except the already existing one which is determined with the correlated subquery
)
)
RAISERROR('There is already an issue flagged as active',16,1)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks so much for the help. Will you explain the NOT EXISTS section?|||Done. :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Many thanks for al your help so far. I didn't get a chance to implement it until now. Anway, what do you mean when you wrote "Inserted I"?

|||Inserted and deleted are tables which are available in the trigger context (and only there)

They are present in the following tables:

Update

Insert

Delete

Table Inserted

Containing the new values of the updated rows.

Containing the new values of the Inserted rows.

Table Deleted

Containing the old values of the updated rows.

Containing the deleted rows.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Okay, the more I thought about this the more I got confused. Here's the scenero.

In the Issue table I have four issues:

Summer Issue

Fall Issue

Winter Issue

Spring Issue

All of these issues have a crrntIssue field. Currently the Summer Issue has a true value in the crntIssue field and the rest of the issues have a false value in the crntIssue field. If later on, I decide to update the Issue page and make Fall as the current issue, I want the triger to automatically change the crntIssue field of Fall to true and the rest of issues crntIssue field to false. In the suggested trigger solution above, I don't see where the changes occur. In both cases of query, it's a select statement. So where is the update statement to make all the other issues crntIssue field false? And where is the statement to make the current issue's crntIssue field true?

|||

OK, I guess the problem was not stated clearly, so I assumed that you only want to check for wring entered values, not changing the flag automatically.

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update SomeTable
SET crrntIssue = False
FROM SomeTable S1
INNER JOIN Inserted I
In I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

That should be pretty much of it (did not check wheter syntax or compiling)

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thanks so much for your patience.

Okay, so in your code above, you have two tables involved or just one table (SomeTable)? It seems to me that you have two tables (SomeTable, Inserted) and then I'm not sure what the "S" and "I" stand for. In my scenero (I'm not sure if I even doing this right), it only involve one table (magIssue). So here's what I'm thinking.

If there is an update/insert of magazine issue, check to see if the insert/update query changes the existing crntIssue field to some other issue, if not, leave it alone. If the insert/update query changes the crntIssue of let's say Summer to Fall, then go ahead and make other issues' crntIssue field in the magIssue table false and the crntIssue field of Fall true.

Sorry for my poor explanation.

|||

Sometable was just a sample. In my example I avoid using the same names to make the samples more educational as the posters need to convert it to their environment to manifest the used technolgoy while adopting the sample to their situation:

Code Snippet

CREATE TRIGGER TRG_INS_UPD_magIssue
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update magIssue
SET crrntIssue = False
FROM magIssue S1
INNER JOIN Inserted I
On I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

The S and I are just aliases for the used tables. You will need the inserted table (which is only virtual within the trigger) to know if and which values changed during the inserted / update.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Sorry to bother again. I tried this:

CREATE TRIGGER tgrOLissue

ON magIssue

FOR INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNER JOIN magIssue

In magIssue.IssueId = magIssue.IssueId

WHERE magIssue.crrntIssue = magIssue.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

GO

I tried to parse in MS SQL Server Management Studio and here is the error I got:

Incorrect syntax near the keyword 'In'.

|||Try 'on' instead of 'in'. They are close together on the keyboard.
|||

Okay, this is what I have so far.

Code Snippet

CREATE TRIGGER tgrmagIssue
ON magIssue
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crntIssue = 0x1)
Update magIssue
SET crntIssue = False
FROM magIssue
INNER JOIN Inserted
ON magIssue.issueID = magIssue.issueID
WHERE magIssue.crntIssue = magIssue.True
AND magIssue.issueID != magIssue.crntIssue --except the already existing one which is determined with the correlated subquery

And the error is:

Invalid column name 'True'.

|||Sorry, the part should read:

WHERE magIssue.crntIssue = 0x1

But can you send over a complete list of values for one issue (summer, winter, spring and autuumn ? This would be to redefine the query written above.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Right now I don't have all the issues entered as I'm just starting to create the table. In addition, the issue name or title may change. However, here is what the magIssue table look like:

Colomn Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Checked

sql

How do I make sure only one record is checked?

I have a table of magazine issues. The table are defined as below:

issueID int Unchecked
name varchar(50) Unchecked
title varchar(100) Checked
description varchar(500) Checked
crntIssue bit Checked
archived bit Checked
navOrder int Checked
dateCreate datetime Checked

And here is what I want. Is there a way when inserting/updating or on the table itself to make sure that there is only one record that is marked as the current issue? The way I have it here in my table, any records can have the current issue (crntIssue) field checked. I only want one crntIssue field checked regardless of how many records or issues are in the table. If there is no way to automatically have SQL Server to manage that then that means I must check all the records before hand before the update/insert query, correct?

Hi Charlie,

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS
(
SELECT * FROM SomeTable S
WHERE crntIssue = 0x1 AND --Select all current checked ones
NOT EXISTS
(
SELECT * FROM Inserted I
WHERE I.IssueId = S.IssueId --except the already existing one which is determined with the correlated subquery
)
)
RAISERROR('There is already an issue flagged as active',16,1)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks so much for the help. Will you explain the NOT EXISTS section?|||Done. :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Many thanks for al your help so far. I didn't get a chance to implement it until now. Anway, what do you mean when you wrote "Inserted I"?

|||Inserted and deleted are tables which are available in the trigger context (and only there)

They are present in the following tables:

Update

Insert

Delete

Table Inserted

Containing the new values of the updated rows.

Containing the new values of the Inserted rows.

Table Deleted

Containing the old values of the updated rows.

Containing the deleted rows.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Okay, the more I thought about this the more I got confused. Here's the scenero.

In the Issue table I have four issues:

Summer Issue

Fall Issue

Winter Issue

Spring Issue

All of these issues have a crrntIssue field. Currently the Summer Issue has a true value in the crntIssue field and the rest of the issues have a false value in the crntIssue field. If later on, I decide to update the Issue page and make Fall as the current issue, I want the triger to automatically change the crntIssue field of Fall to true and the rest of issues crntIssue field to false. In the suggested trigger solution above, I don't see where the changes occur. In both cases of query, it's a select statement. So where is the update statement to make all the other issues crntIssue field false? And where is the statement to make the current issue's crntIssue field true?

|||

OK, I guess the problem was not stated clearly, so I assumed that you only want to check for wring entered values, not changing the flag automatically.

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update SomeTable
SET crrntIssue = False
FROM SomeTable S1
INNER JOIN Inserted I
In I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

That should be pretty much of it (did not check wheter syntax or compiling)

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thanks so much for your patience.

Okay, so in your code above, you have two tables involved or just one table (SomeTable)? It seems to me that you have two tables (SomeTable, Inserted) and then I'm not sure what the "S" and "I" stand for. In my scenero (I'm not sure if I even doing this right), it only involve one table (magIssue). So here's what I'm thinking.

If there is an update/insert of magazine issue, check to see if the insert/update query changes the existing crntIssue field to some other issue, if not, leave it alone. If the insert/update query changes the crntIssue of let's say Summer to Fall, then go ahead and make other issues' crntIssue field in the magIssue table false and the crntIssue field of Fall true.

Sorry for my poor explanation.

|||

Sometable was just a sample. In my example I avoid using the same names to make the samples more educational as the posters need to convert it to their environment to manifest the used technolgoy while adopting the sample to their situation:

Code Snippet

CREATE TRIGGER TRG_INS_UPD_magIssue
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update magIssue
SET crrntIssue = False
FROM magIssue S1
INNER JOIN Inserted I
On I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

The S and I are just aliases for the used tables. You will need the inserted table (which is only virtual within the trigger) to know if and which values changed during the inserted / update.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Sorry to bother again. I tried this:

CREATE TRIGGER tgrOLissue

ON magIssue

FOR INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNER JOIN magIssue

In magIssue.IssueId = magIssue.IssueId

WHERE magIssue.crrntIssue = magIssue.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

GO

I tried to parse in MS SQL Server Management Studio and here is the error I got:

Incorrect syntax near the keyword 'In'.

|||Try 'on' instead of 'in'. They are close together on the keyboard.
|||

Okay, this is what I have so far.

Code Snippet

CREATE TRIGGER tgrmagIssue
ON magIssue
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crntIssue = 0x1)
Update magIssue
SET crntIssue = False
FROM magIssue
INNER JOIN Inserted
ON magIssue.issueID = magIssue.issueID
WHERE magIssue.crntIssue = magIssue.True
AND magIssue.issueID != magIssue.crntIssue --except the already existing one which is determined with the correlated subquery

And the error is:

Invalid column name 'True'.

|||Sorry, the part should read:

WHERE magIssue.crntIssue = 0x1

But can you send over a complete list of values for one issue (summer, winter, spring and autuumn ? This would be to redefine the query written above.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Right now I don't have all the issues entered as I'm just starting to create the table. In addition, the issue name or title may change. However, here is what the magIssue table look like:

Colomn Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Checked

How do I make sure only one record is checked?

I have a table of magazine issues. The table are defined as below:

issueID int Unchecked
name varchar(50) Unchecked
title varchar(100) Checked
description varchar(500) Checked
crntIssue bit Checked
archived bit Checked
navOrder int Checked
dateCreate datetime Checked

And here is what I want. Is there a way when inserting/updating or on the table itself to make sure that there is only one record that is marked as the current issue? The way I have it here in my table, any records can have the current issue (crntIssue) field checked. I only want one crntIssue field checked regardless of how many records or issues are in the table. If there is no way to automatically have SQL Server to manage that then that means I must check all the records before hand before the update/insert query, correct?

Hi Charlie,

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS
(
SELECT * FROM SomeTable S
WHERE crntIssue = 0x1 AND --Select all current checked ones
NOT EXISTS
(
SELECT * FROM Inserted I
WHERE I.IssueId = S.IssueId --except the already existing one which is determined with the correlated subquery
)
)
RAISERROR('There is already an issue flagged as active',16,1)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks so much for the help. Will you explain the NOT EXISTS section?|||Done. :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Many thanks for al your help so far. I didn't get a chance to implement it until now. Anway, what do you mean when you wrote "Inserted I"?

|||Inserted and deleted are tables which are available in the trigger context (and only there)

They are present in the following tables:

Update

Insert

Delete

Table Inserted

Containing the new values of the updated rows.

Containing the new values of the Inserted rows.

Table Deleted

Containing the old values of the updated rows.

Containing the deleted rows.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Okay, the more I thought about this the more I got confused. Here's the scenero.

In the Issue table I have four issues:

Summer Issue

Fall Issue

Winter Issue

Spring Issue

All of these issues have a crrntIssue field. Currently the Summer Issue has a true value in the crntIssue field and the rest of the issues have a false value in the crntIssue field. If later on, I decide to update the Issue page and make Fall as the current issue, I want the triger to automatically change the crntIssue field of Fall to true and the rest of issues crntIssue field to false. In the suggested trigger solution above, I don't see where the changes occur. In both cases of query, it's a select statement. So where is the update statement to make all the other issues crntIssue field false? And where is the statement to make the current issue's crntIssue field true?

|||

OK, I guess the problem was not stated clearly, so I assumed that you only want to check for wring entered values, not changing the flag automatically.

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update SomeTable
SET crrntIssue = False
FROM SomeTable S1
INNER JOIN Inserted I
In I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

That should be pretty much of it (did not check wheter syntax or compiling)

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thanks so much for your patience.

Okay, so in your code above, you have two tables involved or just one table (SomeTable)? It seems to me that you have two tables (SomeTable, Inserted) and then I'm not sure what the "S" and "I" stand for. In my scenero (I'm not sure if I even doing this right), it only involve one table (magIssue). So here's what I'm thinking.

If there is an update/insert of magazine issue, check to see if the insert/update query changes the existing crntIssue field to some other issue, if not, leave it alone. If the insert/update query changes the crntIssue of let's say Summer to Fall, then go ahead and make other issues' crntIssue field in the magIssue table false and the crntIssue field of Fall true.

Sorry for my poor explanation.

|||

Sometable was just a sample. In my example I avoid using the same names to make the samples more educational as the posters need to convert it to their environment to manifest the used technolgoy while adopting the sample to their situation:

Code Snippet

CREATE TRIGGER TRG_INS_UPD_magIssue
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update magIssue
SET crrntIssue = False
FROM magIssue S1
INNER JOIN Inserted I
On I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

The S and I are just aliases for the used tables. You will need the inserted table (which is only virtual within the trigger) to know if and which values changed during the inserted / update.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Sorry to bother again. I tried this:

CREATE TRIGGER tgrOLissue

ON magIssue

FOR INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNER JOIN magIssue

In magIssue.IssueId = magIssue.IssueId

WHERE magIssue.crrntIssue = magIssue.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

GO

I tried to parse in MS SQL Server Management Studio and here is the error I got:

Incorrect syntax near the keyword 'In'.

|||Try 'on' instead of 'in'. They are close together on the keyboard.
|||

Okay, this is what I have so far.

Code Snippet

CREATE TRIGGER tgrmagIssue
ON magIssue
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crntIssue = 0x1)
Update magIssue
SET crntIssue = False
FROM magIssue
INNER JOIN Inserted
ON magIssue.issueID = magIssue.issueID
WHERE magIssue.crntIssue = magIssue.True
AND magIssue.issueID != magIssue.crntIssue --except the already existing one which is determined with the correlated subquery

And the error is:

Invalid column name 'True'.

|||Sorry, the part should read:

WHERE magIssue.crntIssue = 0x1

But can you send over a complete list of values for one issue (summer, winter, spring and autuumn ? This would be to redefine the query written above.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Right now I don't have all the issues entered as I'm just starting to create the table. In addition, the issue name or title may change. However, here is what the magIssue table look like:

Colomn Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Checked

How do I make sure only one record is checked?

I have a table of magazine issues. The table are defined as below:

issueID int Unchecked
name varchar(50) Unchecked
title varchar(100) Checked
description varchar(500) Checked
crntIssue bit Checked
archived bit Checked
navOrder int Checked
dateCreate datetime Checked

And here is what I want. Is there a way when inserting/updating or on the table itself to make sure that there is only one record that is marked as the current issue? The way I have it here in my table, any records can have the current issue (crntIssue) field checked. I only want one crntIssue field checked regardless of how many records or issues are in the table. If there is no way to automatically have SQL Server to manage that then that means I must check all the records before hand before the update/insert query, correct?

Hi Charlie,

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS
(
SELECT * FROM SomeTable S
WHERE crntIssue = 0x1 AND --Select all current checked ones
NOT EXISTS
(
SELECT * FROM Inserted I
WHERE I.IssueId = S.IssueId --except the already existing one which is determined with the correlated subquery
)
)
RAISERROR('There is already an issue flagged as active',16,1)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks so much for the help. Will you explain the NOT EXISTS section?|||Done. :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Many thanks for al your help so far. I didn't get a chance to implement it until now. Anway, what do you mean when you wrote "Inserted I"?

|||Inserted and deleted are tables which are available in the trigger context (and only there)

They are present in the following tables:

Update

Insert

Delete

Table Inserted

Containing the new values of the updated rows.

Containing the new values of the Inserted rows.

Table Deleted

Containing the old values of the updated rows.

Containing the deleted rows.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Okay, the more I thought about this the more I got confused. Here's the scenero.

In the Issue table I have four issues:

Summer Issue

Fall Issue

Winter Issue

Spring Issue

All of these issues have a crrntIssue field. Currently the Summer Issue has a true value in the crntIssue field and the rest of the issues have a false value in the crntIssue field. If later on, I decide to update the Issue page and make Fall as the current issue, I want the triger to automatically change the crntIssue field of Fall to true and the rest of issues crntIssue field to false. In the suggested trigger solution above, I don't see where the changes occur. In both cases of query, it's a select statement. So where is the update statement to make all the other issues crntIssue field false? And where is the statement to make the current issue's crntIssue field true?

|||

OK, I guess the problem was not stated clearly, so I assumed that you only want to check for wring entered values, not changing the flag automatically.

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update SomeTable
SET crrntIssue = False
FROM SomeTable S1
INNER JOIN Inserted I
In I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

That should be pretty much of it (did not check wheter syntax or compiling)

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thanks so much for your patience.

Okay, so in your code above, you have two tables involved or just one table (SomeTable)? It seems to me that you have two tables (SomeTable, Inserted) and then I'm not sure what the "S" and "I" stand for. In my scenero (I'm not sure if I even doing this right), it only involve one table (magIssue). So here's what I'm thinking.

If there is an update/insert of magazine issue, check to see if the insert/update query changes the existing crntIssue field to some other issue, if not, leave it alone. If the insert/update query changes the crntIssue of let's say Summer to Fall, then go ahead and make other issues' crntIssue field in the magIssue table false and the crntIssue field of Fall true.

Sorry for my poor explanation.

|||

Sometable was just a sample. In my example I avoid using the same names to make the samples more educational as the posters need to convert it to their environment to manifest the used technolgoy while adopting the sample to their situation:

Code Snippet

CREATE TRIGGER TRG_INS_UPD_magIssue
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update magIssue
SET crrntIssue = False
FROM magIssue S1
INNER JOIN Inserted I
On I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

The S and I are just aliases for the used tables. You will need the inserted table (which is only virtual within the trigger) to know if and which values changed during the inserted / update.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Sorry to bother again. I tried this:

CREATE TRIGGER tgrOLissue

ON magIssue

FOR INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNER JOIN magIssue

In magIssue.IssueId = magIssue.IssueId

WHERE magIssue.crrntIssue = magIssue.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

GO

I tried to parse in MS SQL Server Management Studio and here is the error I got:

Incorrect syntax near the keyword 'In'.

|||Try 'on' instead of 'in'. They are close together on the keyboard.
|||

Okay, this is what I have so far.

Code Snippet

CREATE TRIGGER tgrmagIssue
ON magIssue
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crntIssue = 0x1)
Update magIssue
SET crntIssue = False
FROM magIssue
INNER JOIN Inserted
ON magIssue.issueID = magIssue.issueID
WHERE magIssue.crntIssue = magIssue.True
AND magIssue.issueID != magIssue.crntIssue --except the already existing one which is determined with the correlated subquery

And the error is:

Invalid column name 'True'.

|||Sorry, the part should read:

WHERE magIssue.crntIssue = 0x1

But can you send over a complete list of values for one issue (summer, winter, spring and autuumn ? This would be to redefine the query written above.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Right now I don't have all the issues entered as I'm just starting to create the table. In addition, the issue name or title may change. However, here is what the magIssue table look like:

Colomn Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Checked

How do I loop thru a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

SELECT id, label, url, sort
FROM mainNav
ORDER BY sort

Set @.tc = @.@.rowcount

while @.i <= @.tc

begin
Set @.i = (@.i + 1)

/* Select for submenu items*/
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end

RETURNI'm thinking that what you really want to do is perform a join and then handle the presentation stuff on the client side. I can't think of any valid reason why you would want to write your stored proc in the manner you have outlined above.

SELECT
m.id as MainID,
m.label as MainLabel,
m.url as MainUrl,
m.sort as MainSort,
s.id,
s.label,
s.url,
s.sort
FROM
mainNav m inner join SubNav s
ORDER BY
m.sort,
s.sort

Regards,

hmscott|||Yeah, you definitely seem hazy on the SQL concept. What is the output format you want for your dropdown list? Give us a sample of the data you want to display.|||The objective is to return a .Net dataset that contains a series of recordsets . The first recordset is the items contained in the top bar of the menu site. The subsequest recordsets contain the submenus for each item in the top menu.

The challenge is that it's not a simple binding issue once the data is returned from the stored proc. Some items in the top bar may not have a submenu and therefore require different html and javascript.

Inorder to render a lightweight, css-based (and W3C compliant) menu system, I need to determine at runtime which items have submenus and which don't

- items with no submenu need to have code that only closes other menus
- items with submenus need to have the closing code and also code to open there respective submenu.

A working (static) example would be something like http://peelcas.org/home/index.aspx

In order to pull this off, I have a c# routine that loops thru the dataset and renders the html.

Here's the c# code

///////////////////////////
// build global menu system
///////////////////////////

string sqlConnstring = ConfigurationManager.ConnectionStrings["sqlConnString"].ConnectionString;
DataSet NavData = new DataSet();
NavData = SqlHelper.ExecuteDataset(sqlConnstring, CommandType.StoredProcedure, "OPA_GetMenuItems");
//Response.Write(NavData.Tables.Count.ToString());

int i = 1; // counter for looping thru tables collection
int tc = NavData.Tables.Count - 1;
System.Text.StringBuilder sbNavLinks = new StringBuilder();
System.Text.StringBuilder sbSubLinks = new StringBuilder();
string url;
//string webSectionName;
string label;
string anchorId;
string menuId;

// build main nav bar
sbNavLinks.Append("<div id=\"navBar\"><ul>\r");
while (i < tc)
{
label = NavData.Tables[0].Rows[i].ItemArray[1].ToString();
anchorId = NavData.Tables[0].Rows[i].ItemArray[3].ToString();
url = NavData.Tables[0].Rows[i].ItemArray[2].ToString();

// no submenu items for this web section therefore...
// ...create main nav bar link with global menu closing javascript only
if (NavData.Tables[i].Rows.Count == 0)
{
sbNavLinks.Append("<li><a href=\""
+ url + "\" onmouseover=\"P7_autoLayers(0);\">"
+ label + "</a></li>\r");
}
// has submenu items therefore...
// ...create main navbar link with submenu opening javascript
else
{
// main navbar link
sbNavLinks.Append("<li><a href=\""
+ url + "\""
+ " id=\"Anchor" + anchorId + "\""
+ " onmouseover=\"P7_autoLayers(0,'subMenu" + anchorId + "');"
+ "P7_Snap('Anchor" + anchorId + "','subMenu" + anchorId + "',0,24);"
+ "\">"
+ label + "</a></li>\r");
}
i++;
}
sbNavLinks.Append("</ul></div>\r");
navBar.Text = sbNavLinks.ToString();

// build submenus

int j = 0; // counter for looping thru rows in current table
int rc; // row count of current table
i = 1;
while (i < tc)
{
if (NavData.Tables[i].Rows.Count > 0)
{
// extract menu id for use in div id

menuId = NavData.Tables[i].Rows[0].ItemArray[4].ToString();
//Response.Write(webSectionSort);

sbSubLinks.Append("<div id=\"subMenu" + menuId + "\" style=\"position:absolute; z-index:" + menuId + "; visibility: hidden;\">\r");
sbSubLinks.Append(" <div class=\"subButton\">\r");
sbSubLinks.Append(" <ul>\r");

rc = NavData.Tables[i].Rows.Count -1;

while (j <= rc)
{
// extract data for this link
label = NavData.Tables[i].Rows[j].ItemArray[1].ToString();
url = NavData.Tables[i].Rows[j].ItemArray[0].ToString();

sbSubLinks.Append("<li><a href=\""
+ url + "\" class=\"subButton\">"
+ label + "</a></li>\r");
j++;
}

sbSubLinks.Append("</ul></div></div>\r\r");
}
i++;
j = 0;
}
subNavBar.Text = sbSubLinks.ToString();
NavData.Dispose();

Yes I too dislike all this looping business, but I don't have a way of binding and achieving the desired results...|||What about a left join, instead of an inner join. With a left join, you could easily determine the MainMenu items with no submenus (the submenu fields would be null).

You would still loop through it on the web server.

Alternatively, you could use the FOR XML clause to create an XML string (which you would have to wrap inside of xml tags to make compliant).

Regards,

hmscott|||I'm not a C coder. You need to give us the layout of the recordset that you want to get from the server. We can help you with that, and from there on its up to you.|||Thanks for the offer...

I think hmscott's suggestion of a Left Join might just work, so I will groove on that for a while. As much as you're not a C# guy, I'm not a Sql guy, so I often just need a nudge in the right directlon..

Thanks againsql

How do I loop through a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause usesnot the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDUREdbo.OPA_GetMenuItems
AS
Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = @.i)
ORDER BYmainNavId, sort
end

RETURN

Here's one way: You could get the resultset into a table variable. Add an additional column in the table variable and mark it off as processed after each record.

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

DECLARE @.t tabke (id int, label varchar(100), url varchar(100), sort varchar(100), Processed char(1) )
DECLARE @.minid int

INSERT INTO @.t
SELECT
id, label, url, sort, 'N'
FROM
mainNav
ORDER BY
sort

WHILE EXISTS (SELECT 1 FROM @.t WHERE Processed = 'N')
BEGIN
--Get the first record
SELECT
@.minid = id
FROM
@.t
WHERE
Processed = 'N'
ORDER BY
id

--your processing code
/*
Select for submenu items
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end
*/

--make sure you mark the record as Processed
UPDATE
@.t
SET
Processed = 'Y'
WHERE
id = @.minid

END

|||

Depends on if you want the items to come back in one or multiple recordsets.

Here's a single recordset:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav
UNION
SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's two recordsets:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav

SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's multiple recordsets:

Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = (SELECT TOP 1 FROM (SELECT TOP @.i id,sort FROM mainNav ORDER BY sort,id) ORDER BY sort DESC,id DESC))
ORDER BYsort
end

You can also do it using a cursor if you want, which would probably be easier/faster if you have a LOT of menu items, but I'm guessing that since it's a menu, you are only talking about 5-20 items and not thousands.

|||

Basic cursor logic works like this, but the syntax is NOT correct, I haven't used a cursor in a LONG time:

DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id

OPEN MyCursor
READ NEXT FROM MyCursor INTO @.id
WHILE (@.@.FETCHSTATUS<>-2)
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav WHEREmainNavID=@.id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @.id
END
CLOSE MyCursor
DEALLOCATE MyCursor

That should be pretty close, all except for the READ NEXT FROM... I forget the command/syntax, that definately is not correct. And the cursor declaration is a bit off, but close.

|||

The multiple recordsets approach is the one I'm after - I need an .Net data to spit out the menus back in C#. I tryed your example and it did not compile - Sql came back with:

Incorrect Syntax near keyword 'From'.
Line 24: incorrect syntax:near'@.i'.

any ideas?

|||Ah, you must be using SQL Server 2000, yeah, that's not valid on 2000, only 2005. I'm not sure what your code looks like, but I would do the single resultset approach. Just loop through the resultset, and whatever you would do when you get a new resultset, just do it when the mainNav field changes value.|||

Thanks for your help. I actually took a different direction and managed to get around the problem with a left join...

Thanks again...

how do I keep a table from shifting items below the table

I have a table on a report that will have the max of 6 rows and below that I
need to have other information on the report to print onto fields on a form,
when the rows change in the table the textboxes below get shifted. How do I
stop this from happening?
Thanks,
JimMake your textboxes as large as they should grow and turn off 'CanGrow'.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Ciotuszynski" <jimcio@.hotmail.com> wrote in message
news:uu2Kr%237WEHA.1128@.TK2MSFTNGP10.phx.gbl...
>I have a table on a report that will have the max of 6 rows and below that
>I
> need to have other information on the report to print onto fields on a
> form,
> when the rows change in the table the textboxes below get shifted. How do
> I
> stop this from happening?
> Thanks,
> Jim
>

how do I inner join two table to a dataset

Hello. I'm trying to inner join two tables into one dataset. Is that possible. If so I do I do it. I have my code down below, so if you could help me. please!! I'm using asp.net for my coding
[vb.asp.net-code]
Dim myConnection As New Data.Odbc.OdbcConnection(connectionstring)
Dim mysql As String = "select rmsfiles2.obcop200.line#, rmsfiles2.mspmp100.ptyp1, rmsfiles2.mspmp100.ptyp2, rmsfiles2.obcop200.quano, rmsfiles2.obcop200.quana, rmsfiles2.obcop200.quans, rmsfiles2.obcop200.c2rdt,rmsfiles2.mspmp100.prdno, rmsfiles2.obcop200.unitm, rmsfiles2.mspmp100.descp, rmsfiles2.obcop200.actsp, rmsfiles2.obcop200.ordno from rmsfiles2.obcop200 inner join rmsfiles2.mspmp100 on rmsfiles2.obcop200.prdno = rmsfiles2.mspmp100.prdno where rmsfiles.obcop200.ordno = " & Order.ToString()
Dim command As New Data.Odbc.OdbcCommand(mysql)
command.Connection = myConnection
Dim adapter As OdbcDataAdapter = New OdbcDataAdapter(command)
Dim ds As DataSet = New DataSet()
adapter.SelectCommand = New OdbcCommandselect rmsfiles2.obcop200.line#, rmsfiles2.mspmp100.ptyp1, rmsfiles2.mspmp100.ptyp2, rmsfiles2.obcop200.quano, rmsfiles2.obcop200.quana, rmsfiles2.obcop200.quans, rmsfiles2.obcop200.c2rdt,rmsfiles2.mspmp100.prdno, rmsfiles2.obcop200.unitm, rmsfiles2.mspmp100.descp, rmsfiles2.obcop200.actsp, rmsfiles2.obcop200.ordno from rmsfiles2.obcop200 inner join rmsfiles2.mspmp100 on rmsfiles2.obcop200.prdno = rmsfiles2.mspmp100.prdno where rmsfiles.obcop200.ordno = " & Order.ToString()
,myConnection)
adapter.Fill(ds, "rmsfiles2.OBCOP200, "MSPMP100")
gridview1.DataSource = ds
gridview1.DataBind()
[/code]While you can do it for selection and databinding purposes, I don't believe such a dataset will let you perform update/insert operations.|||I'm not going to update or insert. I'm just binding the two table together

Quote:

Originally Posted by davef

While you can do it for selection and databinding purposes, I don't believe such a dataset will let you perform update/insert operations.

sql