Forum Stats

  • 3,838,712 Users
  • 2,262,395 Discussions
  • 7,900,739 Comments

Discussions

Need help converting from oracle to sql server

User_5BILT
User_5BILT Member Posts: 5 Green Ribbon

Hi All I have an Oracle Query that i want to convert to sql server, i could manage the date conversions but still there are some XMLTABLE that i cannot wrap my head around, Can someone please help me what the XMLTABLE part is doing and what is the equivalent in SQL Server.

Thanks

ORACLE QUERY

SELECT aosp.osi_key,

Cast(aa.osi_data_type AS NUMBER(3)) AS osi_data_type,

aa.hour_ctr,

sp.uom_name

FROM air_so aosp,

Sap sp,

(WITH t

AS (SELECT 1 d1,

5 d2

FROM dual)

SELECT bb.hour_ctr,

d1 + i osi_data_type

FROM t,

XMLTABLE('for $i in 0 to xs:int(D) return $i' passing XMLELEMENT(

d, d2

-d1) COLUMNS i INTEGER path '.'),

(WITH t

AS (SELECT ( To_date('07/30/2012 07', 'MM/DD/YYYY HH24') -

Numtodsinterval(10, 'hour') ) d1

,

( To_date('09/19/2012 11', 'MM/DD/YYYY HH24')

+ Numtodsinterval(10, 'hour') )

d2

FROM dual)

SELECT i hour_ctr

FROM t,

XMLTABLE('for $i in 0 to xs:int(D) return $i' passing

XMLELEMENT

(d, (d2

-d1)*24) COLUMNS i INTEGER path '.')) bb) aa

WHERE sp.osi_key = aosp.osi_key

AND sp.active_flag = 1

ORDER BY 1,

2,

3

 SQL SERVER 
 SELECT aosp.osi_key,
        Cast(aa.osi_data_type AS NUMBER(3)) AS osi_data_type,
        aa.hour_ctr,
        sp.uom_name
 FROM   air_so aosp,
        [dbo].[saP] sp
          ;WITH t
                     AS(SELECT 1 d1,
                        5 d2
                 ) 
        SELECT bb.hour_ctr,
               d1 + i osi_data_type
         FROM   t,
                XMLTABLE('for $i in 0 to xs:int(D) return $i' passing XMLELEMENT(
                d, d2
                -d1) COLUMNS i INTEGER path '.'),
                ;WITH t
                     AS (select convert(varchar, dateadd(hour, -10,'07/30/2012'), 101) d1
                                   ,
                                 convert(varchar, dateadd(hour, 10,'09/19/2012'), 101) 
                                   d2
                         )
                SELECT i hour_ctr
                 FROM   t,
                        XMLTABLE('for $i in 0 to xs:int(D) return $i' passing
                XMLELEMENT
                (d, (d2
                -d1)*24) COLUMNS i INTEGER path '.')) bb) aa
 WHERE  sp.osi_key = aosp.osi_key
        AND sp.active_flag = 1
 ORDER  BY 1,
           2,
           3


Tagged:

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,700 Employee

    This is the Oracle community, I think you want the MSFT one.

  • cormaco
    cormaco Member Posts: 1,952 Silver Crown
    edited May 20, 2022 1:44PM

    The first XMLTABLE is a row generator that counts from d1 to d2 in column osi_data_type:

    OSI_DATA_TYPE
    -------------
                1
                2
                3
                4
                5
    

    The second XMLTABLE similarly creates a numbered row for each hour between d1 and d2 in column hour_ctr:

      HOUR_CTR
    ----------
             0
             1
             2
             3
             4
             5
             6
    <Truncated>
          1246
          1247
          1248
    
    1.249 rows selected.
    

    I don't know if these work the same in SQL Server but they are not Oracle specific.

  • User_5BILT
    User_5BILT Member Posts: 5 Green Ribbon

    @cormaco thanks, is there a way of doing this without using XMLTABLE?

  • cormaco
    cormaco Member Posts: 1,952 Silver Crown

    There are several other ways of generating rows.

    Which work in SQL Server would definitely be a question for an SQL Server forum