I am trying write a query to create a report and for the report, I need to get the manager of the user who created a form on our system. Because the system is old and changed a lot, there is no easy way to get the manager of the user or get their manager from the time when they created the form. So, the person before me created a function for the job but it is costly. It takes way too long to get a result from the function fn_getUserMan
. I want to call the function once and then use the result on my other selections as a variable and because it is dependent on Frm
, I cannot take it out of the sub-query and have it on the main query and set it on a local variable and sql wont let me set the variable on the sub-query.
So my question is this: Is it possible to use FnMAN
in my next selection as i tried in the query. It says Invalid column name 'FnMAN'.
when i try it like in the query.
In the code block, it seems like i need it only twice but i actually need it a lot more. I need to check the result and if its some person A, i need to make it some person B like i did with John and Jane. So i really need to make this faster.
select *,(--More selection--) from (select -- --Some selection of 10s of columns -- (select dbo.fn_getUserMAN(( select M.txtPerson from Frm (nolock) Fm JOIN FLOWDOCUMENTS(nolock) FD ON Fm.ID = FD.FILEPROFILEID JOIN FLOWREQUESTS(nolock) FR on FD.PROCESSID = FR.PROCESSID JOIN MdlFrm (nolock) M on M.ID = FR.EVENTFORMID where FR.EVENTFORMID !=-1 and F.FrmNo = Fm.FrmNo)) ) AS 'FnMAN', (SELECT TOP 1 ( CASE WHEN (select txtPerson from Frm_Info (nolock) as BB inner join FLOWREQUESTS(nolock) FQ on BB.ID = FQ.EVENTFORMID and FQ.PROCESSID = FD.PROCESSID) is not null or (select txtPerson from Frm_Info (nolock) as BB inner join FLOWREQUESTS(nolock) FQ on BB.ID = FQ.EVENTFORMID and FQ.PROCESSID = FD.PROCESSID) !='' THEN CASE WHEN FnMAN = 'John Doe' THEN 'Jane Doe' --More checks and switches on managers ELSE FnMAN END WHEN F.txtManager IS NOT NULL THEN F.txtManager END)) from Frm (NOLOCK) F INNER JOIN FLOWDOCUMENTS(NOLOCK) FD ON FD.FILEPROFILEID = F.ID INNER JOIN LIVEFLOWS(NOLOCK) LF ON LF.ID = FD.PROCESSID INNER JOIN FLOWSTATUSES(NOLOCK) FS ON FS.PROCESS = LF.PROCESS AND FS.VERSION =LF.FLOWVERSION AND FS.STATUS = LF.STATUS WHERE LF.DELETED = 0 and F.FrmNo IS NOT NULL and F.FrmNo != '') T
Advertisement
Answer
I found the solution with LEFT JOIN
and added a new column to use as a result of FnMAN
. I dont know whether or not this is a healthy way of having a a solution but it works.
select *,(--More selection--) from (select -- --Some selection of 10s of columns -- (SELECT TOP 1 ( CASE WHEN (select txtPerson from Frm_Info (nolock) as BB inner join FLOWREQUESTS(nolock) FQ on BB.ID = FQ.EVENTFORMID and FQ.PROCESSID = FD.PROCESSID) is not null or (select txtPerson from Frm_Info (nolock) as BB inner join FLOWREQUESTS(nolock) FQ on BB.ID = FQ.EVENTFORMID and FQ.PROCESSID = FD.PROCESSID) !='' THEN CASE WHEN FnMAN.MAN = 'John Doe' THEN 'Jane Doe' --More checks and switches on managers ELSE FnMAN.MAN END WHEN F.txtManager IS NOT NULL THEN F.txtManager END)) from Frm (NOLOCK) F INNER JOIN FLOWDOCUMENTS(NOLOCK) FD ON FD.FILEPROFILEID = F.ID INNER JOIN LIVEFLOWS(NOLOCK) LF ON LF.ID = FD.PROCESSID INNER JOIN FLOWSTATUSES(NOLOCK) FS ON FS.PROCESS = LF.PROCESS AND FS.VERSION =LF.FLOWVERSION AND FS.STATUS = LF.STATUS LEFT JOIN ( (select Fm.FormID, dbo.fn_getUserMAN(M.txtPerson) MAN from Frm (nolock) Fm JOIN FLOWDOCUMENTS(nolock) FD ON Fm.ID = FD.FILEPROFILEID JOIN FLOWREQUESTS(nolock) FR on FD.PROCESSID = FR.PROCESSID JOIN MdlFrm (nolock) M on M.ID = FR.EVENTFORMID where FR.EVENTFORMID !=-1) ) AS 'FnMAN' on Fm.FormID = F.FormID, WHERE LF.DELETED = 0 and F.FrmNo IS NOT NULL and F.FrmNo != '') T