select           distinct
                 case 
                      WHEN dbo.EventStatusType.KeyName = 'sync' then 'Level 1'
                      WHEN dbo.EventStatusType.KeyName = 'approval' and dbo.EventApprovalLevel.Level = 1 THEN 'Level 2'
                      WHEN dbo.EventStatusType.KeyName = 'approval' THEN 'Level 2'
                      WHEN dbo.EventStatusType.KeyName = 'backofficeadd' THEN 'Rollback' 
                      else Null                
                 end as approvalneeded,
                 goLabor30.dbo.Job.CompanyJobID as jobnumber 
from             goLabor30.dbo.Batch 
                       inner join dbo.Event ON dbo.Event.BatchGuid = dbo.Batch.BatchGuid
                       left join dbo.EventApprovalLevel ON dbo.EventApprovalLevel.EventApprovalLevelGuid = dbo.Event.EventApprovalLevelGuid
where          (dbo.EventApprovalLevel.Level IS NOT Null then dbo.EventApprovalLevel.Level <> 2) ????????????? 

quote:

---

Originally posted by snufse

Not sure if I explain myself correctly. If there is no success on my left join then I do not really want to execute the "where" clause. Using an "AND' will only return records if sucess on the left join.

---

LEFT JOIN dbo.EventApprovalLevel ON
	dbo.EventApprovalLevel.EventApprovalLevelGuid = dbo.EVENT.EventApprovalLevelGuid
	AND dbo.EventApprovalLevel.LEVEL <> 2

select           distinct
                 goLabor30.dbo.Event.BatchGuid as batch_guid,
                 goLabor30.dbo.Batch.ReportDate as batchreportdate,
                 substring(goLabor30.dbo.Batch.Name, 1, 30) as batchname, 
                 substring(goLabor30.dbo.Region.CompanyRegionId, 1, 20) as batchcompany,
                 substring(goLabor30.dbo.Employee.Name, 1, 30) as batchowner,
                 substring(goLabor30.dbo.EventStatusType.Name, 1, 30) as batchstatus,
                 CASE 
                      WHEN dbo.EventStatusType.KeyName = 'sync' then 'Level 1'
                      WHEN dbo.EventStatusType.KeyName = 'approval' and dbo.EventApprovalLevel.Level = 1 THEN 'Level 2'
                      WHEN dbo.EventStatusType.KeyName = 'backofficeadd' THEN 'Rollback' 
                      ELSE Null                
                 end as approvalneeded,
                 goLabor30.dbo.Job.CompanyJobID as jobnumber 
from             goLabor30.dbo.Batch 
                       inner join dbo.Event ON dbo.Event.BatchGuid = dbo.Batch.BatchGuid
                       join dbo.EventStatusType ON dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                       join dbo.Account ON dbo.Account.AccountGuid = dbo.Batch.AccountGuid
                       join dbo.Employee ON dbo.Employee.EmployeeGuid = dbo.Account.EmployeeGuid
                       join dbo.Region ON dbo.Region.RegionGuid = dbo.Batch.RegionGuid
                       join dbo.Job ON dbo.Job.Jobguid = dbo.Event.JobGuid
                       join dbo.EventType ON dbo.EventType.EventTypeGuid = dbo.Event.EventTypeGuid
                       left join dbo.EventApprovalLevel ON dbo.EventApprovalLevel.EventApprovalLevelGuid = dbo.Event.EventApprovalLevelGuid
where          goLabor30.dbo.Region.CompanyRegionID = '00022' and
               goLabor30.dbo.Batch.Reportdate >=  '2009-08-24'and
               goLabor30.dbo.Batch.ReportDate <= '2009-08-25' and
               dbo.EventStatusType.Name <> 'Loaded to ERP' and
               (dbo.EventApprovalLevel.Level IS NOT Null and dbo.EventApprovalLevel.Level <> 2)
order by       goLabor30.dbo.Event.BatchGuid

quote:

---

Originally posted by Transact Charlie

quote:

---

Originally posted by snufse

Not sure if I explain myself correctly. If there is no success on my left join then I do not really want to execute the "where" clause. Using an "AND' will only return records if sucess on the left join.

---

I think what you mean is that your WHERE clause is eliminating rows that don't have a match to your LEFT JOIN? (and you don't want them to be removed)


If that's what you mean then just move the logic in the WHERE into the the JOIN.

Try replacing your join with this:

LEFT JOIN dbo.EventApprovalLevel ON
	dbo.EventApprovalLevel.EventApprovalLevelGuid = dbo.EVENT.EventApprovalLevelGuid
	AND dbo.EventApprovalLevel.LEVEL <> 2

and ditch the WHERE clause completely.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

That was my solution 3 posts before

---