WHERE CLAUSE

skhan · August 27th, 2009, 02:22 PM

I have the following SQL Query:
"SELECT re_MLNO, MAX(re_SpecimenDate)-MIN(re_SpecimenDate) AS DateDiff FROM ml_hiv_status WHERE (re_HIV1_Status=0) GROUP BY re_MLNO";

I need to impose a condition on DateDiff so that the query only selects the rows where DateDiff>2555

If I write the following then it gets exception:
"SELECT re_MLNO, MAX(re_SpecimenDate)-MIN(re_SpecimenDate) AS DateDiff FROM ml_hiv_status WHERE (re_HIV1_Status=0 AND DateDiff>=2555) GROUP BY re_MLNO";

Please help!!!! Thank you in advance.

Old Pedant · August 27th, 2009, 04:35 PM

You can't use an aliased SELECT name in the WHERE clause (or in the GROUP BY clause...you can only use it in ORDER BY).

So you have to repeat the code:

Code:

SQL = "SELECT re_MLNO, MAX(re_SpecimenDate)-MIN(re_SpecimenDate) AS DateDiff " 
    + " FROM ml_hiv_status "
    + " WHERE re_HIV1_Status=0 "
    + " AND MAX(re_SpecimenDate)-MIN(re_SpecimenDate)  >= 2555 "
    + " GROUP BY re_MLNO";

By the way DateDiff is a builtin function name in SQL Server and Access, so you should avoid it as a field name.

Oh, and don't worry about the performance of repeating the expression. The query engine is smart enough to see the duplication and reuse the value it has previously computed.

If you are curious: The reason you can't use the aliased SELECT name is because the WHERE clause is executed *FIRST*, long before the SELECT creates the field values. So, actually, it is the SELECT that is using the duplicated expression.

skhan · August 27th, 2009, 05:19 PM

this still throws exception! :( I am so frustrated with it now......can you suggest one group where I can find how to manipulate each cell from a datagrid? At least I can try hiding/deleting the data from the datagrid which are >=2555!

Old Pedant · August 27th, 2009, 07:45 PM

*WHAT* exception does it throw??? Show us the full error message.

You don't say what DB you are using. It's possible that the DB does not allow simple subtraction of dates like that. You might need to use a function to subtract dates.

Are you expecting that subtracting the dates will give you the number of *DAYS*?? 2555 days is a long time--almost 7 full years. Makes me wonder a bit.

skhan · August 27th, 2009, 08:20 PM

Server Error in '/hiv' Application.

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 235: catch(Exception ex)
Line 236: {
Line 237: message.InnerHtml = "SQL: " + sql + "<p></p>" + ex.ToString();
Line 238: }
Line 239: }
Source File: c:\inetpub\wwwroot\hiv\run_specific_query.aspx.cs Line: 237

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
HIV.run_specific_query.executeResistantListQuery_A NY() in c:\inetpub\wwwroot\hiv\run_specific_query.aspx.cs: 237
HIV.run_specific_query.submitButton_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\hiv\run_specific_query.aspx.cs: 121
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData)
System.Web.UI.Page.ProcessRequestMain()

Version Information: Microsoft .NET Framework Version:1.1.4322.2407; ASP.NET Version:1.1.4322.2407

It gives the above error......

I am using MS Access database, and I am guessing that it doesn't take the date format I am using to compare dates. I am comparing the date with the date column in the DB which has medium date format.

Yes I am expecting that subtracting the dates will give me the number of *DAYS*, cause I can see from the datagrid that it's giving me the correct number of days if I don't put the >=2555 condition in. 2555 days is a long time--almost 7 full years which I need to calculate because I need to know which patients are hiv resistant, and that's the criteria. If one person is hiv negative for 7 years then he/she is hiv resistant....... that's why io need to calculate that.

Please help if you can....my demo is tomorrow morning,, :(

Old Pedant · August 27th, 2009, 10:09 PM

AHH! That error has NOTHING WHATSOEVER to do with the SQL.

That error is telling you there is no such thing as a message object. And I'd say it's almost certainly correct. Because no place in ASP.NET, that I am aware of, can you use the innerHTML property. That's a BROWSER-ONLY property, only valid in JavaScript in the browser.

NOW...use the debugger or use a MSGBOX to display the error. Don't try to mix server-side and client-side code!!

NOTE: The SQL code you show should be perfectly valid for Access. Did you *try* the query in Access? Not using ASP.NET. Just in Access itself??

OH WAIT! I may have given you the answer already! DATEDIFF is a *bad* name to use. It conflicts with the builtin function of that name. You may need to put [...] around that name if you don't want to change it.

Code:

SQL = "SELECT re_MLNO, MAX(re_SpecimenDate)-MIN(re_SpecimenDate) AS [DateDiff] " 
    + " FROM ml_hiv_status "
    + " WHERE re_HIV1_Status=0 "
    + " AND MAX(re_SpecimenDate)-MIN(re_SpecimenDate)  >= 2555 "
    + " GROUP BY re_MLNO";

skhan · August 27th, 2009, 10:30 PM

Thank you for your clarification. It helped a lot. Now how do I use the debugger or use a MSGBOX to display the error?........inside the catch block? Can you please elaborate?....i am new in coding in C#.

skhan · August 27th, 2009, 10:38 PM

Got the stacktrace:
Exception: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbComm and.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at HIV.run_specific_query.executeResistantListQuery_A NY() in c:\inetpub\wwwroot\hiv\run_specific_query.aspx.cs: line 196