如何包含CASE IF THEN语句SQL
我想在下面的存储过程中创建一个case语句。我想@OHDept做到以下几点:如何包含CASE IF THEN语句SQL
如果系= 15,则表明16
一切我已经试过给了我某种错误,如“规定的非布尔类型的表达式在
了条件,预计,不久的“其他人”的较量。我知道我做错了什么,但我不知道。有没有人能帮助我吗? 谢谢!
(@JCCo bCompany, @BeginContract bContract ='', @EndContract bContract= 'zzzzzzzzz', @ThroughMth bDate,@BegMth bDate,@BegDept bDept='', @EndDept bDept='zzzzzzzzzz', @Status char(1)='A', @BegMthClosed bDate, @EndMthClosed bDate)
With Recompile
as
set nocount on
declare @OHDept varchar(2)
declare @CT1Desc varchar(5), @CT2Desc varchar(5), @CT3Desc varchar(5),
@CT4Desc varchar(5), @CT5Desc varchar(5), @CT6Desc varchar(5),
@CT7Desc varchar(5), @CT8Desc varchar(5), @CT9Desc varchar(5)
select @CT1Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=1
select @CT2Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=2
select @CT3Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=3
select @CT4Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=4
select @CT5Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=5
select @CT6Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=6
select @CT7Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=7
select @CT8Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=99
select @CT9Desc=JCCT.Abbreviation
from HQCO
join JCCT WITH (NOLOCK) on HQCO.PhaseGroup=JCCT.PhaseGroup
Where [email protected] and JCCT.CostType=9
set nocount off;
With Contracts (JCCo, Contract)
as (select JCCo,
Contract
From JCCM
Where JCCo = @JCCo
and (
------------------------
/*Open*/
case @Status
when 'O' then ContractStatus
end = 1
or /*Open*/
case @Status
when 'O' then ContractStatus
end = 2
and MonthClosed > @ThroughMth
or /*Open*/
case @Status
when 'O' then ContractStatus
end = 3
and MonthClosed > @ThroughMth
------------------------
or /*Soft-Closed/Open*/
case @Status
when 'S' then ContractStatus
end = 2
and MonthClosed <= @ThroughMth
or /*Soft-Closed/Open*/
case @Status
when 'S' then ContractStatus
end = 1
or /*Soft-Closed/Open*/
case @Status
when 'S' then ContractStatus
end = 2
and MonthClosed > @ThroughMth
or /*Soft-Closed/Open*/
case @Status
when 'S' then ContractStatus
end = 3
and MonthClosed > @ThroughMth
------------------------
or /*Hard-Closed*/
case @Status
when 'C' then ContractStatus
end = 3
and MonthClosed between @BegMthClosed and @EndMthClosed
------------------------
or
case @Status
when 'A' then ContractStatus
end <> 0)
) --End CTE
Select 'CT1Desc'[email protected], 'CT2Desc'[email protected], 'CT3Desc'[email protected],'CT4Desc'[email protected],
'CT5Desc'[email protected],'CT6Desc'= @CT6Desc, 'CT7Desc'[email protected], 'CT8Desc'[email protected],'CT9Desc'[email protected],
ProjectMngrID=JCJMPM.ProjectMgr, --3/11/16 CJO
PrjDept=JCCM.Department, ---3/11/16 CJO
PrjVP=JCCM.udProjectManager, --3/25/16 CJO
JCCM.JCCo, JCCM.Contract, ContDesc=JCCM.Description, JCCM.ContractStatus,
JCCI.Department, DeptDesc = JCDM.Description,
JCIP.BilledAmt,
JCIP.ReceivedAmt,ActualCost,ACost1,ACost2,ACost3,ACost4,ACost5,ACost6,ACost7,ACost8,ACost9,
ProjCloseDate=JCCM.ProjCloseDate,
StartMonth=JCCM.StartMonth,---7/5/02 AA
MonthClosed=JCCM.MonthClosed,---7/5/02 AA
ContractDays=JCCM.CurrentDays,
JobItem=JCJMPM.Job,
CoName=HQCO.Name,
[email protected],
[email protected],
[email protected],
[email protected]/*, JCCM.Notes*/
FROM JCCI WITH (NOLOCK)
JOIN JCCM WITH (NOLOCK) on JCCM.JCCo=JCCI.JCCo AND JCCM.Contract=JCCI.Contract
JOIN Contracts on JCCM.JCCo=Contracts.JCCo AND JCCM.Contract = Contracts.Contract /**CTE with Contracts filtered by Status**/
JOIN JCDM WITH (NoLock) on JCDM.JCCo=JCCI.JCCo AND JCDM.Department = JCCI.Department
JOIN HQCO WITH (NOLOCK) on HQCO.HQCo=JCCI.JCCo
--- Project Management ID Info
LEFT JOIN JCJMPM WITH (NOLOCK) ON JCCI.BillGroup =JCJMPM.Job AND JCCI.JCCo = JCJMPM.JCCo --Pull project ID info into report CJO.
--- Revenue
left join (select JCCo, Contract, Item, BilledAmt=sum(JCIP.BilledAmt),ReceivedAmt=sum(JCIP.ReceivedAmt)
from JCIP
where JCIP.Mth>[email protected] and JCIP.Mth<[email protected]
and (JCIP.ContractAmt<>0 or JCIP.BilledAmt<>0 or JCIP.ReceivedAmt<>0)
group by JCCo, Contract, Item)
as JCIP on JCIP.JCCo=JCCI.JCCo and JCIP.Contract=JCCI.Contract and JCIP.Item=JCCI.Item
-- Cost
left join (select JCJP.JCCo, JCJP.Contract, JCJP.Item,
ACost1=sum(case when CostType=1 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ACost2=sum(case when CostType=2 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ACost3=sum(case when CostType=3 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ACost4=sum(case when CostType=4 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ACost5=sum(case when CostType=5 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ACost6=sum(case when CostType=6 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ACost7=sum(case when CostType=7 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ACost8=sum(case when CostType=99 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ACost9=sum(case when (CostType>7 and CostType <99) and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end),
ActualCost=sum(case when JCCP.Mth>[email protected] then ActualCost else 0 end) - sum(case when CostType=99 and JCCP.Mth>[email protected] then JCCP.ActualCost else 0 end)
from JCCP
join JCJP WITH (NOLOCK) on JCJP.JCCo=JCCP.JCCo and JCJP.Job=JCCP.Job and JCJP.PhaseGroup=JCCP.PhaseGroup
and JCJP.Phase=JCCP.Phase
where JCCP.Mth>[email protected] and JCCP.Mth<[email protected]
and [email protected] and JCJP.Contract>[email protected] and JCJP.Contract<[email protected]
group by JCJP.JCCo, JCJP.Contract, JCJP.Item)
as JCCP on JCCP.JCCo=JCCI.JCCo and JCCP.Contract=JCCI.Contract and JCCP.Item=JCCI.Item
where [email protected] and JCCI.Contract>[email protected] and JCCI.Contract<[email protected]
and [email protected]
case where @OHDept="15" then "16 else 0
order by JCCI.JCCo, JCCI.Department, JCCI.Contract
我没有看到您的发布代码在哪里@OHDept
被赋予任何值。
无论它被分配一个值,你可以按照这种说法像这样的东西:
set @OHDept = case when @OHDept = '15' then '16' else @OHDept end;
对我有帮助!!谢谢!!!!!! – omchantichanti
@omchantichanti快乐的帮助! – SqlZim
你似乎是试图以类似于你在与if
做什么方式使用case
程序语言。在SQL中,case
是在流程中评估为值而不是分支的表达式。
,其将15
到16
留下其他不变的值的表达式是:
case where @OHDept = '15' then '16' else @OhDept end
听起来好像是表达select
子句中属于既然你提到显示它。
我也认为布尔逻辑正在让你失望。通过这种方式,您可以轻松地简化查询顶部的大量内容。由于and
的优先级高于or
,所以您不需要使用括号。
(
@Status in ('O', 'S') and ContractStatus in (1, 2, 3))
and MonthClosed > @ThroughMth
)
or
(
@Status = 'C' and ContractStatus = 3
and MonthClosed between @BegMthClosed and @EndMthClosed
)
or
(
@Status = 'A' and ContractStatus <> 0
)
谢谢你,这是非常有帮助的!由于这是我试图更新的旧代码,我只关注我添加到它的内容,但你完全正确,所有的布尔逻辑已经完全让我落在了我的脸上,我很欣赏它! – omchantichanti
我想这是基于评论的日期一些古老的方法,但你还是应该考虑一下:[坏习惯:把NOLOCK到处 - 阿龙贝特朗(https://blogs.sentryone.com/ aaronbertrand/bad-habits-nolock-everywhere /) – SqlZim
你想做什么?你想用'@ OHDept'过滤'JCCI.Department'吗?或者你是否试图根据“JCCI.Department”设置“@ OHDept”? – fqhv
是的,这是一个非常旧的程序,我现在正在努力工作 - 感谢NOLOCK提示,我很感激! – omchantichanti