want execute sql server query in oracle

Question

User-502209244 posted

Hi All...

 im new to oracle and i want small query need to exicutee in oracle.

this is working query in ms sql server. i want this to execute in oracle also..

select TotalPages, to_date(uploadedDate, 'dd/MM/yyyy') as uploadedDate,PagesNo
 from ( (select ROW_NUMBER() OVER (ORDER BY to_date(uploadedDate, 'dd/MM/yyyy')   DESC )
as ROW_NUM,*  FROM (  select ScannedDate as UploadedDate,SUM(PagesNum) as PagesNo ,
(select sum(pagesNum) from Scanned_Files_Table where IndexFlag=1 and DeleteFlag=0 and 
to_date(ScannedDate, 'dd/MM/yyyy') >= to_date('16/08/2011 12:00:00 AM', 'dd/MM/yyyy') 
and  to_date(ScannedDate, 'dd/MM/yyyy')  <= to_date('17/08/2011 12:00:00 AM', 'dd/MM/yyyy'))
as TotalPages  from Scanned_Files_Table where IndexFlag=1 and DeleteFlag=0 and 
to_date(ScannedDate, 'dd/MM/yyyy') >=  to_date('16/08/2011 12:00:00 AM', 'dd/MM/yyyy')
and  to_date(ScannedDate, 'dd/MM/yyyy')  <= to_date('17/08/2011 12:00:00 AM', 'dd/MM/yyyy') 
group by ScannedDate ) temp )) innerSelect WHERE ROW_NUM >0

---------

and getting error like 

ORA-00936: missing expression
00936. 00000 -  "missing expression"

Answer 1

User269602965 posted

I am uncertain of why you need ROW_NUM > 0 since Oracle ROW_NUM always starts with 1 and therefore will always be > 0,

  and you are not displaying or using the ROW_NUM value in the final outer select.

Often, MSSQL and ORacle queries are not entirely interchangeable since the SQL syntax is often different between the two databases,

  despite both for certain basic functions conforming with ANSI SQL standard.

Cleaning up layout of your coding follows with explicit table and column naming.

I do not have a test dataset to run this on, so good luck.

SELECT 
  d.TotalPages, 
  TO_DATE(d.UploadedDate, 'dd/MM/yyyy') as UploadedDate,
  d.PagesNo 
FROM 
  ( 
  SELECT
    c.TotalPages,
    c.UploadedDate,
    c.PagesNo,
    ROW_NUMBER() OVER (ORDER BY TO_DATE(c.uploadedDate, 'dd/MM/yyyy') DESC) as ROW_NUM
  FROM 
    (
    SELECT 
      b.ScannedDate   as UploadedDate,
      SUM(b.PagesNum) as PagesNo,
      (
      SELECT 
        SUM(a.pagesNum) 
      FROM 
        Scanned_Files_Table a 
      WHERE 
        a.IndexFlag = 1
        AND
        a.DeleteFlag = 0
        AND
        TO_DATE(a.ScannedDate, 'dd/MM/yyyy') >= TO_DATE('16/08/2011 12:00:00 AM', 'dd/MM/yyyy') 
        AND
        TO_DATE(a.ScannedDate, 'dd/MM/yyyy') <= TO_DATE('17/08/2011 12:00:00 AM', 'dd/MM/yyyy')
      ) as TotalPages
    FROM 
      Scanned_Files_Table b
    WHERE 
      b.IndexFlag = 1
      AND
      b.DeleteFlag = 0
      AND
      TO_DATE(b.ScannedDate, 'dd/MM/yyyy') >= TO_DATE('16/08/2011 12:00:00 AM', 'dd/MM/yyyy')
      AND
      TO_DATE(b.ScannedDate, 'dd/MM/yyyy') <= TO_DATE('17/08/2011 12:00:00 AM', 'dd/MM/yyyy') 
    GROUP BY 
      b.ScannedDate
    ) c
  ) d
WHERE d.ROW_NUM > 0
/