|
lhayes00 wrote: /** THIS DOES NOT WORK **/
IF(MinuteDiff <= 10) RETURN 1;
END
Because MinuteDiff does not exist at this point in the code.
Okay - Let's look at the IF statement
lhayes00 wrote: IF EXISTS(SELECT TOP 1 DATEDIFF(MINUTE, tableNews.CreateDate, GETDATE()) AS MinuteDiff FROM tableNews WHERE tableNews.Subject=@Subject)
IF EXISTS(SELECT) does a check to discover if the SELECT statement will return any rows. It doesn't care what those rows are, or store the results anywhere. It is a simple: Is anything at all returned.
Because of that, any calculations performed in the SELECT clause (in the place you'd normally return column data) is ignored. Putting a TOP 1 is pointless because the operation will short circuit the moment any row is found. Putting a DATEDIFF function in is pointless because it will ignore the results.
What you might want to do instead is something like this:
DECLARE @tenMinutesAgo DATETIME
SET @tenMinutesAgo = DATEADD(minute, -10, GETDATE())
IF EXISTS (SELECT * FROM tableNews WHERE CreateDate >= @tenMinutesAgo AND Subject=@Subject)
BEGIN
RETURN 1;
END
RETURN 0;
|
|
|
|
|
Thankyou very much for your reply 
I have just tried running that in my project and it works perfectly!!
And in addition is somewhat more optimal which is great.
|
|
|
|
|
You're welcome. I'm glad it worked.
|
|
|
|
|
When to use an inner join and when to use connect by prior to clause .
|
|
|
|
|
zizu9096@gmail.com wrote: When to use an inner join and when to use connect by prior to clause .
I don't recognise a "connect by prior to" clause. I couldn't find it in the documentation. Could you please cite the source of this phrase?
|
|
|
|
|
If you are referring to the CONNECT BY clause available within Oracle, that and self-joins are two entirely different things. The CONNECT BY allows recursion in the case that a table contains parent-child relationships. Using the CONNECT BY clause enables one to select ALL the children/grand-children/etc given a root entry. You can also use it to locate the root given a (perhaps distant) node. Joins on the other hand are not recursive. As nearly as I've ever been able to use them, you're limited to two generations of parent-child (unless you bury layers of sub-queries or some such thing).
|
|
|
|
|
I wanted to know how can we call Oracle stored procedures from Microsoft Reporting Services. I could pass a sql text and get a record set, but I want to execute a store proc and get the record set, and kindly let me know how to pass a refcursor to get the record set into the reporting services.
As always.. quick response would be greatly appreciated.
Thanks
|
|
|
|
|
anyone can help?
'/aspx' 應用程式中發生伺服器錯誤。
-----------------------------------------------------------------------------
DataBinding: 'System.Data.DataRowView' 沒有包含名為 '書名' 的屬性。
描述: 在執行目前 Web 要求的過程中發生未處理的例外情形。請檢閱堆疊追蹤以取得錯誤的詳細資訊，以及在程式碼中產生的位置。
例外詳細資訊: System.Web.HttpException: DataBinding: 'System.Data.DataRowView' 沒有包含名為 '書名' 的屬性。
原始程式錯誤:
只有在偵錯模式編譯時，才可以顯示產生此未處理例外狀況的原始程式碼。若要啟動，請依照下列步驟之一，然後要求 URL:
1. 將 "Debug=true" 指示詞加入產生錯誤的程式碼頂端。例如:
<%@ Page Language="C#" Debug="true" %>
或:
2. 將下列區段加入您應用程式的組態檔:
請注意: 第二種技巧會導致在偵錯模式下編譯指定應用程式中的所有檔案。第一種技巧只會造成在偵錯模式下編譯該特定檔案。
重要資訊: 在偵錯模式下執行應用程式會過度耗用記憶體/效能。在實際執行部署之前，應該先確認應用程式已經停止偵錯。
堆疊追蹤:
[HttpException (0x80004005): DataBinding: 'System.Data.DataRowView' 沒有包含名為 '書名' 的屬性。]
Microsoft.VisualBasic.CompilerServices.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags) +272
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure) +196
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn) +216
ASP.bookstore_default_aspx.Data_Bind(DataView DView, Object BindObject) +91
ASP.bookstore_default_aspx.Cart_Add(String Title, String Price) +823
ASP.bookstore_default_aspx.AddBookToCart(Object sender, DataGridCommandEventArgs e) +247
System.Web.UI.WebControls.DataGrid.OnItemCommand(DataGridCommandEventArgs e) +105
System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +77
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +117
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
--------------------------------------------------------------------------------
版本資訊: Microsoft .NET Framework 版本:2.0.50727.42; ASP.NET 版本:2.0.50727.210
-- modified at 22:50 Friday 30th March, 2007
|
|
|
|
|
There is a table name "ResultData" which following data :
Rollno Result SchoolCode
1 First 1
2 First 1
3 Second 2
4 First 1
5 Fail 2
6 Third 2
7 Second 1
8 Fail 3
9 Fail 1
10 Second 2
I want the output like this :
(School code wise count of Result)
SchoolCode First Second Third Fail Total
1 3 1 0 1 5
2 0 2 1 1 4
3 0 0 0 1 1
Please write a single select query to get output.
Thanks in advance
Girish Kumar Sharma
|
|
|
|
|
I believe you want a "crosstab query". Apart from Microsoft products, I don't know if other vendors have it built-in. I don't believe it's part of the SQL standard...
Here is what Microsoft Access help says about cross tab queries.
"
Syntax
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
The TRANSFORM statement has these parts:
Part Description
aggfunction An SQL aggregate function that operates on the selected data.
selectstatement A SELECT statement.
pivotfield The field or expression you want to use to create column headings in the query's result set.
value1, value2 Fixed values used to create column headings.
"
|
|
|
|
|
So, is it not possible to write a select statement in SQL Sever ?
|
|
|
|
|
Well, I thought it was included in SQL Server... Looks like didn't show-up until 2005.
Here is one article that may help. http://builder.com.com/5100-6388_14-6143761.html[^]
Once upon a time I wrote a lot of Perl code to dynamically do the same thing that the crosstab query does, but when I discovered crosstabs, I never went back. I don't know how to do it using CASE statements.
sorry I can't help more. Maybe someone else can...
|
|
|
|
|
You're correct, PIVOTs don't show up until 2005 in MSSQL
“Some have an idea that the reason we in this country discard things so readily is because we have so much. The facts are exactly opposite - the reason we have so much is simply because we discard things so readily. We replace the old in return for something that will serve us better.”--Alfred P. Sloan
|
|
|
|
|
|
Thanks to all of you and finally i used this query :
SELECT SCHCODE,COUNT(CASE WHEN RESULT = 'First' THEN Result END) AS First,COUNT(CASE WHEN Result = 'Second' THEN Result END) AS Second,COUNT(CASE WHEN Result = 'Third' THEN Result END) AS Third,COUNT(CASE WHEN Result = 'Fail' THEN Result END) AS Fail,count(*) as Total FROM resultdata GROUP BY schcode
|
|
|
|
|
I'm not much of an SQL query expert,
But I think that I have a solution
SELECT<br />
SchoolCode,<br />
COUNT(First) As TotalFirst,<br />
COUNT(Second) As TotalSecond,<br />
COUNT(Third) As TotalThird,<br />
COUNT(Fail) As TotalFail,<br />
(COUNT(First) + COUNT(Second) + COUNT(Third) +COUNT(Fail)) As TotalTotal<br />
FROM ResultData<br />
GROUP BY (SchoolCode)<br />
<br />
I havent tested it.
Post a reply to let every body know if it works.
|
|
|
|
|
hello all. i was wondering if anyone could recommend a good SQL IDE. i currently use SQL Server 2000 and SQL Server 2005. one of my main requirements is CVS. in one of my projects, i need to replicate one database's stored procedures to numerous others, so i need to see what procedures have changed everytime i need to work on the project. if anyone has any recommendations for other solutions, those are welcome to.
thanks for any help anyone provides.
|
|
|
|
|
I use both the SQL Server Management Studio Express and Quest Software's Toad for SQL 2.0 Freeware. Both work well for me, you may want to look at them if you haven't already
|
|
|
|
|
I would recommend you to use SQL Server Mangement Studio itself, but try to set up CVSNT ( Concurrent Versioning System - its free - Open source) and the great thing is you can integrate SQL Mangement Studio with CVS. You can use wincvs as a client to access the CVS Server. Setting up the CVS server and client are a breeze if you know what you are doing... There are plenty of resources on the net too.
So you can maintain source control easily with no cost. Mail me if you need further instructions on integrating SSMS with CVS.
|
|
|
|
|
Where is (LOCAL) determined on my machine? I have a local instance of SQL Server and am trying to get to it with that name but it just goes to my machine name instead of <machinename>\<sqlservername>.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: Where is (LOCAL) determined on my machine? I have a local instance of SQL Server and am trying to get to it with that name but it just goes to my machine name instead of \.
It is the default instance of SQL Server on the local machine.
|
|
|
|
|
Where can I change that? My computer thinks I have SQL Express 2005 and when I installed SQL Server 2000 it obviously didnt get updated.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: Where can I change that?
It is set when SQL Server is installed.
CleaKO wrote: My computer thinks I have SQL Express 2005 and when I installed SQL Server 2000 it obviously didnt get updated
I don't understand that statement.
SQL Server 2005 express edition usually installed itself as a named (not default) instance. The name is, I think, SQLEXPRESS.
The full server editions will attempt to install themselves as the default SQL Server unless it is already taken. (There can only be one default, but upto 16 named instances)
|
|
|
|
|
Colin Angus Mackay wrote: The full server editions will attempt to install themselves as the default SQL Server unless it is already taken. (There can only be one default, but upto 16 named instances)
This isnt a show stopper but Im just curious if after it is installed whether it can be changed by me. There is some kind of entry in the machine.config that has a .\SQLEXPRESS in it but I cant think of anywhere else that I would change something.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: Im just curious if after it is installed whether it can be changed by me
Not that I am aware of.
CleaKO wrote: There is some kind of entry in the machine.config that has a .\SQLEXPRESS in it but I cant think of anywhere else that I would change something.
That is most likely to permit SQL Server 2005 to use CLR Stored Procedures
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
