PLSQL Case in Where clause for >= (date)-CodePudding

I am using a table that contains various milestones to determine the stages of a contract. Each milestone has a date, and I need to build a report on a handful of those milestones to determine the average length of time it takes for a project to get from one stage to another. The table contains information spanning from 1996 onward, and I'm only interested in projects that have been finalized (milestone 4.08) in the past year.

The trouble is I can't seem to build a case statement that looks for a particular milestone, and then filters the date that corresponds to it. Below is the code I'm working on.

Select Distinct CONSTRUCTION.CONTRACTS.CONTRACTNUMBER,
                    CONSTRUCTION.TBL_CONTRACTPROJECTCOMBO.LOCATIONS,
                    Max(Case
                          When CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER =
                               '3.02' Then
                           CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE
                        End) RECEIVED_IN_AUDIT,
                    Max(Case
                          When CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER =
                               '4.04' Then
                           CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE
                        End) INITIAL_AUDIT_COMPLETE,
                    Max(Case
                          When CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER =
                               '4.08' Then
                           CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE
                        End) FINAL_AUDIT_COMPLETE,
                    (Max(Case
                           When CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER =
                                '4.08' Then
                            CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE
                         End) - (Max(Case
                                        When CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER =
                                             '3.02' Then
                                         CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE
                                      End))) DAYS_IN_AUDIT,
                    (Case
                      When Extract(Month From Max(Case
                                         When CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER =
                                              '4.08' Then
                                          CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE
                                       End)) >= 7 Then
                       Extract(Year From Max(Case
                                     When CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER =
                                          '4.08' Then
                                      CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE
                                   End))   1
                      Else
                       Extract(Year From Max(Case
                                     When CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER =
                                          '4.08' Then
                                      CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE
                                   End))
                    End) FY_AUDIT_COMPLETE
                    
      From CONSTRUCTION.CONTRACTS
      
      Join CONSTRUCTION.CONTRACTMILESTONES on CONSTRUCTION.CONTRACTMILESTONES.CONTRACTID = CONSTRUCTION.CONTRACTS.ID
      Join CONSTRUCTION.MILESTONEEVENTS on CONSTRUCTION.MILESTONEEVENTS.ID = CONSTRUCTION.CONTRACTMILESTONES.MILESTONEID
      Join CONSTRUCTION.TBL_CONTRACTPROJECTCOMBO on CONSTRUCTION.TBL_CONTRACTPROJECTCOMBO.CONTRACTNUMBER = CONSTRUCTION.CONTRACTS.CONTRACTNUMBER

      
     Where (CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER = '4.04' Or
           CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER = '4.08' Or
           CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER = '3.02')
       And (case when CONSTRUCTION.MILESTONEEVENTS.MILESTONENUMBER = '4.08' 
then CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE >= sysdate-365 END)
           
     Group By CONSTRUCTION.CONTRACTS.CONTRACTNUMBER,
              CONSTRUCTION.TBL_CONTRACTPROJECTCOMBO.LOCATIONS

The error I get is

ORA-00905: missing keyword.

The error indicates that my code breaks between the CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE and the ">=". What am I doing wrong?

Any help is appreciated. Thank you.

CodePudding user response：

Aliases exist for reason; I suggest you use them and make queries you write easier to read.

As of a problem you have: I believe this solves part of it:

SELECT DISTINCT c.contractnumber,
                m.milestone,
                m.milestonedescription,
                cm.milestonedate,
                m.milestonenumber
  FROM construction.contracts c
       JOIN construction.contractmilestones cm ON cm.contractid = c.id
       JOIN construction.milestoneevents m ON m.id = cm.milestoneid
 WHERE cm.milestonedate >=
       CASE WHEN m.milestonenumber = '4.08' THEN TRUNC (SYSDATE) - 365
            ELSE DATE '2021-01-01'   --> if it isn't 4.08, what should CM.MILESTONEDATE be larger than?
       END

CASE you wrote is wrong because it should represent some condition, while your query tries to use it, but in a wrong manner (you can't have THEN within the CASE).

Note ELSE I wrote; I used date literal (Jan 1st 2021) because I don't know what you want to do with other milestones (different from 4.08). If you do (you should, right?), put that date in there.

CodePudding user response：

I guess you have extra bracket here at end..

then CONSTRUCTION.CONTRACTMILESTONES.MILESTONEDATE >= to_date(to_char(sysdate, 'mm/dd/yyyy'), 'mm/dd/yyyy')-365) END)

Can you try removing and executing same query again.