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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> 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,184
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
 
 
Start new topic
Replies
theDBguy
post Jul 17 2019, 11:24 AM
Post#2


UA Moderator
Posts: 78,444
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,184
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: 37,446
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 did business for 20 years.
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,184
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


UA Moderator
Posts: 78,444
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: 3,087
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,184
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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    3rd July 2020 - 11:58 PM