|
Bob,
Thanks very much, you pointed me in the right direction. I did n't spot the SELECT TOP (100) percent , which was added by the View designer.
My final solution if anyone is interested :-
SELECT DISTINCT employee.intEmpID AS ID, employee.strEmpID AS CardReference,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname
END AS Visitor,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 Company FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As Company,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As ResponsiblePersonnelID,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
dbo.fnGetEmployeeName((SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC))
END As ResponsiblePersonnel,
CASE Employee.intStatus
WHEN 1 THEN
-1
ELSE
0
END AS ClockedIn,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 TimeIn FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As ArrivalTime
FROM Tensor.dbo.tblEmployee AS employee LEFT OUTER JOIN
dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID
WHERE (employee.strEmpID LIKE 'V%')
ORDER BY CardReference
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Pleased to have been of use.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Good morning,
I have a question regarding if something is possible to do in SQL. Is it possible to setup a Linked Server in SQL, or some other SQL connection that will access an existing Web Service? I have found various web pages about setting up SQL data to be accessable as a Web Service, but I found nothing that says if it's possible for SQL to access a Web Service to pull back records as part of a stored procedure.
The reason I'm wondering about this is because as we are moving to a new database engine, it has been suggested to turn off access to Linked Servers.
David
|
|
|
|
|
I my be wrong, but I think you will need to write a .NET extension for SQL Server, which can then subscribe to a Web Service
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
I have a need to return a table of data, for a MS SQL Server 2005 Stored Procedure, based on the following :-
ID = Table1.ID
If Table1.Status = 0 Then
Name = Table1.Name
Company = NULL
Else
Name = Table2.Name
Company = Table2.Company
Where Table2.ID = Table1.ID
End
I hope this mud is clear.
My T-SQL is not bad for the run-of-mill type tasks, but I do struggle with the more complex ones. (But I am learning)
Any pointers on where I should start would be great.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Hi,
the exact approach depends on whether you just want to invoke the procedure and check it's return value or if you need to retrieve other results from it as well (a procedure returns an integer with the RETURN statement, but can also return a data set from a SELECT statement or another proc). If you wish to retrieve a dataset returned by the procedure, the simplest approach is to use a data adapter and configure it's SelectCommand to call the procedure. If you only need the return value it's more efficient to use an SqlCommand directly without an adapter.
In any case, you configure the command as follows.
First, set command text and type to specify a proc and it's name:
cmd.CommandText = "myproc"; // name of stored procedure
cmd.CommandType = CommandType.StoredProcedure;
Next, create parameter objects:
SqlParameter name = cmd.Parameters.Add("@name");
If you need the return value from the procedure, add a parameter of type ReturnValue using another Add() overload.
Assign the parameter values according to your logic:
If ...
name.Value = Table1.Name
...
Else
name.Value = Table2.Name
...
Then execute: DataAdapter.Fill() if you're using the adapter, or SqlCommand.ExecuteNonQuery() if you only need the return value. Note that ExecuteNonQuery() returns number of rows affected (in the last batch of the procedure, see @@ROWCOUNT in Sql Server docs), not the return value from the procedure. To get this, read the Value property of the SqlParameter you added.
|
|
|
|
|
try this
select ID = Table1.ID
If Table1.Status = 0 Then
Name = Case when Table1.Status = 0 then Table1.Name else Table2.Name end,
Company = case when Table1.Status = 0 then NULL else Table2.Company end
from Table1
inner join Table2 on Table2.ID = Table1.ID
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob, with a bit of refinement I have what I need based on what you gave me.
Cheers
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
No problem
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi...i have created a system that allows users to select process start dates and end dates, then they can select specific days for the process to run eg(Monday and Thursday), is there any possible way for me to get the exact date they specified eg: start date: 2008/09/10 and end date: 2008/09/20, they select Monday as specified date..
now is there a way to select the start date to be the first monday of the start date... scheduled dates: 2008/09/15 and 2008/09/18.
Is there a wat to do this?
living life on the flip side
|
|
|
|
|
Of course...
SELECT DATEPART(dw, GETDATE()) AS 'Today'
This gives you the day of the week (as an index), so if the first day is Monday (this is culture-dependent) the function returns 5 today, since today is Friday.
So just test the start date and you'll know what day of the week that is. Based on that it's pretty easy to figure out how many days it is until any given day.
|
|
|
|
|
thnx for the reply...really works fine!!
living life on the flip side
|
|
|
|
|
Hi,
I have a table called Contacts.
The records in this table have associations with records in other tables.
Sometimes when I try and delete a row, I get an error saying that it can't because there are references (sorry not at work and can't remember the exact error).
I'm using MS SQL Studio, and am wondering how I can delete these rows correctly. Is there some property that lets add references (or links I guess) to a column or table? If so, where can I find it?
Cheers,
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
You can use truncate[^].
<br />
TRUNCATE TABLE tablename<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Ok sweet ill look into that.
Thank you!
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
No problem,you are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Not sure if that was intentionally evil but TRUNCATE will remove ALL the data from the table...
The reason you are getting the error is because SQL Server is enforcing referential integrity. It's easiest to explain in the context of a simple example, so imagine you have book and author tables, and for the sake of simplicity let's assume a book can have only one author, but an author can write many books. The way this would be implemented in a relational database is by defining a foreign key column in the Books table that point to an author, say AuthorID. Now if you delete an Author there may be Book records that refer to that author, and this is generally considered a bad thing. Depending on your scenario it might make sense to decide that if an author is deleted, so should all the books by that author be. This is called CASCADE DELETE and can be configured in SQL Server so your code can just delete one or more Author records, leaving it to SQL Server to also delete the related books. In other cases it might make more sense to UPDATE the Books table and perhaps set NULL in the AutorID column, or sometimes even to transfer the association so it refers to another entity (though in our example it makes no sense to change the author of books).
SQL server only enforces referential integrity for relations that are so configured. You CAN change the setup and it will allow you to leave data referring to non-existing data, but of course this is seldom a good idea. Usually it is best to define all your relations in the database and enforce integrity everywhere. This will help you spot errors much earlier in the development and prevent errors in your code from corrupting the data, but may of course cause blocking bugs if you do not perform adequate testing.
|
|
|
|
|
Hi,
If you are deleting row from a table which has primary key referenced to other table as foreign key,
Solution 1 - you will have to delete foreign key occurence of the data first and then delete primary key occurence or you'll get reference error which I think you are facing currently,
Solution 2 - uou can force referential integrity between relations this will delete all forieign occurences on deletion of primary key but this solution is not recommended.
Note: Truncate will delete all the record of your table and I'm not sure this will solve your problem anyways becuse truncate operation also will give referential error.
hope this solves your problem
Bi
|
|
|
|
|
razov wrote: Solution 1 - you will have to delete foreign key occurence of the data first and then delete primary key occurence or you'll get reference error which I think you are facing currently,
Yes, this is my problem.
Is there script or something I can execute to search for al these foreign keys (or are they listed somewhere)?
The problem is I do not know where they all are!
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
For foreign key dependencies either you can look in th DB documentation(if any) or create a DB Diagram.
Here is one query using the INFORMATION_SCHEMA views that returns both sides of all FOREIGN KEY relationships, as well as the name of the foreign key constraint.
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
ORDER BY
1,2,3,4
If you want to limit it to specific tables, you can add any of the following immediately prior to the optional ORDER BY clause:
WHERE PK.TABLE_NAME='something'
WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')
Hope this solves your problem.
|
|
|
|
|
Thankyou very much!
I'll play with this tommorrow at work.
Cheers,
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
|
thank god sql not allow you delete the records!
if it let´s you do that how will you handle it? you are not supose to do that!
it won´t let you delete because it´s using those record in other table.
do this:
if not exists(select * from other_table where id_other_table = @id_contact) begin
delete from contact where id_contact = @id_contact
select 'OK: deleted'
end
else
begin
select 'ERROR:inform that you can delete that row because you use it in other_table'
end
that way you know sure where in which table the record is used!
go to that table and delete then afterwords if you want
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
should care the order in every group
for example,
table:
id data num
1 2008-1-1 2
2 2008-2-2 2
3 2008-1-1 1
4 2008-2-2 4
5 2008-1-1 5
the result will be:
data first last
2008-1-1 2 5
2008-2-2 2 4
Here in every group take first/last in the order of id asc.
system
|
|
|
|
|
select distinct data,<br />
(select top 1 (t1.num) from myTable as t1 where t1.data = myTable.data order by t1.id ) as firstRow,<br />
(select top 1 (t2.num) from myTable as t2 where t2.data = myTable.data order by t2.id desc) as LastRow<br />
from myTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|