Search code examples
sqloracledate-arithmetic

I would like to make a oracle query but I'm really not familiar with it, SQL error message[Err] ORA-00933: SQL command not properly ended

I have a table customerordercapture and a column updatedate. I want to select entities which are monitored by customer in customerordercapture table during previous day (till midnight), that is last updatedate whole days data and I want one more query where I need data of and before second last updatedate. The query is fired from a script, so hardcoded dates won't work.

i think my queries are wrong.

SELECT distinct UPDATEDATE 
FROM customerordercapture 
GROUP BY UPDATEDATE 
HAVING MAX(UPDATEDATE) < = SYSDATE-2 
and UPDATEDATE >= MIN(UPDATEDATE) 
ORDER BY UPDATEDATE asc ;

SELECT distinct UPDATEDATE 
FROM customerordercapture
GROUP BY UPDATEDATE 
HAVING UPDATEDATE <= TRUNC(MAX(UPDATEDATE)) - INTERVAL '3' DAY 
and UPDATEDATE =TRUNC( MAX(UPDATEDATE) )
ORDER BY UPDATEDATE asc ;
Solution

  • If I right understand, maybe this help you:

    SELECT MAX(UPDATEDATE), some_column
FROM customerordercapture 
WHERE UPDATEDATE <= sysdate 
GROUP BY some_column
ORDER BY MAX(UPDATEDATE) asc;