locked
want execute sql server query in oracle RRS feed

  • 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"
    Wednesday, August 17, 2011 2:54 AM

All replies

  • 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
    /
    Saturday, August 20, 2011 9:52 PM