Need help converting from oracle to sql server

User_5BILT

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

thatJeffSmith-Oracle

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

cormaco

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

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

cormaco

There are several other ways of generating rows.

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