Joining 2 select results into a single row

  • 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

  • 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'

    • This reply was modified 1 month, 1 week ago by  ktflash.

    I want to be the very best
    Like no one ever was

  • @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

  • 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

  • Thanks ktflash..worked like a charm !

  • 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

  • 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

  • Did you read my answer in your previous thread for this problem?

  •   wrong code posted.let me post it back

    @ktflash

    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

    • This reply was modified 1 month ago by  mtz676.
    • This reply was modified 1 month ago by  mtz676.
  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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.

    • This reply was modified 1 month ago by  mtz676.
  • 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?

  • They worked good. I want to know how to fix the one I attempted.

     

  • mtz676 wrote:

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • mtz676 wrote:

    @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?

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply