In SQL query how to use If statement that if column value blank then assign value Empty else Not Empty

Select FirstName, LastName, Address, case when Type is null then 'Contract' else 'Permanent' end as type
from Employee

Select FirstName, LastName, Address, CASE WHEN [Type] IS NULL THEN 'Contract' ELSE 'Permanent' END AS [Type]
from Employee

I have getting error Invalid column name PD.POPDEP

CASE WHEN [PD.POPDJB] IS NULL THEN 'OpEX' ELSE 'CapEX' END AS [PD.POPDJB]

Just thought I'd mention, you also have the ISNULL() function.  So if all you wanted to do was replace the empty values with something, and leave the non empty ones alone you can do:

Select FirstName, LastName, Address, ISNULL(Type, 'Contract') from Employee

http://msdn.microsoft.com/en-us/library/aa933210%28SQL.80%29.aspx

~bp

Try this:

CASE WHEN [PD.POPDJB] IS NULL THEN 'OpEX' ELSE 'CapEX' END AS 'POPDJB'

~bp

Following is full query and in POPDJB column it just displaying CapEX

SELECT     G.GLDGL AS Account, P.POPOID AS PONumber, G.GLDEDT AS EntryDate, A.APTRN1 AS InvoiceTrans, A.APTEDT AS EntryDate,
                      A.APTEDS AS Description, A.APTSU1 AS InvoiceStatus, A.APTBA6 AS APAmount, A.APTCKN AS Cheque#, A.APTDT4 AS ChequeDate,
                      A.APTDT5 AS ClearDate, G.GLDBA AS Amount, A.APTCKN AS CheckNum, A.APTDT4 AS CheckDate, A.APTDT5 AS ClearDate, G.GLDJB AS Job,
                      G.GLDGL AS Costcode, G.GLDBT AS JVN, PD.POPDEP AS Amount, PD.POPDJB as Type,
                              POPDJB = CASE WHEN PD.POPDJB IS NULL THEN 'OpEX' ELSE 'CapEX' END
FROM         dbo.bpl_GLPD AS G LEFT OUTER JOIN
                      dbo.bpl_POPPD AS PD ON G.GLDPJ = PD.POPDPJ LEFT OUTER JOIN
                      dbo.bpl_APPT AS A ON G.GLDEN1 = A.APTEC1 AND G.GLDRN1 = A.APTRN1 AND G.GLDBT = A.APTBT AND G.GLDBE = A.APTBE LEFT OUTER JOIN
                      dbo.bpl_POPPO AS P ON G.GLDTM = P.POPOTM
WHERE     (G.GLDBK = 'ACT') AND (G.GLDJB = 'BT09ADMIG') AND (G.GLDEDT >= '1080101') AND (G.GLDEDT <= '1091231')
ORDER BY G.GLDCC, G.GLDJB, G.GLDEDT, G.GLDJC, G.GLDBT, G.GLDBE, G.GLDBL

Perhaps there are no NULLs in that field?  Maybe they are blanks, and not nulls?  Try a query just looking for nulls in that field.

~bp

If field is blank can I use same stement
POPDJB = CASE WHEN [PD.POPDJB] IS BLANK THEN 'OpEX' ELSE 'CapEX' END

mas1963 said:
>>If field is blank can I use same stement
>>POPDJB = CASE WHEN [PD.POPDJB] IS BLANK THEN 'OpEX' ELSE 'CapEX' END

No

Thanks everyone for help
matthewspatrick your check for NULL, all spaces, or zero length string work fine.

Thanks again