Converting a query from Oracle PL/SQL to Microsoft T-SQL

Question

UPDATE: Thanks for all your help guys! I just need to take a litte bit of time to tlook through the results and I will definitely mark the answer. Really appreciate everyone's feedback!

I have the following query that needs to be converted from Oracel PL/SQL to T-SQL to accomodate a data source change. I've already done the work needed to isolate the logic (still written in PL/SQL) and merely need to adjust the remaining parts. Things like TO_DATE have, in particular, been tricky to convert so I decided to turn to StackOverflow.

I have already heard about the SwisSQL tool but as this is just an isolated instance of a single query that needs conversion using that product in not a possibility. Any and all help in converting the query to use proper T-SQL synthax would be greatly appreciated. Thank you for your time and here's the query in question:

 SELECT
            F.TYPE_ID,
            TRIM(f.event_type_name),
            TRIM(e.event_name),
            NVL(trim(e.event_title),' '),
            e.cur_event_state,
            TO_CHAR(D.EV_START_DT, 'YYYYMMDD') ,
            TO_CHAR(D.EV_START_DT,'HH24MI') ,
            TO_CHAR(D.EV_END_DT, 'YYYYMMDD') ,
            TO_CHAR(D.EV_END_DT,'HH24MI') ,
            TO_char(d.EV_START_DT, 'D')
    from rooms C,
         SP_RESERVATIONS D,
         EVENTS E,
         event_types f
    where @Room = TRIM(replace(C.room_short(+),'-','*'))
AND C.ROOM_ID = D.ROOM_ID
      AND D.EVENT_ID = E.EVENT_ID
      and e.event_type_id = f.type_id
      and f.type_id in ('22','40','70','71','72','105','121','119')
      AND (D.EV_START_DT
                     BETWEEN TO_DATE(:WS-TERM-START-DATE,'YYYYMMDD')
                        AND TO_DATE(:WS-TERM-END-DATE,'YYYYMMDD')
       OR  D.EV_END_DT
                     BETWEEN TO_DATE(:WS-TERM-START-DATE,'YYYYMMDD')
                        AND TO_DATE(:WS-TERM-END-DATE,'YYYYMMDD'))
      and not e.cur_event_state = '59'

Thanks!

In your filters you use some columns like B.rooms_id, but I don't see the table B on your FROM — Lamak
– Lamak, Commented Jan 30, 2012 at 22:03
Thanks for catching this, Lamark. The query's been updated with the right value. As for my question, it is to have this query converted to T-SQL from PL/SQL. — user1179071
– user1179071, Commented Jan 30, 2012 at 22:11
Is this query supposed to run on SQL Server?, if that is the case, on what version? — Lamak
– Lamak, Commented Jan 30, 2012 at 22:16
It's going to be run embedded in a Visual Studio Application. The database against which it rans is SQL Server 2008. — user1179071
– user1179071, Commented Jan 30, 2012 at 22:20

Jake Feasel · Accepted Answer · 2012-01-30 22:39:02Z

2

Here's your Oracle code as a SQL Fiddle: http://sqlfiddle.com/#!4/80d20/4

And here's the modified version as SQL Server: http://sqlfiddle.com/#!3/5fe1b/2

SELECT
            F.TYPE_ID,
            RTRIM(LTRIM(f.event_type_name)),
            RTRIM(LTRIM(e.event_name)),
            COALESCE(RTRIM(ltrim(e.event_title)),' '),
            e.cur_event_state,
            convert(varchar, D.EV_START_DT, 112) ,
            datepart(hh, D.EV_START_DT) ,
            convert(varchar, D.EV_END_DT, 112) ,
            datepart(hh, D.EV_END_DT) ,
            datepart(d, d.EV_START_DT)
    from rooms C,
         SP_RESERVATIONS D,
         EVENTS E,
         event_types f
    where 'Big Room' = RTRIM(LTRIM(replace(C.room_short,'-','*')))
AND C.ROOM_ID = D.ROOM_ID
      AND D.EVENT_ID = E.EVENT_ID
      and e.event_type_id = f.type_id
      and f.type_id in ('22','40','70','71','72','105','121','119')
      AND (D.EV_START_DT
                     BETWEEN '2012-01-01'
                        AND '2012-02-01'
       OR  D.EV_END_DT
                     BETWEEN '2012-01-01'
                        AND '2012-02-01')
      and not e.cur_event_state = '59'

edited adding rtrim with ltrim

answered Jan 30, 2012 at 22:39

Jake Feasel

17k5 gold badges55 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1179071 Over a year ago

Thank you for the query and for pointing me to this site. Much appreciated!

Lamak · Accepted Answer · 2012-01-30 22:24:22Z

1

Ok, try this query:

SELECT f.type_id, 
       LTRIM(RTRIM(f.event_type_name)), 
       LTRIM(RTRIM(e.event_name)), 
       ISNULL(LTRIM(RTRIM(e.event_title)), ' '), 
       e.cur_event_state, 
       CONVERT(VARCHAR(8), d.ev_start_dt, 112), 
       CONVERT(VARCHAR(5), d.ev_start_dt, 114), 
       CONVERT(VARCHAR(8), d.ev_end_dt, 112), 
       CONVERT(VARCHAR(5), d.ev_end_dt, 114), 
       DATENAME(DAY, d.ev_start_dt) 
FROM   rooms c 
       INNER JOIN sp_reservations d 
         ON c.room_id = d.room_id 
       INNER JOIN [EVENTS] e 
         ON d.event_id = e.event_id 
       INNER JOIN event_types f 
         ON e.event_type_id = f.[type_id] 
WHERE  @Room = RTRIM(LTRIM(REPLACE(c.room_short, '-', '*'))) 
       AND f.[type_id] IN ( '22', '40', '70', '71', 
                            '72', '105', '121', '119' ) 
       AND ( d.ev_start_dt BETWEEN CONVERT(DATETIME, [WS-TERM-START-DATE], 112) 
                                   AND 
                                         CONVERT(DATETIME, [WS-TERM-END-DATE], 
                                         112) 
              OR d.ev_end_dt BETWEEN CONVERT(DATETIME, [WS-TERM-START-DATE], 112 
                                     ) AND 
                                         CONVERT(DATETIME, [WS-TERM-END-DATE], 
                                         112) ) 
       AND NOT e.cur_event_state = '59'

answered Jan 30, 2012 at 22:24

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

3 Comments

user1179071 Over a year ago

Thank you very much, Lamark! I will try this out right now and will come back with results.

ypercubeᵀᴹ Over a year ago

I think that the Oracle NVL(trim(e.event_title),' ') will also convert any string with spaces only to a 1-space string, too: ' ' (due to the ill-functioning of the empty strings in Oracle). This may or may not need special care in SQL-Server, depending on how empty and NULL strings will be dealt.

Lamak Over a year ago

@ypercube - you are probably right, I actually don't know for sure how that command will behave

score 0 · Accepted Answer · 2012-01-30 23:15:03Z

Here's my answer. Let me know if it does not work.

SELECT
   f.type_id                                                    ,
   RTRIM(LTRIM(f.event_type_name))                              ,
   RTRIM(LTRIM(e.event_name     ))                              ,
   ISNULL(RTRIM(LTRIM(e.event_title)),' ')                      ,
   e.cur_event_state                                            ,
   CONVERT(VARCHAR(8),d.ev_start_dt , 112)                      ,      -- 'YYYYMMDD'
   CONVERT(VARCHAR(2),d.ev_start_dt , 114) +
   SUBSTRING(CONVERT(VARCHAR(12),d.ev_start_dt , 114),4,2)      ,      -- 'HH24MI'  
   CONVERT(VARCHAR(8),d.ev_end_dt   , 112)                      ,      -- 'YYYYMMDD'
   CONVERT(VARCHAR(2),d.ev_end_dt   , 114) +    
   SUBSTRING(CONVERT(VARCHAR(12),d.ev_end_dt   , 114),4,2)      ,      -- 'HH24MI'  
   DATENAME(DAY, d.ev_start_dt)                                        -- 'D'       
FROM 
   rooms c
      INNER JOIN sp_reservations d
      ON c.room_id = d.room_id
      INNER JOIN events e
      ON d.event_id = e.event_id
      INNER JOIN event_types f
      ON e.event_type_id = f.type_id
WHERE 
   --@Room = RTRIM(LTRIM(REPLACE(c.room_short(+),'-','*'))) and 
   --variable = LOJ table.column above is not a real filter
   f.type_id IN ('22','40','70','71','72','105','121','119')
   and ( d.ev_start_dt
            BETWEEN CONVERT(datetime,[WS-TERM-START-DATE],112)
                AND CONVERT(datetime,[WS-TERM-END-DATE]  ,112)
         or d.ev_end_dt
            BETWEEN CONVERT(datetime,[WS-TERM-START-DATE],112)
                AND CONVERT(datetime,[WS-TERM-END-DATE]  ,112) )
   and e.cur_event_state != '59';
GO

Collectives™ on Stack Overflow

Converting a query from Oracle PL/SQL to Microsoft T-SQL

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related