--如果是1月,则上个月为上年的12月,否则为同年的上个月
IF FLAG_JAN THEN
V_W11_01_01 := V_YEAR - 1;
V_W11_01_02 := 12;
ELSE
V_W11_01_01 := V_YEAR;
V_W11_01_02 := V_MON - 1;
END IF;
--补充上个月有而这个月没有的维度
IF FLAG_JAN THEN
INSERT INTO K21_01_00_TEMP
(W11_01_01, -- NUMBER(4) Y 年度
W11_01_02, -- NUMBER(2) Y 月份
W11_02_01, -- VARCHAR2(6) Y 省级行政区划
W11_02_02, -- VARCHAR2(6) Y 市级行政区划
W11_02_03 -- VARCHAR2(6) Y 县级行政区划
)
SELECT V_YEAR,
V_MON,
T1.W11_02_01,
T1.W11_02_02,
T1.W11_02_03
FROM K21_01_00 T1
WHERE T1.W11_02_03 LIKE V_V_AAB301 --加上行政区划判断
AND T1.W11_01_01 = V_W11_01_01
AND T1.W11_01_02 = V_W11_01_02
AND NOT EXISTS
(SELECT 1
FROM K21_01_00_TEMP T
WHERE T.W11_02_01 = T1.W11_02_01
AND T.W11_02_02 = T1.W11_02_02
AND T.W11_02_03 = T1.W11_02_03
)
AND (T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR
T1.Z21_007 <> 0 OR T1.Z21_011 <> 0);
COMMIT;
ELSE
INSERT INTO K21_01_00_TEMP
(W11_01_01, -- NUMBER(4) Y 年度
W11_01_02, -- NUMBER(2) Y 月份
W11_02_01, -- VARCHAR2(6) Y 省级行政区划
W11_02_02, -- VARCHAR2(6) Y 市级行政区划
W11_02_03 -- VARCHAR2(6) Y 县级行政区划
SELECT V_YEAR,
V_MON,
T1.W11_02_01,
T1.W11_02_02,
T1.W11_02_03
FROM K21_01_00 T1
WHERE T1.W11_02_03 LIKE V_V_AAB301 --加上行政区划判断
AND T1.W11_01_01 = V_W11_01_01
AND T1.W11_01_02 = V_W11_01_02
AND NOT EXISTS
(SELECT 1
FROM K21_01_00_TEMP T
WHERE T.W11_02_01 = T1.W11_02_01
AND T.W11_02_02 = T1.W11_02_02
AND T.W11_02_03 = T1.W11_02_03
)
AND (T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR
T1.Z21_007 <> 0 OR T1.Z21_011 <> 0 OR T1.Z21_001 <> 0 OR
T1.Z21_005 <> 0);
COMMIT;
END IF;
以上else中的代码和if里面的基本是一样的,就是后面的条件多了'OR T1.Z21_001 <> 0 OR
T1.Z21_005 <> 0'这部分,也就是说如果当前月不是一月的话就要加'OR T1.Z21_001 <> 0 OR
T1.Z21_005 <> 0'这个条件,否则就不要加.过程中有个变量表示月份,v_mon,我就希望有类似'case when v_mon>1 then OR T1.Z21_001 <> 0 OR T1.Z21_005 <> 0' else 1=1但是这样写有问题.我又不想把它拼成sql字符串,然后再用EXECUTE IMMEDIATE来执行sql.希望高手指点!谢谢!只要能成,马上结贴.
if v_mon=1 then
select V_YEAR,
V_MON,
T1.W11_02_01,
T1.W11_02_02,
T1.W11_02_03
from K21_01_00 T1
where (T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR
T1.Z21_007 <> 0 OR T1.Z21_011 <> 0)
else
select V_YEAR,
V_MON,
T1.W11_02_01,
T1.W11_02_02,
T1.W11_02_03
from K21_01_00 T1
where (T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR
T1.Z21_007 <> 0 OR T1.Z21_011 <> 0 OR T1.Z21_001 <> 0 OR
T1.Z21_005 <> 0)
end if;
希望这个select语句能合为一句,不要用if .......else.只需在where 条件中红色后,加上类型似'case when v_mon>1 then OR T1.Z21_001 <> 0 OR T1.Z21_005 <> 0' else 1=1'这样的条件,不知能否实现??
V_MON,
T1.W11_02_01,
T1.W11_02_02,
T1.W11_02_03
from K21_01_00 T1
where (v_mon=1) AND (T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR T1.Z21_007 <> 0 OR T1.Z21_011 <> 0)
OR
(
(T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR
T1.Z21_007 <> 0 OR T1.Z21_011 <> 0 OR T1.Z21_001 <> 0 OR
T1.Z21_005 <> 0)
)
要么用EXECUTE IMMEDIATE来做:
(T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR
T1.Z21_007 <> 0 OR T1.Z21_011 <> 0 OR T1.Z21_001 <> 0 OR
T1.Z21_005 <> 0)
),你这样想,看起来感觉不行.我试下.
这样做:
在if else里判断不同月份,定义个变量v_sql赋予不同的值,
然后后面执行SQL时只需要一个语句'EXECUTE IMMEDIATE v_sql;'即可,这样代码就不就短了吗.
select V_YEAR,
V_MON,
T1.W11_02_01,
T1.W11_02_02,
T1.W11_02_03
from K21_01_00 T1
where (v_mon=1) AND (T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR T1.Z21_007 <> 0 OR T1.Z21_011 <> 0)
OR
(
(T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 ....)
)这个不是跟你的sql的逻辑一样吗? 还是这样写还是复杂?
T1.Z21_005 <> 0'这部分是否要放在where条件里。
V_MON,
T1.W11_02_01,
T1.W11_02_02,
T1.W11_02_03
from K21_01_00 T1
where (v_mon=1) AND (T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 OR T1.Z21_007 <> 0 OR T1.Z21_011 <> 0)
OR
((v_mon <> 1) AND
(
(T1.Z21_002 <> 0 OR T1.Z21_003 <> 0 OR T1.Z21_006 <> 0 ....)
)
)这个逻辑就对了。 原以为oracle会做短路值求值的,刚才测试发现他不