UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> A Way To Produce A View That Will Return A Value Even With Empty Table, Any Versions    
 
   
cocoflipper
post Jul 17 2019, 11:21 AM
Post#1



Posts: 1,175
Joined: 11-August 03
From: Denver - CO


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)

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
theDBguy
post Jul 17 2019, 11:24 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cocoflipper
post Jul 17 2019, 11:39 AM
Post#3



Posts: 1,175
Joined: 11-August 03
From: Denver - CO


Did already try something like:

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

Still returns nothing.

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2019, 11:50 AM
Post#4


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cocoflipper
post Jul 17 2019, 12:35 PM
Post#5



Posts: 1,175
Joined: 11-August 03
From: Denver - CO


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!
This post has been edited by cocoflipper: Jul 17 2019, 12:36 PM

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
theDBguy
post Jul 17 2019, 12:37 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Jul 17 2019, 01:01 PM
Post#7



Posts: 2,537
Joined: 4-February 07
From: USA, Florida, Delray Beach


Have you tried?
SQL
SELECT IIf(DCount("*","twkServiceTable")=0,0,Max([twkServiceTable].[serviceVal])) AS serviceVal
FROM twkServiceTable;
Go to the top of the page
 
cocoflipper
post Jul 17 2019, 01:24 PM
Post#8



Posts: 1,175
Joined: 11-August 03
From: Denver - CO


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

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
ADezii
post Jul 17 2019, 01:32 PM
Post#9



Posts: 2,537
Joined: 4-February 07
From: USA, Florida, Delray Beach


Sorry, little point that I missed! shrug.gif
Go to the top of the page
 
cocoflipper
post Jul 17 2019, 01:51 PM
Post#10



Posts: 1,175
Joined: 11-August 03
From: Denver - CO


No worries. Thanks for taking a look.

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
cocoflipper
post Jul 17 2019, 02:31 PM
Post#11



Posts: 1,175
Joined: 11-August 03
From: Denver - CO


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.

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
CaptainMilly
post Aug 14 2019, 08:37 AM
Post#12



Posts: 142
Joined: 13-August 13



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
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 09:09 AM
Post#13


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


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.
This post has been edited by GroverParkGeorge: Aug 14 2019, 09:10 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
CaptainMilly
post Aug 14 2019, 09:39 AM
Post#14



Posts: 142
Joined: 13-August 13




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.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 06:40 PM