Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    67

    Unanswered: Sqlserver To Oracle

    I have a query in SQL SERVER that needs to be converted to Oracle.

    The idea is simple.
    I have a table that has a date column as a field.

    I want to display 6 records at a time and there are conditions for displaying the 6 records.

    Here is the SQL QUERY

    select dte from
    (
    select top 6 B.dte
    from
    (
    select id='999999999',dte = min(dte) from dbo.tst
    union all
    select id='999999998',dte = max(dte) from dbo.tst
    union all
    select id='8' + convert(varchar(8),A.dte,112),A.dte from
    (
    select TOP 4 month = datepart(mm,dte),dte = max(dte)
    from dbo.tst
    group by datepart(mm,dte)
    having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)
    order by dte desc
    /**/) A
    union all
    select id='7' + convert(varchar(8),A.dte,112),A.dte from
    (
    select TOP 6 dte
    from dbo.tst
    where dte not in ( select dte = min(dte) from dbo.tst
    union all
    select dte = max(dte) from dbo.tst
    union all
    select dte from (
    select TOP 4 month = datepart(mm,dte),dte = max(dte)
    from dbo.tst
    group by datepart(mm,dte)
    having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)
    order by dte desc ) A
    )
    order by dte desc
    /**/) A
    ) B
    order by id desc
    ) C
    order by dte


    Can someone convert this to Oracle and let me know

    Thanks
    Shankar

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Tips:

    No datepart in Oracle ... you have to use the to_date and to_char functions ie,

    SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"
    FROM DUAL;

    The convert function will have to be to_char function

    convert(varchar(8),A.dte,112), = to_char(dte, 'dd-mm-yy') or however you want to format ...

    Loading a variable is

    select id='999999998',dte = max(dte) from dbo.tst
    to

    select '999999998', max(dte)
    into id, dte
    from dbo.tst


    HTH
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •