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

Question

0.00/5 (No votes)

See more:

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
);

Posted 8-Jul-18 18:55pm

Darsh12345

Updated 8-Jul-18 19:34pm

Add a Solution

Comments

Bryian Tan 9-Jul-18 1:36am

look fine beside the group by ... having .. clause in wrong order.

Darsh12345 9-Jul-18 2:06am

still showing error

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohibur Rashid · Answer 1 · 2018-07-08T19:34:00

There are many issues with your query.
I will explain some and this may improve your query

SQL

    -- 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.