select(
select name,enabled,
case
when enabled = 1 then 'active'
else 'notactive'
end as JobStatus
from msdb.dbo.sysjobs where name='JobA'),
(SELECT CASE
WHEN EXISTS ( SELECT name FROM msdb.dbo.sysjobs WHERE name = 'JobA' )
THEN 'Exists'
ELSE 'Does not Exists'
END as 'JobAPresentAbsent')
Error:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
How can I rewrite the below query so that I can have all 4 column value in a single row.
Thanks
October 21, 2020 at 5:25 am
is there a reason why u are not doing it in one go? unless i might be overlooking something obvious here
select name
,enabled
,case when enabled = 1
then 'active'
else 'notactive' end as JobStatus
,CASE WHEN EXISTS ( SELECT name FROM msdb.dbo.sysjobs sj where sj.name = 'JobA' )
THEN 'Exists'
ELSE 'Does not Exists' END as 'JobAPresentAbsent'
from msdb.dbo.sysjobs
where name = 'JobA'
I want to be the very best
Like no one ever was
October 21, 2020 at 6:08 am
@ktflash..thanks but...your code returns well when the job exists but....
you code returns nothing if the job does not exist.
My requirement is if the job does not exist it should return something like this:
name enabled JobStatus JobPresentAbsent
JobA Job Not present Job Not present Job does not exists
October 21, 2020 at 8:18 am
These might be abominations not sure, first one is an if which is the easiest version if u can use it.
The 2nd version just uses a Dummyvalue if the values not found.
if (SELECT count(*) FROM msdb.dbo.sysjobs sj where sj.name = 'JobA' )= 1
BEGIN
select name
,enabled
,case when enabled = 1
then 'active'
else 'notactive' end as JobStatus
,CASE WHEN EXISTS ( SELECT name FROM msdb.dbo.sysjobs sj where sj.name = 'JobA' )
THEN 'Exists'
ELSE 'Does not Exists' END as 'JobAPresentAbsent'
from msdb.dbo.sysjobs
where name = 'JobA'
END
ELSE
BEGIN
select name = 'JobA'
,enabled = 'Job Not present'
,Jobstatus = 'Job Not present'
,JobAPresentAbsent = 'Job does not exists'
END
select name
,enabled
,JobStatus
,JobAPresentAbsent
from (
select name
,enabled=cast(enabled as nvarchar(1))
,case when enabled = 1
then 'active'
else 'notactive' end as JobStatus
,'Exists' as JobAPresentAbsent
from msdb.dbo.sysjobs
UNION ALL
select 'Searched Job not Existing'
,'Job Not present'
,'Job Not present'
,'Does not Exists'
) SJ
where CASE WHEN EXISTS ( SELECT name FROM msdb.dbo.sysjobs sj where sj.name = 'JobA')
THEN ( SELECT name FROM msdb.dbo.sysjobs sj where sj.name = 'JobA')
ELSE 'Searched Job not Existing' END = NAME
I want to be the very best
Like no one ever was
October 21, 2020 at 8:57 am
Thanks ktflash..worked like a charm !
October 21, 2020 at 11:47 am
the issue still persists..
I am trying to do an
select
(select...),
(select...exists...)
Those inner selects if they have an EXISTS it is throwing an error:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
Is there a workaround
October 22, 2020 at 9:12 am
post full query that leads to the error and use "insert/editcode sample" function plz
I want to be the very best
Like no one ever was
October 22, 2020 at 9:26 am
Did you read my answer in your previous thread for this problem?
_____________
Code for TallyGenerator
October 22, 2020 at 9:50 am
wrong code posted.let me post it back
wrong code posted.let me post it back
we have a MYDB database.If that database exist only then there will relevant jobs available.If the MYDB does not exist then the related jobs do not exist.Hence the update statement to make it clear that MYDB does not exist- update @Temptable set JobFPresentAbsent = 'MYDB not installed so no related MYDB jobs' where MYDB='Absent'
If we have MYDB we will have MYDB jobs and they may or may be disabled that where your query helps.
error:Msg 116, Level 16, State 1, Line 34
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Thanks
October 22, 2020 at 9:59 am
DECLARE @job_name nvarchar(128)
SET @job_name = 'JobA'
SELECT
job_name AS name,
COALESCE(CAST(j.enabled AS varchar(1)), 'Job not present') AS enabled,
CASE WHEN j.enabled = 1 THEN 'active' ELSE 'notactive' END AS JobStatus,
CASE WHEN j.name IS NOT NULL THEN 'Job exists' ELSE 'Job does not exist' END AS JobPresentAbsent
FROM ( SELECT @job_name AS job_name ) AS job_name
LEFT OUTER JOIN msdb.dbo.sysjobs j ON j.name = job_name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 22, 2020 at 11:40 am
Error:Msg 116, Level 16, State 1, Line 26
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
select
(select @@servername) as ServerName,
(select @@version) as ServerVersion,
(select name
,enabled
,JobStatus
,JobAPresentAbsent
from (
select name
,enabled=cast(enabled as nvarchar(1))
,case when enabled = 1
then 'active'
else 'notactive' end as JobStatus
,'Exists' as JobAPresentAbsent
from msdb.dbo.sysjobs
UNION ALL
select 'Searched Job not Existing'
,'Job Not present'
,'Job Not present'
,'Does not Exists'
) SJ
where CASE WHEN EXISTS ( SELECT name FROM msdb.dbo.sysjobs sj where sj.name = 'JobA')
THEN ( SELECT name FROM msdb.dbo.sysjobs sj where sj.name = 'JobA')
ELSE 'Searched Job not Existing' END = NAME)
Is there a workaround to this EXISTS issue.
October 22, 2020 at 12:11 pm
I posted the solution in your previous thread, Scott posted pretty much the same solution here, just above. The only difference is that I used one more left join - to master.dbo.sysdatabases to indicate if the database exists and is online.
What did you find unsatisfactory with these 2 solutions?
_____________
Code for TallyGenerator
October 22, 2020 at 2:09 pm
They worked good. I want to know how to fix the one I attempted.
They worked good. I want to know how to fix the one I attempted.
Hate to be the bearer of bad news, but doing this the way you want to isn't going to work, and because what you want to do just isn't possible. Whenever you need to select more than one column of data from something, it can't be done in a sub-query that appears within the part of your SELECT statement that appears before the FROM clause... EVER ! So that method is just not going to work. You've been given great alternative queries. The idea behind them is to create a "row" based solely on the job name, and then either OUTER APPLY or LEFT JOIN to a query against the sysjobs table. That way, you always have a row representing the job you are interested in, and may or may not have a matching row from sysjobs.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2020 at 3:04 am
@ktflash..thanks but...your code returns well when the job exists but....
you code returns nothing if the job does not exist.
My requirement is if the job does not exist it should return something like this:
name enabled JobStatus JobPresentAbsent
JobA Job Not present Job Not present Job does not exists
here is a simple question for you to start with:
if the job does not exist - where a record with its name should come from?
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply