sql - 如何将选定列用作同一子查询的下一个选择的变量

Question

我正在尝试编写查询来创建报告，对于报告，我需要获取在我们的系统上创建表单的用户的经理。因为系统老旧且变化很大，所以没有简单的方法来获取用户的经理或从他们创建表单时获取他们的经理。因此，我之前的人为这项工作创建了一个功能，但成本很高。从函数中获得结果需要很长时间fn_getUserMan。我想调用该函数一次，然后在我的其他选择中使用结果作为变量，因为它依赖于Frm，所以我不能将它从子查询中取出并将其放在主查询中并将其设置在局部变量上并且 sql 不会让我在子查询上设置变量。

所以我的问题是：是否可以FnMAN在我在查询中尝试的下一个选择中使用。它说Invalid column name 'FnMAN'.当我在查询中尝试它时。

在代码块中，似乎我只需要它两次，但实际上我需要它更多。我需要检查结果，如果它是某个人 A，我需要像我对 John 和 Jane 所做的那样使它成为某个人 B。所以我真的需要让它更快。

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

标签： sqlsql-server

Answer 1

我找到了解决方案LEFT JOIN并添加了一个新列以作为FnMAN. 我不知道这是否是一种健康的解决方案，但它确实有效。

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