tanj1035
asked on
SQL, SYNTAX ERROR
Hi Expert,
for "Lease percentage" , I have to use case when, otherwise, it will return null value sometimes. But the query in bold part got some syntax error, Can you someone help me to fix it? Thanks.
error message : Incorrect syntax near ')' before "end as"
select ld.dealershipname, ld.dealerid, impressions, pencils, isnull(leasecompare,0) as Leasecompare, isnull(totallease,0), isnull(newtotalfinancedeal s,0), isnull(financecompare,0) as Financecompare, isnull(soldarchived,0),
case when isnull(totallease,0) =0 then 0 else cast (totallease as float)
/(case when isnull(totallease,0) =0 then 0 else cast (totallease as float)+case when isnull (newtotalfinancedeals,0) = 0 then 0 else cast (newtotalfinancedeals as float)) end as Leasepercentage,
case when isnull(financecompare,0) =0 then 0 else cast(financecompare as float)/cast(totallease as float) end as Leaseswitch
from #leasingdealers ld
left join #leasecompare2 lc on ld.dealerid=lc.dealerid
left join #financecompare2 fc on fc. dealerid=lc.dealerid
left join #deals d on ld.dealerid=d.dealerid
order by ld.dealershipname asc
for "Lease percentage" , I have to use case when, otherwise, it will return null value sometimes. But the query in bold part got some syntax error, Can you someone help me to fix it? Thanks.
error message : Incorrect syntax near ')' before "end as"
select ld.dealershipname, ld.dealerid, impressions, pencils, isnull(leasecompare,0) as Leasecompare, isnull(totallease,0), isnull(newtotalfinancedeal
case when isnull(totallease,0) =0 then 0 else cast (totallease as float)
/(case when isnull(totallease,0) =0 then 0 else cast (totallease as float)+case when isnull (newtotalfinancedeals,0) = 0 then 0 else cast (newtotalfinancedeals as float)) end as Leasepercentage,
case when isnull(financecompare,0) =0 then 0 else cast(financecompare as float)/cast(totallease as float) end as Leaseswitch
from #leasingdealers ld
left join #leasecompare2 lc on ld.dealerid=lc.dealerid
left join #financecompare2 fc on fc. dealerid=lc.dealerid
left join #deals d on ld.dealerid=d.dealerid
order by ld.dealershipname asc
On the CASE right after your division slash (/), you need an "end" (just before the plus sign).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.