If case equals something then subtract date-CodePudding

Trying to write a query that checks if a column equals something. If so, take the value of a date column then subtract by a certain value.

The query looks like this:

select
  v.voyage "Voyage"
 ,v.service "Service"
 ,to_char(vp.eta_date, 'MONTH dd, yyyy') "ETA"
 ,case 
    when v.service = "USA" then to_char(vp.eta_date, 'MONTH dd, yyyy') - 2
    else 'n/a'
    end as 'Notice'
from 
  table
// bunch of joins

When I run the above, I get an error that reads:

FROM keyword not found where expected

The error is pointing to the word 'Notice'.

I basically want to check if service is equal to 'USA', and if so, use the eta_date subtracted by 2 to give me the date in the Notice column.

If not, then just so 'N/A' in the Notice column.

What am I doing wrong and how do I fix it?

CodePudding user response：

There are a couple of syntax errors and quote mixups that make this confusing. These are the rules for quotes (relevant for your code)

Use single quotes for strings
Use double quotes for column aliases

And then there is this part: to_char(vp.eta_date, 'MONTH dd, yyyy') - 2 which tells the sql engine to substract 2 from a string of format "MONTH dd, yyyy". You probably meant substract 2 days from the date (vp.eta_date) and then format it as "MONTH dd, yyyy" which can be written as to_char(vp.eta_date - 2, 'MONTH dd, yyyy')

Putting it all together gives

select
  v.voyage "Voyage"
 ,v.service "Service"
 ,to_char(vp.eta_date, 'MONTH dd, yyyy') "ETA"
 ,case 
    when v.service = "USA" then to_char(vp.eta_date - 2, 'MONTH dd, yyyy')
    else 'n/a'
    end as "Notice"
from 
  table