select ID, [havechecked]=(case when charindex('Y',havechecked)>0 then 'Y' else 'N' end) from ( 你上面的语句 )T
SELECT ID,case when charindex('Y',((CASE WHEN ((SELECT COUNT(s_ct_id) FROM BKCHARGE WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR ((SELECT COUNT(s_ct_id) FROM BKCHARGE WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END)) + ((CASE WHEN ((SELECT COUNT(b_ct_id) FROM BKCHARGE WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR ((SELECT COUNT(s_ct_id) FROM BKCHARGE WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END)) + ((CASE WHEN ((SELECT COUNT(bk_no_id) FROM BKCHARGE WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR ((SELECT COUNT(bk_no_id) FROM BKCHARGE WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END)))>0 then 'Y' else 'N' end AS havechecked FROM acnote WHERE ac_no IN (SELECT DISTINCT ac_no FROM bkcharge ) ORDER BY ac_no
--写一个函数进行这种转化(输入参数为一个字符串变量,返回值为'Y'或'N') --然后在查询中调用这个函数。 --顺便说一句,你这段代码真的好长啊。 --你看看有没有办法优化一下吧。太长了,没心思看。 --调用示例如下, 将结果集用一个表别名命名,然后再查询 --你本身的代码有语法错误!你调用正确代码时,做相应的修改即可. select id, havechecked=dbo.fun(havechecked) from (SELECT ID,((CASE WHEN ((SELECT COUNT(s_ct_id) FROM BKCHARGE WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR ((SELECT COUNT(s_ct_id) FROM BKCHARGE WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END)) + ((CASE WHEN ((SELECT COUNT(b_ct_id) FROM BKCHARGE WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR ((SELECT COUNT(s_ct_id) FROM BKCHARGE WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END)) + ((CASE WHEN ((SELECT COUNT(bk_no_id) FROM BKCHARGE WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR ((SELECT COUNT(bk_no_id) FROM BKCHARGE WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END)) AS havechecked FROM acnote WHERE ac_no IN SELECT DISTINCT ac_no FROM bkcharge )t order by t.ac_no
[havechecked]=(case when charindex('Y',havechecked)>0 then 'Y' else 'N' end)
from
(
你上面的语句
)T
((SELECT COUNT(s_ct_id)
FROM BKCHARGE
WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND
BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR
((SELECT COUNT(s_ct_id)
FROM BKCHARGE
WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END))
+ ((CASE WHEN
((SELECT COUNT(b_ct_id)
FROM BKCHARGE
WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND
BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR
((SELECT COUNT(s_ct_id)
FROM BKCHARGE
WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END))
+ ((CASE WHEN
((SELECT COUNT(bk_no_id)
FROM BKCHARGE
WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND
BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR
((SELECT COUNT(bk_no_id)
FROM BKCHARGE
WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END)))>0 then 'Y' else 'N' end
AS havechecked
FROM acnote WHERE ac_no IN
(SELECT DISTINCT ac_no
FROM bkcharge )
ORDER BY ac_no
--然后在查询中调用这个函数。
--顺便说一句,你这段代码真的好长啊。
--你看看有没有办法优化一下吧。太长了,没心思看。
--调用示例如下, 将结果集用一个表别名命名,然后再查询
--你本身的代码有语法错误!你调用正确代码时,做相应的修改即可.
select id, havechecked=dbo.fun(havechecked)
from (SELECT ID,((CASE WHEN
((SELECT COUNT(s_ct_id)
FROM BKCHARGE
WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND
BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR
((SELECT COUNT(s_ct_id)
FROM BKCHARGE
WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END))
+ ((CASE WHEN
((SELECT COUNT(b_ct_id)
FROM BKCHARGE
WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND
BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR
((SELECT COUNT(s_ct_id)
FROM BKCHARGE
WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END))
+ ((CASE WHEN
((SELECT COUNT(bk_no_id)
FROM BKCHARGE
WHERE (round(ABS(PAY_AMOUNT - AMOUNT * dbcur_rate), 5) > 0.0051) AND
BKCHARGE.AC_NO = ACNOTE.AC_NO) <> 0) OR
((SELECT COUNT(bk_no_id)
FROM BKCHARGE
WHERE BKCHARGE.AC_NO = ACNOTE.AC_NO) = 0) THEN 'N' ELSE 'Y' END))
AS havechecked
FROM acnote WHERE ac_no IN
SELECT DISTINCT ac_no FROM bkcharge
)t
order by t.ac_no