January 28, 2011 at 10:16 am
I'm running a query that may or may not return matching data and if there are no rows that meet the query criteria, I want to return a default response.
For example:
SELECT ISNULL(Name, 'NO STUDENT EXISTS')
FROM Students
WHERE ID=100
If there is no ID 100 in the table, then I want it to return 'NO STUDENT EXISTS'. I thought that this was a job for ISNULL, right? However, I keep getting and empty set rather than 'NO STUDENT EXISTS'
I can replicate the behavior here:
IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP TABLE #Table
SELECT 1 AS StudentID,
'John Doe' AS Student
INTO #Table
--Returns 'John Doe'
SELECT ISNULL(Student, 'NO STUDENT EXISTS')
FROM #Table
WHERE StudentID = 1
--Returns nothing
SELECT ISNULL(Student, 'NO STUDENT EXISTS')
FROM #Table
WHERE StudentID = 2
--Returns 'No Student Exists'
IF ( SELECT Student
FROM #Table
WHERE StudentID = 2) IS NULL
BEGIN
SELECT 'NO STUDENT EXISTS'
END
DROP TABLE #Table
Why does the IF...THEN return 'No student exists', but ISNULL returns nothing? I'm sure I'm missing something very simple here, but this behavior confuses me.
January 28, 2011 at 10:50 am
IsNull will return a secondary value if a value is null. No row = no column value = IsNull doesn't handle that.
Usually, handling of empty datasets is something that's done in the application, not in the database. If you have to do it in the database, there are a number of methods for doing so, including using a return value, using a Union statement with an inverted Where clause (so if the query above the Union has rows, the one below won't, and vice versa), or branching with an IF statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2011 at 10:06 am
IsNull function only replaces data where the value is null, but it does not handle when there is no data.
For that you need something like this:
IF NOT EXISTS ( SELECT NAME
FROM Students
WHERE ID = 100 )
BEGIN
SELECT 'NO STUDENT EXISTS' AS Name
END
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 11, 2011 at 1:15 pm
Or
DECLARE @result VARCHAR(50)
SET @result = (SELECT TOP 1 Student FROM #Table WHERE StudentID = 1)
SELECT ISNULL( @result, 'NO STUDENT EXISTS')
It's important to use SET operator (not SELECT), so that @result variable is always set to NULL value if student does not exist, or to Student name value if exists.
August 10, 2011 at 2:11 pm
How would you display the value of that variable @result, for example in ASP? PLEASE HELP
would it be.....
Response.Write("<td class=fieldFormat valign=top nowrap> "&rs("@result")&"</td>")
August 10, 2011 at 3:01 pm
tomperson349 (8/10/2011)
How would you display the value of that variable @result, for example in ASP? PLEASE HELPwould it be.....
Response.Write("<td class=fieldFormat valign=top nowrap> "&rs("@result")&"</td>")
You can't write the value of a variable in sql in a front end application. You CAN select that value and your app can access it.
DECLARE @result VARCHAR(50)
SET @result = (SELECT TOP 1 Student FROM #Table WHERE StudentID = 1)
SELECT ISNULL( @result, 'NO STUDENT EXISTS') as MyResult
Then your asp would be something like
Response.Write "<td class=fieldFormat valign=top nowrap>" & rs("MyResult") & "<td>"
btw, you really should be using style for your visual elements instead of the older and less precise display items. If I were writing that HTML it would be like this instead:
Response.Write "<td class='fieldFormat mySecondCSS' style='white-space:nowrap; vertical-align:top;'>" & rs("MyResult") & "<td>"
Yeah I know it is picky but style lets YOU decide exactly how to display your elements instead of leaving it up to the browser interpretation. Notice I also wrapped things like class in quotes. It makes no difference if they are single or double as long as they are the same. If you want to inherit more than one css class you have to use quotes. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2011 at 3:03 pm
If you are using an SQLDatareader Class to read the data, you need must pass either the name of the column or the column order index.
DECLARE @result VARCHAR(50)
SET @result = ( SELECT TOP 1
Student
FROM #Table
WHERE StudentID = 1
)
SELECT ISNULL(@result, 'NO STUDENT EXISTS') AS ResultColumn
'Specify the name of the column
Response.Write("<td class=fieldFormat valign=top nowrap> " & rs("ResultColumn") & "</td>")
'column index number..this is the order of the column
Response.Write("<td class=fieldFormat valign=top nowrap> " & rs(0) & "</td>")
if you need further assistance, please place a copy of your .net code so we can see what components you are trying to use for data retrieval.
You can also contact me by going to HERE or emailing me HERE
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
August 10, 2011 at 3:09 pm
Ysaias Portes-QUI Group (8/10/2011)
If you are using an SQLDatareader Class to read the data, you need must pass either the name of the column or the column order index.
DECLARE @result VARCHAR(50)SET @result = ( SELECT TOP 1
Student
FROM #Table
WHERE StudentID = 1
)
SELECT ISNULL(@result, 'NO STUDENT EXISTS') AS ResultColumn
'Specify the name of the column
Response.Write("<td class=fieldFormat valign=top nowrap> " & rs("ResultColumn") & "</td>")
'column index number..this is the order of the column
Response.Write("<td class=fieldFormat valign=top nowrap> " & rs(0) & "</td>")
if you need further assistance, please place a copy of your .net code so we can see what components you are trying to use for data retrieval.
You can also contact me by going to HERE or emailing me HERE
Pretty sure this is classic ASP and not .NET.
Just my 2¢ but, I would strongly urge you to never ever ever ever use ordinal position when referring to a result set. It is nothing but a headache. It makes coding harder for the next guy because they have to map out which column is what. And is a total mess when the underlying select statement changes. Let's say your eager developer changes the ordinal position to the second column in the stored procedure by adding a new column in the select and your app blindly says give me column 1...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2011 at 11:42 am
Ok guys I have another question for you. I am using this query:
sql = " select b.User_Name "
sql = sql & " from Investment_Advisor b "
sql = sql & " where "
sql = sql & " ("
sql = sql & " b.Year_id = '2010' )"
set rs = server.CreateObject("adodb.recordset")
rs.open sql, conn
while not rs.EOF
// HERE I WANT TO QUERY AGAIN, DO I NEED TO MAKE ANOTHER CONNECTION ???
// HERE IS MY NEXT QUERY I WANT TO USE:
sql = "DECLARE @result VARCHAR(50)"
sql = sql & "SET @result = (select b.User_Name = 'Abraham,Todd A' FROM Investment_Advisor b)"
sqla = sql & "SELECT ISNULL( @result, 'Not Available') as theRESULT1"
Response.Write("<td class=fielddHeader valign=top nowrap><b> "&rsa("theRESULT1")&"</b></td>")
// I am writing to an excel spread sheet, fyi . I am getting errors when trying to print out theRESULT1.
Can anyone help ?
August 12, 2011 at 11:44 am
My purpose here was I wanted to compare rs("user_name") to rs("theResult")
August 12, 2011 at 11:56 am
Another example would be to use a case statement to define the column.
--Declare Table
DECLARE @table TABLE (StudentIDINT
,StudentNamevarchar(50));
--Insert Data
INSERT INTO @table(StudentID,StudentName) VALUES (1,'John Doe');
INSERT INTO @table(StudentID,StudentName) VALUES (2,'');
INSERT INTO @table(StudentID,StudentName) VALUES (3,NULL);
--Show Table Data
SELECT * FROM @table;
--Example
SELECT StudentID
,CASE
WHEN StudentName IS NOT NULL AND RTRIM(LTRIM(StudentName)) <> '' THEN
StudentName
ELSE
'NO STUDENT EXISTS'
END as StudentName
FROM @table;
August 12, 2011 at 11:58 am
Instead of ADODB, you should try to use OLEDB Provider which supports a new feature if your database is SQL 2005 or above called "MARS", Multiple Active Resultsets.
With MARS you could retrieve the results to the client with one single connection and the compare the values after they been loaded to the client.
The way you are doing it will make a connection every time you loop around your FIRST recordset.
The best way for you to do it is write a stored procedure on SQL that compares the values as one statement or ultimately but not recommended because of the performance cost, you could write a CURSOR in SQL "This should be your last choice. It is expensive to use in SQL".
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
August 12, 2011 at 12:00 pm
tomperson349 (8/12/2011)
Ok guys I have another question for you. I am using this query:sql = " select b.User_Name "
sql = sql & " from Investment_Advisor b "
sql = sql & " where "
sql = sql & " ("
sql = sql & " b.Year_id = '2010' )"
set rs = server.CreateObject("adodb.recordset")
rs.open sql, conn
while not rs.EOF
// HERE I WANT TO QUERY AGAIN, DO I NEED TO MAKE ANOTHER CONNECTION ???
// HERE IS MY NEXT QUERY I WANT TO USE:
sql = "DECLARE @result VARCHAR(50)"
sql = sql & "SET @result = (select b.User_Name = 'Abraham,Todd A' FROM Investment_Advisor b)"
sqla = sql & "SELECT ISNULL( @result, 'Not Available') as theRESULT1"
Response.Write("<td class=fielddHeader valign=top nowrap><b> "&rsa("theRESULT1")&"</b></td>")
// I am writing to an excel spread sheet, fyi . I am getting errors when trying to print out theRESULT1.
Can anyone help ?
What is all that stuff in the middle?? Your query will not even run by itself.
select b.User_Name = 'Abraham,Todd A' FROM Investment_Advisor b
Even if you have it working it doesn't make sense inside your loop. Are you trying to look at the User_Name for each record in your first query? Perhaps you could change your original query to something like this:
select isnull(b.User_Name, 'Not Available') as UserName from Investment_Advisor where Year_id = '2010'
Maybe if you explain more clearly what you are trying to do we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2011 at 12:46 pm
ok. here is everything i have. keep in mind again I am tryin to export these results into an excel file. The first query runs fine all the user names get exported to excel file. the problem is when i put in the second query. i am getting errors.
sql = " select b.User_Name "
sql = sql & " from Investment_Advisor b "
sql = sql & " where "
sql = sql & " ("
sql = sql & " b.Year_id = '2010' )"
sql = sql & "order by user_name ASC"
set rs = server.CreateObject("adodb.recordset")
rs.open sql, conn
while not rs.EOF
dim name
name=rs("User_Name")
sql = "DECLARE @result VARCHAR(12)"
sql = sql & "SET @result = (select P2_Date_Acknowledged FROM Investment_Advisor_Part2 WHERE P2_Year_ID = '2010' and P2_userid = '" & name & "')"
sql = sql & "SELECT ISNULL( @result, 'N/A') as theRESULT1"
missingis = "Annual Disclosure Reporting"
Response.Write("<tr>")
Response.Write("<td class=fielddHeader valign=top nowrap><b> "&rs("user_name")&"</b></td>")
Response.Write("</tr>")
rs.movenext
wend
end if
August 12, 2011 at 12:48 pm
i realize
Response.Write("<td class=fielddHeader valign=top nowrap><b> "&rs("user_name")&"</b></td>")
should be
Response.Write("<td class=fielddHeader valign=top nowrap><b> "&rs("theResult1")&"</b></td>")
....even with this change still doesn't work
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply