Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ A Way To Produce A View That Will Return A Value Even With Empty Table

Posted by: cocoflipper Jul 17 2019, 11:21 AM

Hi all

I want to utilize a view that will return a value to a field, even if the table (a worktable in this case) is empty.

When the table has a record, I can return one of the fields, no problem. Let's say I use this SQL to return 1 field :

SELECT MAX(serviceVal) as serviceVal FROM dbo.twkServiceTable


When there are no records in the table, I get nothing. Understood.

Now I want to use this view as a derived table in another view, but I want to return a 0 for serviceVal if there is nothing returned. Possible? Or is there another way to get to this resulting output (serviceVal if there is a record, 0 if not)

Posted by: theDBguy Jul 17 2019, 11:24 AM

Hi. Probably using something like SELECT CASE...?

Posted by: cocoflipper Jul 17 2019, 11:39 AM

Did already try something like:

SELECT CASE WHEN serviceVal IS NULL THEN 1 ELSE serviceVal END AS serviceVal FROM dbo.twkServiceTable

Still returns nothing.

Posted by: GroverParkGeorge Jul 17 2019, 11:50 AM

I would try this (may not work) by creating a Union View that always returns one row by using constants for the field names.

SQL
SELECT MAX(serviceVal) as serviceVal FROM dbo.twkServiceTable UNION
SELECT 0 as serviceVal FROM dbo.twkServiceTable

Posted by: cocoflipper Jul 17 2019, 12:35 PM

OK - tried a few things I found on the web. Some which use "IF EXISTS" statements, seem to work in the view IDE, but then you can't save them or use the SQL statement as a derived tables in the view.

George, your solution was close, but as it turns out you can't use the "FROM dbo.twkServiceTable" for the second part of the UNION - it still returns nothing. You have to just do a"SELECT 0 AS serviceVal", without the "FROM table", as the second part to make sure that you get at least one entry.

Then there's the little part about returning only 1 record. I do that by nesting the Union query in another query that gets the Max. Here's the final SQL:

SELECT MAX(serviceVal) AS serviceVal
FROM (SELECT serviceVal FROM dbo.twkServiceTable
UNION ALL
SELECT 0 AS serviceVal) AS dervTable


So now ...
- if there is a value in the table for serviceVal, it gets returned.
- if there is no record in the worktable, at minimum there will be a return of one value --> 0
- I get one record based upon the Max function.
- if the serviceVal is equal to 0, this query returns 0 in either case. If the serviceVal has a value (which will be a whole number > 0), it will return only that value.

That will do the trick. Thanks for your input, George and DBguy!

Posted by: theDBguy Jul 17 2019, 12:37 PM

Hi. Congratulations. Glad to hear you got it sorted out.

Posted by: ADezii Jul 17 2019, 01:01 PM

Have you tried?

SQL
SELECT IIf(DCount("*","twkServiceTable")=0,0,Max([twkServiceTable].[serviceVal])) AS serviceVal
FROM twkServiceTable;

Posted by: cocoflipper Jul 17 2019, 01:24 PM

SQL server views do not allow for IIf, only CASE WHEN statements

Posted by: ADezii Jul 17 2019, 01:32 PM

Sorry, little point that I missed! shrug.gif

Posted by: cocoflipper Jul 17 2019, 01:51 PM

No worries. Thanks for taking a look.

Posted by: cocoflipper Jul 17 2019, 02:31 PM

And for those of you tuning in who are wondering how to do this with a text field instead of a number, here's the SQL that works:

SELECT Comments
FROM (SELECT DISTINCT TOP (1) Comments
FROM (SELECT Comments FROM dbo.twkServiceTable
UNION ALL
SELECT '' AS Comments) AS dervTable
ORDER BY Comments DESC) AS CommentsValue

The default value is '', and I sort the field so that it will go to the bottom if there is text entered as a record in the table. Then I just get the top value.

Posted by: CaptainMilly Aug 14 2019, 08:37 AM

QUOTE
SQL server views do not allow for IIf, only CASE WHEN statements


I've just used IIF() today in a view and it returned the data... am I missing something here? Does it appear to work and then breaks at some point or something?

Best
milly

Posted by: GroverParkGeorge Aug 14 2019, 09:09 AM

You're both "right". Newer versions of SQL Server now support IIf(). I am not sure which version it was first added, but certainly it wasn't available previously. I know it is in SS 2017 and SQL Azure.

Posted by: CaptainMilly Aug 14 2019, 09:39 AM


QUOTE
You're both "right". Newer versions of SQL Server now support IIf(). I am not sure which version it was first added, but certainly it wasn't available previously. I know it is in SS 2017 and SQL Azure


Oh, Ok! thank you for clarifying. I am using 2016.