Invalid Identifier error in Oracle.

Question

I am executing this query i SQL server and it is working fine but when I try to execute it in oracle, then it generates an error that invalid identifier "IS NULL". Can anyone translate this query in oracle supported query? Please help me. Thank you

       SELECT 
       RM_LIVE.EMPLOYEE.EMPNO, RM_LIVE.EMPNAME.FIRSTNAME,
       RM_LIVE.EMPNAME.LASTNAME, RM_LIVE.CRWBASE.BASE ,RM_LIVE.CRWCAT.crwcat AS "Rank",
       isnull(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN ('29','721') THEN '0.25' ELSE '1' END),'0'),'1') *
       isnull(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN ('921','301','30','722','601','581') THEN '0.50' ELSE '1' END),'0'),'1') *
       isnull(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN ('2','1', '4') THEN '0.70' ELSE '1' END),'0'),'1') *
       isnull(nullif(MIN(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC  IN ('1','2') then '0' else '1' END) * 
                     MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC  IN ('4') then '0.20' else '0' END),'0'),'1') *
       isnull(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN ('31','723') THEN '0.75' ELSE '1' END),'0'),'1') *
       isnull(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC = '861' THEN '0.80' ELSE '1' END),'0'),'1') *
       isnull(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN ('17','302','16') THEN '0.85' ELSE '1' END),'0'),'1') *
       isnull(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN ('3','7') THEN '0.90' ELSE '1' END),'0'),'1') AS "FTE VALUE"

     FROM RM_LIVE.EMPBASE,
     RM_LIVE.EMPLOYEE,
     RM_LIVE.CRWBASE,
     RM_LIVE.EMPNAME,
     RM_LIVE.CRWSPECFUNC,
     RM_LIVE.EMPSPECFUNC,RM_LIVE.EMPQUALCAT,RM_LIVE.CRWCAT
       where RM_LIVE.EMPBASE.IDEMPNO = RM_LIVE.EMPLOYEE.IDEMPNO
        AND RM_LIVE.EMPBASE.IDCRWBASE = RM_LIVE.CRWBASE.IDCRWBASE
         AND RM_LIVE.EMPLOYEE.IDEMPNO = RM_LIVE.EMPNAME.IDEMPNO 
          AND RM_LIVE.EMPSPECFUNC.IDCRWSPECFUNC =RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC  
           AND RM_LIVE.EMPSPECFUNC.IDEMPNO =RM_LIVE.EMPLOYEE.IDEMPNO
            AND  RM_LIVE.EMPQUALCAT.IDEMPNO=RM_LIVE.EMPLOYEE.IDEMPNO 
             AND  RM_LIVE.CRWCAT.IDCRWCAT = RM_LIVE.EMPQUALCAT.IDCRWCAT
              AND RM_LIVE.CRWCAT.crwcat IN ('CP','FO','CM','MC')
               AND RM_LIVE.CRWBASE.BASE <> 'XYZ'
                AND RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN  
                ('921','2' ,'1','301','17','4','3','7','302' ,'861','31',
                 '723','30','722 ','29 ','721','16','601','581')  
                  AND RM_LIVE.EMPBASE.STARTDATE <= SYSDATE
                   AND RM_LIVE.EMPBASE.ENDDATE >= SYSDATE
                    AND RM_LIVE.EMPSPECFUNC.STARTDATE <= SYSDATE
                     AND RM_LIVE.EMPSPECFUNC.ENDDATE >= SYSDATE
                      AND RM_LIVE.EMPNAME.FROMDATE <=SYSDATE
                       AND RM_LIVE.EMPQUALCAT.STARTDATE <= SYSDATE
                        AND RM_LIVE.EMPQUALCAT.ENDDATE >= SYSDATE
                        AND TRUNC(RM_LIVE.EMPSPECFUNC.STARTDATE) <=    TO_DATE('01/JAN/2013','dd/mon/yyyy')
                        AND TRUNC(RM_LIVE.EMPSPECFUNC.ENDDATE) > =  TO_DATE('01/JAN/2013','dd/mon/yyyy')
  GROUP BY RM_LIVE.EMPLOYEE.EMPNO, RM_LIVE.EMPNAME.FIRSTNAME,
       RM_LIVE.EMPNAME.LASTNAME, RM_LIVE.CRWBASE.BASE,RM_LIVE.CRWCAT.crwcat;

Why do you have numbers enclosed in single quotes, making them string literals instead of actual numbers? — Alex Poole, Apr 26 '16 at 09:12

piyushj · Answer 1 · 2016-05-01T14:44:15.060

0

isNULL is not defined in oracle try NVL instead

edited May 01 '16 at 14:44

answered Apr 26 '16 at 09:08

piyushj

1,546
5
21
29

I was wondering - was there a reason you rolled back my edit? Nothing was changed, but the punctuation and capitalization were improved, and `ISNULL` and `NVL` (which *are* function names) were wrapped in code blocks, as they should be. I ask because another one of your edits — where code blocks are used inappropriately — is the subject of a [Meta Stack Overflow question](http://meta.stackoverflow.com/questions/322098/why-is-this-a-terrible-edit-suggest); I'm wondering if there's some confusion as to the meaning of backticks (\`) in Stack Overflow posts. – Paul Roub Apr 29 '16 at 22:09
i just wanted to see if the rollback works or not, i have made the suggested edits, thanks for the suggested edit – piyushj May 01 '16 at 14:45

Invalid Identifier error in Oracle.

1 Answers1