合併兩個table進行條件query
測試機能:
異常監控通知信件_
流程:
從資料庫撈取得本月與上月的指定條件資料欄位的所有資料,並分別依類型寄出兩封信件
資料庫類型:
oracle
目前可以順利撈取資料的sql:
select * from APP_FP_NOTIFY_201705
WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'Y' union select * from APP_FP_NOTIFY_201706 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'Y';
select * from APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'N' union select * from APP_FP_NOTIFY_201706 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'N';
select * from APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'N' AND MODIFY_DATE <= TO_DATE('2017/05/18 10:19:54','YYYY/MM/DD HH24:MI:SS');
實際運用的sql:
SELECT * FROM APP_FP_NOTIFY_201710
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N'
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 10:10:52','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE('2017/10/30 11:40:52','YYYY/MM/DD HH24:MI:SS')
UNION
SELECT * FROM APP_FP_NOTIFY_201708 WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 10:10:52','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017/10/30 11:40:52','YYYY/MM/DD HH24:MI:SS')
ORDER BY MODIFY_DATE DESC
-->出現錯誤訊息:ORA-00904: "MODIFY_DATE": 無效的 ID
調整為:
SELECT * FROM APP_FP_NOTIFY_201710
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N' AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 10:19:54','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE('2017/10/30 11:49:54','YYYY/MM/DD HH24:MI:SS')
UNION
SELECT * FROM APP_FP_NOTIFY_201709
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 10:19:54','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE('2017/10/30 11:49:54','YYYY/MM/DD HH24:MI:SS')
ORDER BY MODIFY_DATE DESC
可以正常查詢某特定時間以前:
select * from APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'N' AND MODIFY_DATE <= TO_DATE('2017/05/18 10:19:54','YYYY/MM/DD HH24:MI:SS');
實際運用的sql:
SELECT * FROM APP_FP_NOTIFY_201706 WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 11:46:38','YYYY/MM/DD HH24:MI:SS') UNION SELECT * FROM APP_FP_NOTIFY_201705 WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 11:46:38','YYYY/MM/DD HH24:MI:SS');
執行結果
start run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 13:30:14
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:00:14','YYYY/MM/DD HH24:MI:SS')
UNION
SELECT * FROM APP_FP_NOTIFY_201706
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:00:14','YYYY/MM/DD HH24:MI:SS')
已取得異常資料
KR0 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y'
WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N'
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 12:00:14','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017/10/30 13:30:14','YYYY/MM/DD HH24:MI:SS')
K42 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y'
WHERE MMK_ID IN ('K42','K4A','K4B') AND SEND_FLAG ='N'
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 12:00:14','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017/10/30 13:30:14','YYYY/MM/DD HH24:MI:SS')
KR0 total count : 0 & K42 total_count : 2
end run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 13:30:21
紅字部分需要調整!
調整後執行結果:
start run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 13:39:48
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:09:48','YYYY/MM/DD HH24:MI:SS') UNION
SELECT * FROM APP_FP_NOTIFY_201706
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:09:48','YYYY/MM/DD HH24:MI:SS')
已取得異常資料KR0 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y' WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:09:48','YYYY/MM/DD HH24:MI:SS')
K42 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y' WHERE MMK_ID IN ('K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:09:48','YYYY/MM/DD HH24:MI:SS')
KR0 total count : 1 & K42 total_count : 1
end run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 13:39:50
start run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 14:10:51
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS') UNION
SELECT * FROM APP_FP_NOTIFY_201706 WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')
已取得異常資料
KR0 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y' WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')UPDATE APP_FP_NOTIFY_201706 SET SEND_FLAG='Y' WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')
K42 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y' WHERE MMK_ID IN ('K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')UPDATE APP_FP_NOTIFY_201706 SET SEND_FLAG='Y' WHERE MMK_ID IN ('K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')
KR0 total count : 1 & K42 total_count : 1
end run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 14:10:54
異常監控通知信件_
流程:
從資料庫撈取得本月與上月的指定條件資料欄位的所有資料,並分別依類型寄出兩封信件
資料庫類型:
oracle
目前可以順利撈取資料的sql:
select * from APP_FP_NOTIFY_201705
WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'Y' union select * from APP_FP_NOTIFY_201706 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'Y';
select * from APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'N' union select * from APP_FP_NOTIFY_201706 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'N';
select * from APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'N' AND MODIFY_DATE <= TO_DATE('2017/05/18 10:19:54','YYYY/MM/DD HH24:MI:SS');
實際運用的sql:
SELECT * FROM APP_FP_NOTIFY_201710
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N'
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 10:10:52','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE('2017/10/30 11:40:52','YYYY/MM/DD HH24:MI:SS')
UNION
SELECT * FROM APP_FP_NOTIFY_201708 WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 10:10:52','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017/10/30 11:40:52','YYYY/MM/DD HH24:MI:SS')
ORDER BY MODIFY_DATE DESC
-->出現錯誤訊息:ORA-00904: "MODIFY_DATE": 無效的 ID
調整為:
SELECT * FROM APP_FP_NOTIFY_201710
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N' AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 10:19:54','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE('2017/10/30 11:49:54','YYYY/MM/DD HH24:MI:SS')
UNION
SELECT * FROM APP_FP_NOTIFY_201709
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 10:19:54','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE('2017/10/30 11:49:54','YYYY/MM/DD HH24:MI:SS')
ORDER BY MODIFY_DATE DESC
可以正常查詢某特定時間以前:
select * from APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE','KR3','K42','K4A','K4B') AND SEND_FLAG = 'N' AND MODIFY_DATE <= TO_DATE('2017/05/18 10:19:54','YYYY/MM/DD HH24:MI:SS');
實際運用的sql:
SELECT * FROM APP_FP_NOTIFY_201706 WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 11:46:38','YYYY/MM/DD HH24:MI:SS') UNION SELECT * FROM APP_FP_NOTIFY_201705 WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 11:46:38','YYYY/MM/DD HH24:MI:SS');
執行結果
start run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 13:30:14
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:00:14','YYYY/MM/DD HH24:MI:SS')
UNION
SELECT * FROM APP_FP_NOTIFY_201706
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:00:14','YYYY/MM/DD HH24:MI:SS')
已取得異常資料
KR0 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y'
WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N'
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 12:00:14','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017/10/30 13:30:14','YYYY/MM/DD HH24:MI:SS')
K42 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y'
WHERE MMK_ID IN ('K42','K4A','K4B') AND SEND_FLAG ='N'
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/30 12:00:14','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017/10/30 13:30:14','YYYY/MM/DD HH24:MI:SS')
KR0 total count : 0 & K42 total_count : 2
end run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 13:30:21
紅字部分需要調整!
調整後執行結果:
start run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 13:39:48
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:09:48','YYYY/MM/DD HH24:MI:SS') UNION
SELECT * FROM APP_FP_NOTIFY_201706
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:09:48','YYYY/MM/DD HH24:MI:SS')
已取得異常資料KR0 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y' WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:09:48','YYYY/MM/DD HH24:MI:SS')
K42 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y' WHERE MMK_ID IN ('K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:09:48','YYYY/MM/DD HH24:MI:SS')
KR0 total count : 1 & K42 total_count : 1
end run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 13:39:50
start run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 14:10:51
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS') UNION
SELECT * FROM APP_FP_NOTIFY_201706 WHERE MMK_ID IN ( 'KRE' ,'KR3','K42','K4A','K4B')
AND SEND_FLAG ='N'
AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')
已取得異常資料
KR0 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y' WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')UPDATE APP_FP_NOTIFY_201706 SET SEND_FLAG='Y' WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')
K42 mail successed!
UPDATE APP_FP_NOTIFY_201705 SET SEND_FLAG='Y' WHERE MMK_ID IN ('K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')UPDATE APP_FP_NOTIFY_201706 SET SEND_FLAG='Y' WHERE MMK_ID IN ('K42','K4A','K4B') AND SEND_FLAG ='N' AND MODIFY_DATE <= TO_DATE('2017/10/30 12:40:51','YYYY/MM/DD HH24:MI:SS')
KR0 total count : 1 & K42 total_count : 1
end run time(TICKET_ERROR_NOTIFY.php) : 2017-10-30 14:10:54