ryann
asked on
T SQL Coalesece instead of Case
This query calculates the AdjValue by subtracting TaxDepr from DpreciableBasis. To get the TaxDepr I call a function passing an EquipOid and a date. The date is converted to the 1st day of the next month. If the result of the function is 0.00 then I grab the value from another table. Can I use the Coalesce expression instead of a Case statement? How would this be done? Thanks.
,AdjValue = isnull([ED1].TaxDepreciati onDeprecia bleBasis,0 )-
(
select
case
when isnull(dbo.GetEquipmentTot alTaxDeprA mtFN
(E1.EquipmentOid,CONVERT(v archar(7), DATEADD(m, 1, ff1.DateFunded), 23) + '-01'),0.00) = 0.00
then isnull([ED1].TaxDepreciati onLifetime ,0.00)
else
(dbo.GetEquipmentTotalTaxD eprAmtFN
(E1.EquipmentOid,CONVERT(v archar(7), DATEADD(m, 1, ff1.DateFunded), 23) + '-01'))
end
)
,AdjValue = isnull([ED1].TaxDepreciati
(
select
case
when isnull(dbo.GetEquipmentTot
(E1.EquipmentOid,CONVERT(v
then isnull([ED1].TaxDepreciati
else
(dbo.GetEquipmentTotalTaxD
(E1.EquipmentOid,CONVERT(v
end
)
can GetEquipmentTotalTaxDeprAm tFN actually return 0? (not null)
Coalesce will only help you if you're returning null values.
Coalesce will only help you if you're returning null values.
ASKER
My Case statement works fine. A person told me that Coalesce would be another option I could use and would make the code easier to read. I am just curious how I would code this query using coalesce instead of a Case statement.
Missed the second IsNULL.
You can do this:
COALESCE(dbo.GetEquipmentT otalTaxDep rAmtFN
(E1.EquipmentOid,CONVERT(v archar(7), DATEADD(m, 1, ff1.DateFunded), 23) + '-01'), [ED1].TaxDepreciationLifet ime,0.00)
You can do this:
COALESCE(dbo.GetEquipmentT
(E1.EquipmentOid,CONVERT(v
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
isnull(dbo.GetEquipmentTot
If you have a concern that the first part of the ISNULL function will return 0.00, you can wrap that part up in the NullIF function.