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,438
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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    2nd July 2020 - 05:00 AM