question

Beacon77-6500 avatar image
0 Votes"
Beacon77-6500 asked EricMILA-7882 answered

Need help converting from oracle to sql server

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
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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@Beacon77-6500

there are special differences between different database languages . You need an expert who knows both sql sever and Oracle to help you realize it .
In order to get better interactive support, you may wish to go to the Oracle forum to also ask about related conversions.

Bert Zhou

1 Vote 1 ·
EricMILA-7882 avatar image
0 Votes"
EricMILA-7882 answered NaomiNNN commented

Hi Beacon77-6500

The function XMLTABLE in Oracle import XML to a table. In SQL Server you can do the same but it's in a different method. See this example to understand it.

[1]: https://www.mssqltips.com/sqlservertip/5707/simple-way-to-import-xml-data-into-sql-server-with-tsql/


For the date convert problem, you must convert string to date and apply a desired format.

I hope bring some help
Best Regards

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@EricMILA-7882 thanks but that is not what I was looking for. So according to Oracle experts
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
Is there something in SQL Server that does the same?
Thanks

0 Votes 0 ·

Numbers table on the fly, like this:

 ;WITH E1(N) AS (
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                 ),                          --10E+1 or 10 rows
        E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
        E4(N) AS (SELECT 1 FROM E2 a, E2 b)
    
 SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number FROM E4


Taken from this article https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function


0 Votes 0 ·
Beacon77-6500 avatar image
0 Votes"
Beacon77-6500 answered NaomiNNN commented

@NaomiNNN Thanks , how is it connecting to d1 and d2?
Thanks

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You cross join with the table of numbers (or cte created on the fly, but permanent numbers table is better) adding condition where Number between d1 and d2 if you want to generate rows and replicate some info.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

The standard solution to this problem that works on any relational database engine is to use a table of numbers. That's simply a one-column tables with consecutive numbers. I have a shorter article on my web site that discusses this concept and gives some examples: https://www.sommarskog.se/Short%20Stories/table-of-numbers.html.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EricMILA-7882 avatar image
0 Votes"
EricMILA-7882 answered

Hi Beacon77-6500

About the row's number read this function. sql-server-row_number-function



I Hope bring help to you
Best Regards

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.