February 11, 2013 at 9:40 am
I'm trying to do a CASE statement comparing different columns for different result sets, and am wondering if the CASE requires all arguments to run against the exact same column? Purpose: depending upon the result set in 2 different columns from the backupset table, I want a different column to display which tool ran the backup.
Problem code, with comments on the problem part:
SELECT mb.Database_Name AS DatabaseName
, mb.recovery_model AS RecoveryModel
-- this first CASE works great
, CASE WHEN mb.type = 'L' THEN 'Tran Log'
WHEN mb.type = 'D' THEN 'Full Backup'
WHEN mb.type = 'I' THEN 'Differential'
WHEN mb.type = 'F' THEN 'File or filegroup'
WHEN mb.type = 'G' THEN 'Differential file'
WHEN mb.type = 'P' THEN 'Partial'
WHEN mb.type = 'Q' THEN 'Differential partial'
END AS BackupType
, CONVERT(nvarchar(8), mb.backup_finish_date, 10) + ' ' + CONVERT(nvarchar(5), mb.backup_finish_date, 8) AS BackupFinishDate
, CONVERT(varchar,(mb.backup_finish_date - mb.backup_start_date),108) AS 'time_HMS'
, mb.description --AS BackupToolDesc
, mb.name --AS isNetworker
-- Problem case below--is it because of different columns? Or am I missing something else?
, CASE WHEN mb.name LIKE 'LegaltoNW%' THEN 'Networker'
WHEN mb.description LIKE 'TDP%' THEN 'TDP'
--WHEN mb.description IS NULL AND mb.name LIKE 'LegaltoNW%' THEN 'Networker'
ELSE 'one off by ' + mb.user_name
END AS ClarieDesc
FROM msdb.dbo.backupset mb
WHERE mb.backup_start_date > getDate() - 30
and mb.type = 'd' -- full backups only
ORDER BY BackupFinishDate DESC
Thanks in advance for any helpful suggestions, I make no claims at being a Ninja coder. 😉
February 11, 2013 at 9:54 am
The option you have commented out will never be selected because there's an option before it in the CASE construct which will:
CASE
WHEN mb.name LIKE 'LegaltoNW%' THEN 'Networker'
You need to put the commented-out option before this option.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 11, 2013 at 10:03 am
am wondering if the CASE requires all arguments to run against the exact same column?
No. CASE is very flexible: you can compare any columns you need to, in any order and combination you need.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 11, 2013 at 10:12 am
Clarie DeWayne (2/11/2013)
I'm trying to do a CASE statement comparing different columns for different result sets, and am wondering if the CASE requires all arguments to run against the exact same column? Purpose: depending upon the result set in 2 different columns from the backupset table, I want a different column to display which tool ran the backup.Problem code, with comments on the problem part:
SELECT mb.Database_Name AS DatabaseName
, mb.recovery_model AS RecoveryModel
-- this first CASE works great
, CASE WHEN mb.type = 'L' THEN 'Tran Log'
WHEN mb.type = 'D' THEN 'Full Backup'
WHEN mb.type = 'I' THEN 'Differential'
WHEN mb.type = 'F' THEN 'File or filegroup'
WHEN mb.type = 'G' THEN 'Differential file'
WHEN mb.type = 'P' THEN 'Partial'
WHEN mb.type = 'Q' THEN 'Differential partial'
END AS BackupType
, CONVERT(nvarchar(8), mb.backup_finish_date, 10) + ' ' + CONVERT(nvarchar(5), mb.backup_finish_date, 8) AS BackupFinishDate
, CONVERT(varchar,(mb.backup_finish_date - mb.backup_start_date),108) AS 'time_HMS'
, mb.description --AS BackupToolDesc
, mb.name --AS isNetworker
-- Problem case below--is it because of different columns? Or am I missing something else?
, CASE WHEN mb.name LIKE 'LegaltoNW%' THEN 'Networker'
WHEN mb.description LIKE 'TDP%' THEN 'TDP'
--WHEN mb.description IS NULL AND mb.name LIKE 'LegaltoNW%' THEN 'Networker'
ELSE 'one off by ' + mb.user_name
END AS ClarieDesc
FROM msdb.dbo.backupset mb
WHERE mb.backup_start_date > getDate() - 30
and mb.type = 'd' -- full backups only
ORDER BY BackupFinishDate DESC
Thanks in advance for any helpful suggestions, I make no claims at being a Ninja coder. 😉
No you need not run case statement against same column, you can use logical operators to join them, example,
here 'and' operator is being used to join the two conditions in Case
CASE WHEN mb.type = 'L' and mb.database_name = 'some value' then 'Tran Log'
February 11, 2013 at 11:40 am
Thank's y'all for your responses--I had a typo on the 'legalto', which is why none of my results returned what I was looking for. My actual code was correct, except for that typo issue. They get me every time. :w00t:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply