Skip to content
Advertisement

How do i use a selected column as a variable for the next selection of the same sub-query

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
4 People found this is helpful
Advertisement