我修改了一个原本是正确的select语句,在中间加入了一个自定义的列,该列的值是通过判断另一个列的值来获得值的,
sql语句如下:
... ...
(CASE WHEN (days('2008-03-20')-days(AUDIT_DATE))<=coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,0) THEN 0 ELSE AR_DEBT_HEADERS.TC_TI-AR_DEBT_HEADERS.MATCHING_TC_TI END) TIMEOUT_TC_TI ,
(CASE
WHEN (days('2008-03-20')-days(AUDIT_DATE))<=coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,
0) THEN 0
ELSE (days('2008-03-20')- days(AUDIT_DATE)) - coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,0) END)
TIMEOUT_days ,
-------这里是我增加的自定义的列（开始）-----------
(case
when TIMEOUT_days>1 and TIMEOUT_days<10 then 1
when TIMEOUT_days>11 and timeout_days<30 then 2
when TIMEOUT_days>31 and TIMEOUT_days<90 then 3
when TIMEOUT_days>91 and TIMEOUT_days<180 then 4
else 5
end )
as TIMEOUT_DAYS_TYPE
-------这里是我增加的自定义的列（完）-----------
FROM
... ...
出现的问题是
"TIMEOUT_DAYS" 在使用它的上下文中无效。 SQLSTATE=42703
请问我要如何插入这个列呢？
------解决方案--------------------------------------------------------
我使用sqlserver，不知这句话能不能给你帮助
use pubs
go
select case when job='1' then 1 else 2 end job from (select j.*,'' job from jobs j) b
------解决方案--------------------------------------------------------
肯定是无效的三
------解决方案--------------------------------------------------------
你可以这样
select *,(case
when TIMEOUT_days>1 and TIMEOUT_days <10 then 1
when TIMEOUT_days>11 and timeout_days <30 then 2
when TIMEOUT_days>31 and TIMEOUT_days <90 then 3
when TIMEOUT_days>91 and TIMEOUT_days <180 then 4
else 5
end )
as TIMEOUT_DAYS_TYPE
from (
(CASE WHEN (days('2008-03-20')-days(AUDIT_DATE)) <=coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,0) THEN 0 ELSE AR_DEBT_HEADERS.TC_TI-AR_DEBT_HEADERS.MATCHING_TC_TI END) TIMEOUT_TC_TI ,
(CASE
WHEN (days('2008-03-20')-days(AUDIT_DATE)) <=coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,
0) THEN 0
ELSE (days('2008-03-20')- days(AUDIT_DATE)) - coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,0) END)
TIMEOUT_days
from ...) as temp
------解决方案--------------------------------------------------------
先用一个临时表，把上个查询结果列出了。要不然你不怕复杂的话再把TIMEOUT_days写一次。建议如上楼写法，或者
with t1 as
(select 1,2,CASE WHEN (days('2008-03-20')-days(AUDIT_DATE)) <=coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,0) THEN 0 ELSE AR_DEBT_HEADERS.TC_TI-AR_DEBT_HEADERS.MATCHING_TC_TI END) TIMEOUT_TC_TI ,
(CASE
WHEN (days('2008-03-20')-days(AUDIT_DATE)) <=coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,
0) THEN 0
ELSE (days('2008-03-20')- days(AUDIT_DATE)) - coalesce(SO_ORG_CUSTOMER_SALESPERSON.Payment_condition_id ,0) END)
TIMEOUT_days
from ...)
select t1.* ,(case
when TIMEOUT_days>1 and TIMEOUT_days <10 then 1
when TIMEOUT_days>11 and timeout_days <30 then 2
when TIMEOUT_days>31 and TIMEOUT_days <90 then 3
when TIMEOUT_days>91 and TIMEOUT_days <180 then 4
else 5
end )
as TIMEOUT_DAYS_TYPE
from t1