本文介绍了str_to_date附近的语法erroe(date_format(sysdate(),'%​​d-%b-%Y'),'%d-%b-%Y') - max(ren_date)> = 7的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

str_to_date附近的语法错误(date_format(sysdate(),'%​​d-%b-%Y'),'%d-%b-%Y') - max(ren_date)> = 7





什么是正确的语法?



我的尝试:



创建或替换视图HWP_LASTRENEWED(REGNO,WPNO,PERMITSTATUS,PROCESSSTATUS,LASTRENDATE,DAYS,HTYPE,SRNO)作为


选择regno,wpno,permitstatus,processstatus,max(ren_date)作为lastrendate,

str_to_date(date_format(sysdate(),'%​​d-%b-%Y '),'%d-%b-%Y') - max(ren_date)天,'renw'为htype,'0'为srno

来自hwp_renewal

allowstatus ='RENEWED'

和str_to_date(date_format(sysdate(),'%​​d-%b-%Y'),'%d-%b-%Y') - max(ren_date )> = 7

分组由wpno,permitstatus,regno,processstatus

union

选择reg_no为regno,wp_no为wpno,permitstatus, processstatus,permitdate as lastrendate,

(str_to_date(date_format(sysdate() ,'%d-%b-%Y'),'%d-%b-%Y') - permitdate)as天,'req'为htype,srno

来自hwp_hotwork,其中wp_no是null和reg_no为null和

((permitstatus ='REQUESTED'和processstatus ='REQ')或(permitstatus ='APPROVED'和processstatus ='APPR'))

和str_to_date(date_format(sysdate(),'%​​d-%b-%Y'),'%d-%b-%Y') - permitdate> = 7

);

syntax error near str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y')-max(ren_date)>=7


what is correct syntax?

What I have tried:

CREATE OR REPLACE VIEW HWP_LASTRENEWED (REGNO, WPNO, PERMITSTATUS, PROCESSSTATUS, LASTRENDATE, DAYS, HTYPE, SRNO) AS
(
select regno, wpno, permitstatus, processstatus, max(ren_date) as lastrendate,
str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y')-max(ren_date) days , 'renw' as htype, '0' as srno
from hwp_renewal
having permitstatus='RENEWED'
and str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y')-max(ren_date)>=7
group by wpno, permitstatus, regno, processstatus
union
select reg_no as regno, wp_no as wpno, permitstatus, processstatus, permitdate as lastrendate,
(str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y')-permitdate) as days, 'req' as htype, srno
from hwp_hotwork where wp_no is null and reg_no is null and
( (permitstatus='REQUESTED' and processstatus='REQ') or (permitstatus='APPROVED' and processstatus='APPR') )
and str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y')-permitdate >=7
);

推荐答案

    -- What do you expect from this statement? I am not sure. Are you subtracting two date with max date?
  str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y') - max(ren_date) days
-- few issue I will address from this statement
-- You are generating todays date by 
  SELECT str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y');
--  IMPROVED version
  SELECT DATE(NOW());
-- Second issue subtraction:
-- you are running something like this
  SELECT "2018-07-09"-"2018-07-02";
-- Improved version 
  SELECT DATEDIFF("2018-07-09", "2018-07-02");
-- next issue max command, looks okay but I will still suggest to split up the query
-- examle
   SELECT
      a
    , b
    , '0'  as zero
    , 'd'  as d
   FROM (
     SELECT
         aa as a
       , MAX(bb) as b
     FROM
      table_tbl
     GROUP BY a
     HAVING b > 7

   )







如果您有任何其他问题,请告诉我。




If you have any further question, please let me know.


这篇关于str_to_date附近的语法erroe(date_format(sysdate(),'%​​d-%b-%Y'),'%d-%b-%Y') - max(ren_date)> = 7的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-15 07:43