|
You could always do it like this in a stored procedure:
IF EXISTS(SELECT * FROM Test
WHERE [User] = @User AND [Out] IS NULL)
BEGIN
UPDATE Test
SET [Out] = @TimeValue
WHERE [User] = @User
AND [Out] IS NULL
END
ELSE
BEGIN
INSERT INTO Test ([Date], [User], [In])
VALUES (GETDATE(), @User, @TimeValue)
END
Swapping in your parameters obviously.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thank you very much,its working now.
keep on the good srpirit.
|
|
|
|
|
try this
if ur field is x in table xyz which u have to check its null or blank
select x from xyz where isnull(x,'')<>''
this query check both null or blank in ur x colum give only filled records
Rakesh Jha
|
|
|
|
|
Hi
I m trying to count minimun no of friends that customer has set up in the table customer_fiends.
select min(count(friendname))
from customer_friends
where friendname<>'Me' group by userid
The following error in orruring!
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
SHAHZAD ASLAM
|
|
|
|
|
what are the columns in yur table?
Gautham
|
|
|
|
|
userid,friendname,mobileno
the userid is the reference in Customers Table i want to select minimum no of friends that customer has set up.
SHAHZAD ASLAM
|
|
|
|
|
if i have 7 friends what would be the minimum of friends list, no clue what are your true intentions are????
Gautham
|
|
|
|
|
if userid 1 hve 7 friends and userid 2 hve 5 and userid 3 hve 2 friends then it should display userid 3 have 2 friends that is the minimum no of friends of that customer.
SHAHZAD ASLAM
|
|
|
|
|
OK, I did say that I didn't think it would work still. I answered in a hurry. Colin is right, you can't run min on count, count is always a number. To get the lowqest value, you want to do something like
select top 1 count(friends) as friendCount, userId from tbl group by userId order by friendCount asc
something like that, it's almost certainly not right 100%. But, you get the userId and the count of friends, you order it so the first record is the lowest, then you grab that one record.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
Thanks its working fine.
SHAHZAD ASLAM
|
|
|
|
|
you can only group by things you select
select min(count(friendname)), userid
from customer_friends
where friendname<>'Me' group by userid
may work. I'm not sure this will work overall, I don't have SQL Server here to test it.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
it Still not working the same error occuring.
|
|
|
|
|
Shahzad.Aslam wrote: The following error in orruring!
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
This:
select min(count(friendname)) is the source of the error.
You have nested two aggregate functions inside each other. COUNT() has a single result, so the MIN() of a COUNT() is always the same as the result of COUNT(). . Essentially, there is nothing to aggregate.
I really cannot see what your intent was with this statement, so I cannot help you correct the problem. All I can suggest is to remove the MIN() and just leave the COUNT() .
If you can explain your intentions then it might be easier to help.
|
|
|
|
|
hi
I think its not possible to find the minimum or maximum value for an aggregate function like 'count' because that will return a single value only..like if there are 12 members whose name <> 'ME', then the output will be 12.
From this 12, how can you find the minimum value? Thats why the error has occured.
|
|
|
|
|
Hai friends,
In my project i want to get the data from 2 databases.? i don't have any idea about this i tried but still i didn't get any answer...please help me to find a solution...i am waiting for your kind reply....
Thanks in advance...
Alex.
|
|
|
|
|
select........... dbo.tablename, 2nddatabsename.dbo.tablename.......
Gautham
|
|
|
|
|
Hello,
I have a typed dataset which I want to display in an ultragrid (infragistics) and display the parent and the children.
I can display this ok when I want to display all of the parents and children. But the problem is when user will need to select one parent (by customerID number) from a list and display that parent with its children in the grid.
I have created the dataset schema dsAssembliedEquipment.xsd and my 2 tables assembly and AssembiledEquipment. And also created the relationship between them.
I have a stored procedure (getDataByCustomerID) that will find the customerID of the assembly (parent) table.
This is my code for setting the grid.
<br />
'Table Adapter to get the customer ID record<br />
Me.TA_Assembly1.GetDataCustomerID(customerID)<br />
'Fill the assembly (parent) table and assembliedEquipment table(child)<br />
Me.TA_Assembly1.Fill(Me.DsAssemblyEquipment1.Assembly) Me.TA_AssembliedEquipment1.Fill(Me.DsAssemblyEquipment1.AssembliedEquipment)<br />
Me.grdCustomersAssembly.DataMember = "Assembly"<br />
Me.grdCustomersAssembly.DataSource = Me.DsAssemblyEquipment1 <br />
Code for the stored procedure getDataCustomerID:
<br />
ALTER PROCEDURE [dbo].[Assembly_SelByCustomerID]<br />
(<br />
@CustomerID int<br />
)<br />
AS<br />
SET NOCOUNT ON;<br />
SELECT AssemblyID, CustomerID, AssemblyName<br />
FROM dbo.[Assembly]<br />
WHERE CustomerID = @CustomerID <br />
I have Assembly (parent) Table fields AssemblyID / customerID / AssemblyName
I have child table called AssemblyEquipment (child) fields SerialNo / make / Model / AssemblyID (Foreign Key)
Many thanks for any help with this problem,
Steve
|
|
|
|
|
If you haven't already solved your problem I think it would be helpful to clarify what part you are having a problem with. Are you trying to change the stored procedure or add a customer row to the datagrid after you bind the data?
|
|
|
|
|
Hi Guru's
I created a stored procedure which on a straight query it will appear like this
select BookTitle, Author, Status from LibBooks where Author like '%Buck%'
On my SP I created two @parameters to have flexibility on my query, @SearchCategory - column name (Author)
@SearchField - text to be searched (Buck)
Problem:
I noticed that my query does'nt return result, its it possible to use @parameter as substitute to column name on a condition?
Something like this:
select BookTitle, Author, Status from LibBooks where @SearchCategory like '%' + @SearchField + '%'
SP that I created
Create procedure [dbo].[sp_searchFromCat]
(
@SearchCategory varchar(100) = null,
@SearchField varchar (100) = null
)
as
begin
select
BookTitle,
Author,
Status
from
LibBooks
where
@SearchCategory like '%' + @SearchField + '%'
end
|
|
|
|
|
The easiest way to make the column in the where clause dynamic is to build the query in the procedure then execute the sql code. example from article:
Article on dynamic sql [^]
use Northwind
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' +
' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
N'@RECORDCNT int out, @ORDERID int',
@RECCNT out,
@ORDID
print 'The number of records that have an OrderId' +
' greater than ' + @ORDID + ' is ' +
cast(@RECCNT as char(5))
Another idea is if you only have a couple possible columns you could do something like this
Select *
From table
where column1 = isnull(@Column1Value, column1)
and column2 = isnull(@Column2Value, column2)
This might not perform well on a large table
|
|
|
|
|
Hi,
I guess I'll go to the second suggestion you gave, my brain got tangled after reading the first one. checking on this
Thanks
|
|
|
|
|
Based on what condition your query should be executed? where....??
Gautham
|
|
|
|
|
Hi Gautham,
Just got the query working, thanks!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_searchFromCat]
(
@SearchCategory varchar(100) = null,
@SearchField varchar (100) = null
)
as
begin
declare @SQL varchar(4000)
set @SQL = 'select BookTitle, Author, Status from LibBooks ' +
case when @SearchCategory is null then ''
else 'where ' + @SearchCategory + ' like ' + '''%' + isnull(@SearchField,'') + '%'''
end
exec(@SQL)
end
Dom
|
|
|
|
|
I am running the following query:
select sum(total) as 'Total QA Hours For F.I Centralized Alert Int', task.name as 'Project' from trans
inner join task on task.name=trans.task where trans.project = '374'
and [date] >= '1/31/2007'
group by task.name
The following error is returned.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
Trans.task = bigint
Task.Name = Varchar (250)
trans.Total = float
Prsumably I need to use cast?
I have not been able to figure this one out.
Any help would be greatly appreciated.
Regards,
Hulicat
|
|
|
|
|
select
sum(total) as 'Total QA Hours For F.I Centralized Alert Int'
, task.name as 'Project'
from trans
inner join task on task.name=Cast(trans.task as varchar(250))
where trans.project = '374'
and [date] >= '1/31/2007'
group by task.name
|
|
|
|