Description:
While working with adventureworks database (2005) I encountered what I believe is a bug. I'm only a student and learning so I don't really know much but here's what happened. I clicked create view option on right click and wrote my code. After clicking apply the code formatted into a single line. Which made my code extremely hard to analyze.
How to repeat:
Here is the view code. Some words aren't in English, but I don't think it matters here.
select a.departmentid,
a.name, round(avg(b.rate), 2) as prosecnaPlata,
round(count(*)/sum(CASE When a.maritalstatus='M' Then 1 Else 0 End), 2) as prosecnoUBraku,
round(avg(cast(datediff(sysdate(), a.birthdate) as float))/365.242199, 2) as prosecnoGodina,
concat(round(100*count(*)/(c.procenatZaposlenihUDept),2),"%")
from (
select e.employeeid, edh.departmentid, d.name, e.maritalstatus, e.birthdate
from adventureworks.employeedepartmenthistory edh
join adventureworks.employee e
on edh.employeeid=e.employeeid
join adventureworks.department d
on edh.departmentid=d.DepartmentID
where edh.enddate is null
) a
join(select count(*) as procenatZaposlenihUDept
from adventureworks.employeedepartmenthistory edh
join adventureworks.employee e
on edh.employeeid=e.employeeid
join adventureworks.department d
on edh.departmentid=d.DepartmentID
where edh.enddate is null
) c
join (
select eph.employeeid, eph.rate, eph.modifieddate
from adventureworks.employeepayhistory eph
join (
select employeeid, max(modifieddate) as date
from adventureworks.employeepayhistory
group by employeeid
) eph2
on eph.employeeid=eph2.employeeid and eph.modifieddate=eph2.date
group by employeeid
) b
on a.employeeid=b.employeeid
group by a.departmentid
order by a.departmentid asc
Suggested fix:
After some testing I realized that cast ... as float in select made this error in the first select. After removing it the code formatted normally after clicking apply.