April 29, 2009 at 3:56 am
Hi
I am looking to return a value in a column of my query if the query returns no results, figuring this will need to be done with a stored procedure...
does anyone know how to perform this the query i have is shown below that works fine if a job exists, all i wish to do is if no results are found return string 'No Jobs' in the equipment field of my query, ..
any pointers . will be gladly appreciated... last task finally!!!
SELECT dbo.Calendar.[Job Date], dbo.Calendar.NextBusinessDay, dbo.JobSpec.Branch, dbo.JobSpec.[Deliver Time], dbo.JobSpec.[Job Type],
dbo.JobSpec.[Company Name], dbo.JobSpec.[Site Location], dbo.JobSpec.Equipment, dbo.JobSpec.[Purchase Goods], dbo.JobSpec.Driver,
dbo.Calendar.ID
FROM dbo.Calendar INNER JOIN
dbo.JobSpec ON dbo.Calendar.ID = dbo.JobSpec.ID
WHERE (dbo.Calendar.[Job Date] = [dbo].fn_AddBizDays(1)) AND (dbo.JobSpec.Branch = 'Salisbury')
April 29, 2009 at 4:48 am
select
case when x.greating is null then 'nothing' else x.greating end
from
(
select
'hello' as 'greating'
union
select
'goodbye'
union
select
null
)x
use a case statement within your select part. if its null then output the string else output whats in the column.
April 29, 2009 at 5:05 am
I am not sure....still you can try with below query.
Hope GROP BY wont make any diference.
SELECT [Job Date], NextBusinessDay, Branch, [Deliver Time], [Job Type]
, [Company Name], [Site Location]
, Equipment=case
when count(A.[Job Date]) >0 then Equipment
else 'No Jobs'
end
, [Purchase Goods], Driver, ID
FROM
(
SELECT dbo.Calendar.[Job Date], dbo.Calendar.NextBusinessDay, dbo.JobSpec.Branch, dbo.JobSpec.[Deliver Time], dbo.JobSpec.[Job Type]
, dbo.JobSpec.[Company Name], dbo.JobSpec.[Site Location]
, dbo.JobSpec.Equipment
, dbo.JobSpec.[Purchase Goods], dbo.JobSpec.Driver
, dbo.Calendar.ID
FROMdbo.Calendar
INNER JOIN dbo.JobSpec ON dbo.Calendar.ID = dbo.JobSpec.ID
WHERE(dbo.Calendar.[Job Date] = [dbo].fn_AddBizDays(1))
AND(dbo.JobSpec.Branch = 'Salisbury')
) A
GROUP BY [Job Date], NextBusinessDay, Branch, [Deliver Time], [Job Type]
, [Company Name], [Site Location], Equipment, [Purchase Goods], Driver, ID
April 29, 2009 at 5:44 am
Hi San..
Many thanks for your efforts, looks good and works if query finds a job but if 0 results Expr1 remains blank any ideas?
April 29, 2009 at 7:07 am
This came up in another thread. You can find a couple of competing solutions here.
Basically, you can run an IF Exists with your query to see if there is going to be any data. If so, go ahead and run your query. If not, do a select of some constant text.
If Exists (your query)
(your query)
Else
SELECT 'No rows found'
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2009 at 9:46 am
Hi Bob..
thanks for the suggestion, i am somewhat a novice in these here sql parts :hehe: and struggling a bit.. Tried your suggestion and what i have is below.. but kicks out in my adp query designer as incorrect syntax near IF. Any pointers?
IF EXISTS (SELECT dbo.Calendar.[Job Date], dbo.Calendar.NextBusinessDay, dbo.JobSpec.Branch, dbo.JobSpec.[Deliver Time], dbo.JobSpec.[Job Type],
dbo.JobSpec.[Company Name], dbo.JobSpec.[Site Location], dbo.JobSpec.Equipment, dbo.JobSpec.[Purchase Goods], dbo.JobSpec.Driver,
dbo.Calendar.ID
FROM dbo.Calendar INNER JOIN
dbo.JobSpec ON dbo.Calendar.ID = dbo.JobSpec.ID
WHERE (dbo.Calendar.[Job Date] = [dbo].fn_AddBizDays(1)) AND (dbo.JobSpec.Branch = 'Scotland'))
(SELECT dbo.Calendar.[Job Date], dbo.Calendar.NextBusinessDay, dbo.JobSpec.Branch, dbo.JobSpec.[Deliver Time], dbo.JobSpec.[Job Type],
dbo.JobSpec.[Company Name], dbo.JobSpec.[Site Location], dbo.JobSpec.Equipment, dbo.JobSpec.[Purchase Goods], dbo.JobSpec.Driver,
dbo.Calendar.ID
FROM dbo.Calendar INNER JOIN
dbo.JobSpec ON dbo.Calendar.ID = dbo.JobSpec.ID
WHERE (dbo.Calendar.[Job Date] = [dbo].fn_AddBizDays(1)) AND (dbo.JobSpec.Branch = 'Scotland')) ELSE
SELECT 'No rows found'
April 29, 2009 at 10:04 am
I loaded your code into Management Studio and the syntax checks out fine in 2005. I suspect the ADP designer just can't handle it, but it should run
However, try removing the parenthesis from around the SECOND copy of your query, the one that would actually execute and return data. Let's go ahead and try BEGIN...END blocks as well.
IF EXISTS
(SELECTdbo.Calendar.[Job Date], dbo.Calendar.NextBusinessDay, dbo.JobSpec.Branch, dbo.JobSpec.[Deliver Time], dbo.JobSpec.[Job Type],
dbo.JobSpec.[Company Name], dbo.JobSpec.[Site Location], dbo.JobSpec.Equipment, dbo.JobSpec.[Purchase Goods], dbo.JobSpec.Driver,
dbo.Calendar.ID
FROM dbo.Calendar INNER JOIN
dbo.JobSpec ON dbo.Calendar.ID = dbo.JobSpec.ID
WHERE (dbo.Calendar.[Job Date] = [dbo].fn_AddBizDays(1)) AND (dbo.JobSpec.Branch = 'Scotland'))
--
BEGIN
SELECT dbo.Calendar.[Job Date], dbo.Calendar.NextBusinessDay, dbo.JobSpec.Branch, dbo.JobSpec.[Deliver Time], dbo.JobSpec.[Job Type],
dbo.JobSpec.[Company Name], dbo.JobSpec.[Site Location], dbo.JobSpec.Equipment, dbo.JobSpec.[Purchase Goods], dbo.JobSpec.Driver,
dbo.Calendar.ID
FROM dbo.Calendar INNER JOIN dbo.JobSpec ON dbo.Calendar.ID = dbo.JobSpec.ID
WHERE (dbo.Calendar.[Job Date] = [dbo].fn_AddBizDays(1)) AND (dbo.JobSpec.Branch = 'Scotland')
END
--
ELSE
BEGIN
SELECT 'No rows found'
END
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2009 at 10:19 am
If your calendar table will always have rows in for a given date, just change your join to a left outer and use coalesce for when your equipment column is NULL, that's only if your calendar table will have the rows in though:
SELECT dbo.Calendar.[Job Date],
dbo.Calendar.NextBusinessDay,
dbo.JobSpec.Branch,
dbo.JobSpec.[Deliver Time],
dbo.JobSpec.[Job Type],
dbo.JobSpec.[Company Name],
dbo.JobSpec.[Site Location],
coalesce(dbo.JobSpec.Equipment,'No Jobs'),
dbo.JobSpec.[Purchase Goods],
dbo.JobSpec.Driver,
dbo.Calendar.ID
FROM dbo.Calendar LEFT OUTER JOIN
dbo.JobSpec
ON dbo.Calendar.ID = dbo.JobSpec.ID
WHERE (dbo.Calendar.[Job Date] = [dbo].fn_AddBizDays(1)) AND
(dbo.JobSpec.Branch = 'Salisbury')
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 29, 2009 at 10:23 am
Hi Bob..
yep your right although error spewed by my adp .. it does run .. one thing though where i have text SELECT 'No rows found' I wish to have text 'No Jobs found in my equipment field first row, this query is used in powerpoint pres and needs some data or table doesn't display.. how would i set this first row to be empty apart from the equipment row with 'no jobs' text..
April 29, 2009 at 10:32 am
All you need is empty strings with the same column headings as in your original query. The datatypes shouldn't conflict with your original query, because only one of them is going to run. (I'm assuming Powerpoint will just accept whatever is delivered, without having to know the datatypes in advance.)
ELSE
BEGIN
SELECT '' as [Job Date], '' as NextBusinessDay-- etc, etc, etc
UNION ALL
SELECT 'No jobs found' as [Job Date], '' as NextBusinessDay-- etc, etc, etc
END
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 30, 2009 at 1:30 am
Bob you are a legend! thanks..:-D
April 30, 2009 at 7:22 am
I'm a legend in my my own mind 😉
Seriously, there are people on this site who know a LOT more about SQL than I do. Remember, in the link I pointed you to, Jeff Moden was the man who served up the solution you are using. But it's a pleasure to be of assistance.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply