June 24, 2008 at 12:25 am
I have a table as follows :
[ITEM_CODE],[Trade_Name],[Refill ID],[Refill Quantity],[Refill Period]
491692 APROVEL, ,Null ,Null ,Null
491693 ,PROSPAN ,234 ,2 ,1month
491694, CLARITINE (generic) ,895 ,30 ,1month
I want that is if [Refill ID] is null then it should show "No REFIL"
else it should show the details so my out put will look like
491692,APROVEL, No Refil, Null
491693,PROSPAN,234, 2 / 1month
491694,CLARITINE (generic), 30/1month
can somebody help me with this please
June 24, 2008 at 12:42 am
Should be possible with a case statement. Something like this (partial code only)
SELECT ...
CASE WHEN [Refill ID] IS NULL
THEN 'No REFIL'
ELSE <Whatever you want to show here>
END AS RefillPeriod
FROM ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2008 at 1:01 am
I did that but I am getting an error
this is what I did
SELECT ....
,isnull([PAID],'NO') as [PAID] ,
CASE WHEN isnull(a.[Refill Sales ID],0) = 0 then 'No Refil'
ELSE a.[Refill Quantity]+'/'+a.[Refill Period] ,
A.[Notes]
FROM
[Medication Sales] A
and the error I get is
Incorrect syntax near ','.
June 24, 2008 at 1:21 am
You're missing an END on the case
SELECT isnull([PAID],'NO') as [PAID] ,
CASE WHEN isnull(a.[Refill Sales ID],0) = 0 then 'No Refil'
ELSE a.[Refill Quantity]+'/'+ a.[Refill Period] END,
A.[Notes]
FROM
[Medication Sales] A
If quantity and period are numbers, you'll also have to cast them to varchar to do the concatenation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2008 at 1:45 am
thanks.it worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply