6 Replies Latest reply on Mar 16, 2010 11:12 PM by Lukasz Mastalerz

    ora-00905 missing keyword error

    702665
      Hi ,
      Whats wrong with the below query?
      Pls help
      select state_id ,
             TO_CHAR(state_eff_date, 'dd-mon-yyyy hh24:mi:ss am'),
           case
                when station_id = null then state_id = lag(state_id) over (order by ash.station_id)
           end "previous_state"     
         from vppstation.avi_state_history 
      where state_eff_date >= to_date('01/02/2010', 'dd/mm/yyyy') 
          and state_eff_date <= to_date('16/03/2010', 'dd/mm/yyyy') 
          and station_id = 61 ;
      Thanks.
        • 1. Re: ora-00905 missing keyword error
          Lukasz Mastalerz
          case
          when station_id = null then state_id = lag(state_id) over (order by ash.station_id)
          end "previous_state"
          You need to return value from case, while you are just checking condition.
          • 2. Re: ora-00905 missing keyword error
            702665
            Hi ,
            My intension is , for the given station , if there is no any rows return in the table for the date in the where clause then it should return the row for the last date .

            For example , If there is no data from Mar 01 to Mar 16 it does't return any rows. But there is row for the given station before mar 01.
            How could I get those rows ?
            • 3. Re: ora-00905 missing keyword error
              Lukasz Mastalerz
              Inhdu,

              I'm sorry but I don't understand. Could you paste sample data and results?

              Lukasz
              • 4. Re: ora-00905 missing keyword error
                702665
                Ex:

                In the table there is data for the given station for Feb 4th 2010.
                I have to retrive the data from Mar 01 to Mar 16 2010.
                Because there is no any data for the given station for the given date , it does not return any row for the given station. If there is any rows for the given date then it returns hard coded 0. I dont want to return 0./ Instead it should return the previous entry (row actually in my ex I need to return feb 4th data)for the given station.
                In that situation , it should get the data from table actually it has for Feb 4th 2010.
                How could I get such data?

                Edited by: Indhu Ram on Mar 16, 2010 3:50 PM
                • 5. Re: ora-00905 missing keyword error
                  470572
                  server_id = null ????
                  Is always FALSE
                  Do you mean : "server_id IS NULL"?

                  Rachid
                  • 6. Re: ora-00905 missing keyword error
                    Lukasz Mastalerz
                    Consider this example:
                      1* SELECT * FROM emp ORDER BY hiredate
                    SQL> /
                    
                         EMPNO ENAME      JOB            MGR HIREDATE         SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                          7369 SMITH      CLERK           7902 17-DEC-80         800              20
                          7499 ALLEN      SALESMAN           7698 20-FEB-81        1600        300        30
                          7521 WARD       SALESMAN           7698 22-FEB-81        1250        500        30
                          7566 JONES      MANAGER           7839 02-APR-81        2975              20
                          7698 BLAKE      MANAGER           7839 01-MAY-81        2850              30
                          7782 CLARK      MANAGER           7839 09-JUN-81        2450              10
                          7844 TURNER     SALESMAN           7698 08-SEP-81        1500       0        30
                          7654 MARTIN     SALESMAN           7698 28-SEP-81        1250       1400        30
                          7839 KING       PRESIDENT         17-NOV-81        5000              10
                          7900 JAMES      CLERK           7698 03-DEC-81         950              30
                          7902 FORD       ANALYST           7566 03-DEC-81        3000              20
                    
                         EMPNO ENAME      JOB            MGR HIREDATE         SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                          7934 MILLER     CLERK           7782 23-JAN-82        1300              10
                          7788 SCOTT      ANALYST           7566 19-APR-87        3000              20
                          7876 ADAMS      CLERK           7788 23-MAY-87        1100              20
                    
                    14 rows selected.
                    
                      1  WITH c_set AS
                      2   (SELECT deptno, hiredate FROM emp WHERE hiredate BETWEEN DATE '1981-03-01' AND DATE '1981-03-30' AND deptno = 30)
                      3  SELECT * FROM c_set
                      4  UNION ALL
                      5  SELECT deptno, MAX(hiredate)
                      6   FROM  emp
                      7   WHERE hiredate <= DATE '1981-03-30'
                      8   AND   deptno = 30
                      9   AND   0 = (SELECT COUNT(*) FROM c_set WHERE hiredate BETWEEN DATE '1981-03-01' AND DATE '1981-03-30' AND deptno = 30)
                     10*  GROUP BY deptno
                    SQL> /
                    
                        DEPTNO HIREDATE
                    ---------- ---------
                         30 22-FEB-81
                    
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  WITH c_set AS
                      2   (SELECT deptno, hiredate FROM emp WHERE hiredate BETWEEN DATE '1981-03-01' AND DATE '1981-05-30' AND deptno = 30)
                      3  SELECT * FROM c_set
                      4  UNION ALL
                      5  SELECT deptno, MAX(hiredate)
                      6   FROM  emp
                      7   WHERE hiredate <= DATE '1981-05-30'
                      8   AND   deptno = 30
                      9   AND   0 = (SELECT COUNT(*) FROM c_set WHERE hiredate BETWEEN DATE '1981-03-01' AND DATE '1981-05-30' AND deptno = 30)
                     10*  GROUP BY deptno
                    SQL> /
                    
                        DEPTNO HIREDATE
                    ---------- ---------
                         30 01-MAY-81