Reservoir and river warning SQL statement for water conservancy and Flood Control

SQL Server version:

Create view v_hd_warninginfoasselect T. zdt8, T. tmnow, T. stcd, T. stnm, T. stlc, T. lgtd, T. lttd, T. showlevel, T. wrz, T. grz, TT. STTP, TT. rvnm, T. msgfrom (select t1.stcd, t1.stnm, t1.lgtd, t1.lttd, t1.stlc, t1.zdt8, t1.tmnow, t1.showlevel, t2.wrz, t2.grz, case when wrz-zdt8 = 0 then' alert 'when zdt8 between wrz and grz then' alert: '+ Cast (zdt8-wrz) as varchar (10 )) when zdt8> grz then 'super guarantee:' + Cast (zdt8-grz) as varchar (10) else 'normal' end as MSG from (select stcd, stnm, lgtd, lttd, stlc, Z as zdt8, tmnow, showlevel from st_river_d) T1 left join (select stcd, isnull (wrz, 0) as wrz, isnull (grz, 0) as grz from nation) T2 on t1.stcd = t2.stcd Union all select t1.stcd, t1.stnm, t1.lgtd, t1.lttd, t1.stlc, region, t1.tmnow, t1.showlevel, t2.wrz, t2.grz, case when wrz-zdt8 = 0 then 'limit reached 'when zdt8> wrz then' limit:' + Cast (zdt8-wrz) as varchar (10 )) else 'normal' end as MSG from (select stcd, stnm, lgtd, lttd, stlc, Rz as zdt8, tmnow, showlevel from st_rsvr_d) T1 left join (select stcd, bgmd, edmd, isnull (fsltdz, 0) as wrz, 0 as grz from st_rsvrfsr_ B) T2 on t1.stcd = t2.stcd and t1.tmnow between convert (varchar, year (getdate (), 4) + '-' + substring (t2.bgmd, 1, 2) + '-' + substring (t2.bgmd, 3, 2) and convert (varchar, year (getdate (), 4) + '-' + substring (t2.edmd, 1, 2) + '-' + substring (t2.edmd, 3, 2) as t inner join (select stcd, STTP, rvnm from DBO. st_stbprp_ B where (STTP = 'rr ') as TT ON T. stcd = TT. stcdwhere (T. MSG <> 'normal') and T. tmnow> = dateadd ([Day],-3, getdate (); then create view v_sk_warninginfoasselect T. zdt8, T. tmnow, T. stcd, T. stnm, T. stlc, T. lgtd, T. lttd, T. showlevel, T. wrz, T. grz, TT. STTP, TT. rvnm, T. msgfrom (select t1.stcd, t1.stnm, t1.lgtd, t1.lttd, t1.stlc, t1.zdt8, t1.tmnow, t1.showlevel, t2.wrz, t2.grz, case when wrz-zdt8 = 0 then' alert 'when zdt8 between wrz and grz then' alert: '+ Cast (zdt8-wrz) as varchar (10 )) when zdt8> grz then 'super guarantee:' + Cast (zdt8-grz) as varchar (10) else 'normal' end as MSG from (select stcd, stnm, lgtd, lttd, stlc, Z as zdt8, tmnow, showlevel from st_river_d) T1 left join (select stcd, isnull (wrz, 0) as wrz, isnull (grz, 0) as grz from nation) T2 on t1.stcd = t2.stcd Union all select t1.stcd, t1.stnm, t1.lgtd, t1.lttd, t1.stlc, region, t1.tmnow, t1.showlevel, t2.wrz, t2.grz, case when wrz-zdt8 = 0 then 'limit reached 'when zdt8> wrz then' limit:' + Cast (zdt8-wrz) as varchar (10 )) else 'normal' end as MSG from (select stcd, stnm, lgtd, lttd, stlc, Rz as zdt8, tmnow, showlevel from st_rsvr_d) T1 left join (select stcd, bgmd, edmd, isnull (fsltdz, 0) as wrz, 0 as grz from st_rsvrfsr_ B) T2 on t1.stcd = t2.stcd and t1.tmnow between convert (varchar, year (getdate (), 4) + '-' + substring (t2.bgmd, 1, 2) + '-' + substring (t2.bgmd, 3, 2) and convert (varchar, year (getdate (), 4) + '-' + substring (t2.edmd, 1, 2) + '-' + substring (t2.edmd, 3, 2) as t inner join (select stcd, STTP, rvnm from DBO. st_stbprp_ B where (STTP = 'zz ') as TT ON T. stcd = TT. stcdwhere (T. MSG <> 'normal') and T. tmnow> = dateadd ([Day],-3, getdate ());

Oracle version:

-- Create view v_sk_warninginfoasselect T. zdt8 as S, T. tmnow, T. stcd, T. stnm, T. stlc, T. lgtd, T. lttd, T. showlevel, T. wrz, T. grz, TT. STTP, TT. rvnm, T. msgfrom (select t1.stcd, t1.stnm, t1.lgtd, t1.lttd, t1.stlc, t1.zdt8, t1.tmnow, t1.showlevel, t2.wrz, t2.grz, case when wrz-zdt8 = 0 then' alert 'when zdt8 between wrz and grz then' alert: '| cast (nvl (zdt8, 0)-nvl (wrz, 0) as varchar2 (10) When ZD T8> grz then 'super guarantee:' | cast (nvl (zdt8, 0)-nvl (grz, 0) as varchar2 (10 )) else 'normal' end as MSG from (select stcd, stnm, lgtd, lttd, stlc, Z as zdt8, tmnow, showlevel from st_river_d) T1 left join (select stcd, nvl (wrz, 0) as wrz, nvl (grz, 0) as grz from nation) T2 on t1.stcd = t2.stcd Union all select t1.stcd, t1.stnm, t1.lgtd, t1.lttd, t1.stlc, Beijing, t1.tmnow, t1.showlevel, t2.wrz, T2. Grz, case when wrz-zdt8 = 0 then 'throttling' When zdt8> wrz then ':' | cast (zdt8-wrz) as varchar2 (10) else 'normal' end as MSG from (select stcd, stnm, lgtd, lttd, stlc, Rz as zdt8, tmnow, showlevel from st_rsvr_d) t1 left join (select stcd, bgmd, edmd, nvl (fsltdz, 0) as wrz, 0 as grz from st_rsvrfsr_ B) T2 on t1.stcd = t2.stcd and t1.tmnow between (select to_char (sysdate, 'yyyy') from dual) | '/' | Substr (t2.bgmd, 1, 2) | '/' | substr (t2.bgmd, 3, 2) and (select to_char (sysdate, 'yyyy ') from dual) | '/' | substr (t2.edmd, 1, 2) | '/' | substr (t2.edmd, 3, 2 )) t inner join (select stcd, STTP, rvnm from st_stbprp_ B where STTP = 'rr ') tton T. stcd = TT. stcdwhere T. MSG! = 'Normal' and T. tmnow> = (select sysdate-1 from dual); -- river warning create view v_hd_warninginfoasselect T. zdt8 as S, T. tmnow, T. stcd, T. stnm, T. stlc, T. lgtd, T. lttd, T. showlevel, T. wrz, T. grz, TT. STTP, TT. rvnm, T. MSG from (select t1.stcd, t1.stnm, t1.lgtd, t1.lttd, t1.stlc, t1.zdt8, t1.tmnow, t1.showlevel, t2.wrz, t2.grz, case when wrz-zdt8 = 0 then' alert 'when zdt8 between wrz and grz then' alert: '| cast (Nvl (zdt8, 0)-nvl (wrz, 0) as varchar2 (10) When zdt8> grz then 'superguarantee:' | cast (nvl (zdt8, 0)-nvl (grz, 0) as varchar2 (10) else 'normal' end as MSG from (select stcd, stnm, lgtd, lttd, stlc, Z as zdt8, tmnow, showlevel from st_river_d) T1 left join (select stcd, nvl (wrz, 0) as wrz, nvl (grz, 0) as grz from st_rvfcch_ B) t2 on t1.stcd = t2.stcd Union all select t1.stcd, t1.stnm, t1.lgtd, t1.lttd, T 1. stlc, t1.zdt8, t1.tmnow, t1.showlevel, t2.wrz, t2.grz, case when wrz-zdt8 = 0 then 'exceeds the limit of 'when zdt8> wrz then: '| cast (zdt8-wrz) as varchar (10) else 'normal' end as MSG from (select stcd, stnm, lgtd, lttd, stlc, Rz as zdt8, tmnow, showlevel from st_rsvr_d) T1 left join (select stcd, bgmd, edmd, nvl (fsltdz, 0) as wrz, 0 as grz from nation) T2 on t1.stcd = t2.stcd and t1.tmnow be Tween (select to_char (sysdate, 'yyyy') from dual) | '/' | substr (t2.bgmd, 1, 2) | '/' | substr (t2.bgmd, 3, 2) and (select to_char (sysdate, 'yyyy') from dual) | '/' | substr (t2.edmd, 1, 2) | '/' | substr (t2.edmd, 3, 2) T inner join (select stcd, STTP, rvnm from st_stbprp_ B where STTP = 'zz ') TT ON T. stcd = TT. stcdwhere T. MSG! = 'Normal' and T. tmnow >=( select sysdate-1 from dual );