CODEWITHSUNDEEP
sqlsql-server
Bernie Hunt
Bernie Hunt

Reputation: 414

ISNULL Syntax Challenge

I can't seem to figure out the syntax issue here.

This works, but returns nulls;

SELECT jo.Job_Operation, jo.Job, jo.Work_Center, jo.Operation_Service, jo.Est_Total_Hrs,
    (SELECT SUM(jot.Act_Run_Hrs)
    FROM PRODUCTION.dbo.Job_Operation_Time jot
    WHERE jot.Job_Operation = jo.Job_Operation) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
WHERE jo.Job = 'A5076027'

So I'm trying to use ISNULL here but I get an error:

SELECT jo.Job_Operation, jo.Job, jo.Work_Center, jo.Operation_Service, jo.Est_Total_Hrs,
    (ISNULL(SELECT SUM(jot.Act_Run_Hrs)
    FROM PRODUCTION.dbo.Job_Operation_Time jot
    WHERE jot.Job_Operation = jo.Job_Operation,0)) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
WHERE jo.Job = 'A5076027'

The error is: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ','.

Can anyone see what I'm missing here?

Thanks!

Upvotes: 1

Views: 2533

Answers (3)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

This is far more legible in my opinion

SELECT
   jo.Job_Operation, jo.Job, jo.Work_Center, jo.Operation_Service, jo.Est_Total_Hrs
   ,ISNULL(jot.[SumAct_Run_Hrs],0) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
LEFT JOIN (
    SELECT Job_Operation,SUM(Act_Run_Hrs) [SumAct_Run_Hrs]
    FROM PRODUCTION.dbo.Job_Operation_Time
    GROUP BY Job_Operation
) jot ON jot.Job_Operation = jo.Job_Operation
WHERE jo.Job = 'A5076027'

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

I don't think you need a correlated subquery here. This seems to me like a standard left join is all that is required.

SELECT jo.Job_Operation
    , jo.Job
    , jo.Work_Center
    , jo.Operation_Service
    , jo.Est_Total_Hrs
    , SUM(isnull(jot.Act_Run_Hrs, 0)) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
left join PRODUCTION.dbo.Job_Operation_Time jot ON jot.Job_Operation = jo.Job_Operation
WHERE jo.Job = 'A5076027'
GROUP BY
    jo.Job_Operation
    , jo.Job
    , jo.Work_Center
    , jo.Operation_Service
    , jo.Est_Total_Hrs

Upvotes: 2

Adam Silenko
Adam Silenko

Reputation: 3108

try this:

SELECT jo.Job_Operation, jo.Job, jo.Work_Center, jo.Operation_Service, jo.Est_Total_Hrs,
    ISNULL((SELECT SUM(jot.Act_Run_Hrs)
    FROM PRODUCTION.dbo.Job_Operation_Time jot
    WHERE jot.Job_Operation = jo.Job_Operation),0) AS Cost
FROM PRODUCTION.dbo.Job_Operation jo
WHERE jo.Job = 'A5076027'

Upvotes: 3

Related Questions