Problems with CASE clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problems with CASE clause

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-24 : 11:17:22

Cannot seem to get my case to work:


select           goLabor30.dbo.Batch.Name as name, 
                 goLabor30.dbo.Batch.ReportDate as reportdate,
                 goLabor30.dbo.Region.CompanyRegionId as company,
                 goLabor30.dbo.Employee.Name as batchowner,
                 goLabor30.dbo.EventStatusType.Name as batchstatus,
                 SELECT CASE "approvalneeded"
                      WHEN dbo.EventStatusType.KeyName = 'sync' THEN 'Level 1' else Null 
                      WHEN dbo.EventStatusType.KeyName = 'approval' and dbo.EventApprovalLevel.Level = 1 THEN 'Level 2' else Null 
                      WHEN dbo.EventStatusType.KeyName = 'backofficeadd' THEN 'Rollback' else Null                
                 end
                 SELECT CASE "employee" 
                      WHEN if at least one record found in EventType table then = "Y" else = "N"                
                 end                 
from             goLabor30.dbo.Batch

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-24 : 11:36:24

[code]
select goLabor30.dbo.Batch.Name as name,
goLabor30.dbo.Batch.ReportDate as reportdate,
goLabor30.dbo.Region.CompanyRegionId as company,
goLabor30.dbo.Employee.Name as batchowner,
goLabor30.dbo.EventStatusType.Name as batchstatus,
SELECT 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"
SELECT CASE
WHEN if at least one record found in EventType table then = "Y" else = "N"
end as "employee"
from goLabor30.dbo.Batch
[/code]
What exactly are you trying to do with the second case statement?

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-24 : 11:39:21

Hi RickD,

The 2nd case statement is a "Yes" or "No" based upon if there are record(s) in the EventType table.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-24 : 11:46:46

Can you join the batch table to the EventType table in any way? If so, then you can do a simple left join. If not, you will have to do an if exists (can't remmeber if you can do these in a case though)..

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-24 : 11:49:28

See below for the 2nd case

from             goLabor30.dbo.Batch 
                       inner join dbo.Region ON dbo.Region.RegionGuid = dbo.Batch.RegionGuid
                       inner join dbo.Account ON dbo.Account.AccountGuid = dbo.Batch.AccountGuid
                       inner join dbo.Employee ON dbo.Employee.EmployeeGuid = dbo.Account.EmployeeGuid
                       inner join dbo.Event ON dbo.Event.BatchGuid = dbo.Batch.BatchGuid
                       inner join dbo.Job ON dbo.Job.Jobguid = dbo.Event.JobGuid
                       inner join dbo.EventStatusType ON dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                       inner join dbo.EventApprovalLevel.EventApprovalLevelGuid on dbo.Event.EventApprovalLevelGuid
                       left join dbo.EvenType.EventTypeGuid = dbo.Event.EventTypeGuid

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-24 : 11:51:15

Ok, so do:

CASE WHEN dbo.EvenType.EventTypeGuid is not null THEN 'Y' ELSE 'N' END as "employee"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-24 : 12:05:11

Rickd,

Got it working. Thank you.

select           goLabor30.dbo.Batch.Name as name, 
                 goLabor30.dbo.Batch.ReportDate as reportdate,
                 goLabor30.dbo.Region.CompanyRegionId as company,
                 goLabor30.dbo.Employee.Name as batchowner,
                 goLabor30.dbo.EventStatusType.Name 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",
                 CASE 
                      WHEN dbo.EventType.EventTypeGuid is not null THEN 'Y' ELSE 'N'
                 end as "employee",
                 goLabor30.dbo.Job.CompanyJobID as jobname 
from             goLabor30.dbo.Batch 
                       inner join dbo.Region ON dbo.Region.RegionGuid = dbo.Batch.RegionGuid
                       inner join dbo.Account ON dbo.Account.AccountGuid = dbo.Batch.AccountGuid
                       inner join dbo.Employee ON dbo.Employee.EmployeeGuid = dbo.Account.EmployeeGuid
                       inner join dbo.Event ON dbo.Event.BatchGuid = dbo.Batch.BatchGuid
                       inner join dbo.Job ON dbo.Job.Jobguid = dbo.Event.JobGuid
                       inner join dbo.EventStatusType ON dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                       inner join dbo.EventApprovalLevel ON dbo.EventApprovalLevel.EventApprovalLevelGuid = dbo.Event.EventApprovalLevelGuid
                       left join dbo.EventType ON dbo.EventType.EventTypeGuid = dbo.Event.EventTypeGuid
where          goLabor30.dbo.Region.CompanyRegionID = '00032' and
               goLabor30.dbo.Batch.Reportdate >=  '2009-05-24'and
               goLabor30.dbo.Batch.ReportDate <= '2009-05-29'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources