April 3, 2008 at 12:51 pm
I have the following SELECT statement:
SELECT [CertDescription]
,[CertGroup]
,[CertID]
,[IssueDate]
,[CanExpire]
,[GCStatus]
,GCStatusID
,'GCStatusForDisplay' =
CASE
When GCStatus='Redeemed' THen 'redeemed '+Convert(varchar(20),RedeemedDate,101)+ ' '+ 'With Order# '+ISNULL(Convert(varchar(20),OrderGroupNumber),'No associated Order Group Number')
WHEN GCStatus='Active'AND CanExpire= 1 THEN 'expires '+ Convert(varchar(20),ExpirationDate,101)
WHEN GCStatus='Active' AND Canexpire=0 Then 'No expiration'
WHEN GCStatus='Expired'Then 'Expired '+COnvert(varchar(20),ExpirationDate,101)
ELSE ' '
END
,[GCStatusDate]
,[OrderNUmber]
,[ConsultantID]
,[ConsultantName]
,[CERTAMOUNT]
,[RedeemedValue]
,COnvert(Varchar(20),RedeemedDate,101) AS RedeemedDate
,OrderGroupNumber
from #Final
The bolded code is the question. I have to at a 'G' in front of the OrderGroup Number if it is not NULL or just the text at the end if it is. I tried tacking it on in such as +'G'+ COnvert(..... but that leaves a G on the NULL statement. ANy ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 3, 2008 at 2:12 pm
your approach depends on the setting of CONCAT_NULL_YIELDS_NULL.
if CONCAT_NULL_YIELDS_NULL is ON, then you can just do this:
When GCStatus='Redeemed'
Then 'redeemed '+ Convert(varchar(20),RedeemedDate,101)+
' With Order# '+
ISNULL('G'+ Convert(varchar(20),OrderGroupNumber),
'No associated Order Group Number')
if it's OFF (or you want to play it safe), this should work:
When GCStatus='Redeemed'
Then 'redeemed '+ Convert(varchar(20),RedeemedDate,101)+
' With Order# '+
ISNULL(NULLIF('G','G'+ Convert(varchar(20),OrderGroupNumber)),
'No associated Order Group Number')
April 3, 2008 at 2:21 pm
The first one worked. THanks.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply