transform sql query to oracle

Question

i have to transform some query i´m using in SQL to Oracle code. I´m having a lot of trouble with tis. Does anyone know any Query transformer o something like that?. Can someone translate some part of this code for me?.

This is the code:

SELECT PRUEBA = CASE (SELECT TIMEATT FROM READER WHERE PANELID = DEVID AND READERID = 
MACHINE) WHEN '1' THEN 'P10' ELSE 'P20' END 

+ '0001' 
+ CAST(YEAR(EVENT_TIME_UTC)AS VARCHAR)
+ Right('0' + Convert(VarChar(2), Month(EVENT_TIME_UTC)), 2)
+ Right('0' + Convert(VarChar(2), DAY(EVENT_TIME_UTC)), 2)
+ Right('0' + Convert(VarChar(2), DATEPART(HOUR,EVENT_TIME_UTC)), 2)
+ Right('0' + Convert(VarChar(2), DATEPART(MINUTE,EVENT_TIME_UTC)), 2)
+ Right('0' + Convert(VarChar(2), DATEPART(SECOND,EVENT_TIME_UTC)), 2) 
+ CAST(YEAR(EVENT_TIME_UTC)AS VARCHAR)
+ Right('0' + Convert(VarChar(2), Month(EVENT_TIME_UTC)), 2)
+ Right('0' + Convert(VarChar(2), DAY(EVENT_TIME_UTC)), 2)
+ Right('0' + Convert(VarChar(2), DATEPART(HOUR,EVENT_TIME_UTC)), 2)
+ Right('0' + Convert(VarChar(2), DATEPART(MINUTE,EVENT_TIME_UTC)), 2)
+ Right('0' + Convert(VarChar(2), DATEPART(SECOND,EVENT_TIME_UTC)), 2)
+ Right('00000000' + Convert(VarChar(8), CARDNUM), 8) 
+ Right('00000000' + Convert(VarChar(8), (SELECT SSNO FROM EMP WHERE ID = EMPID)), 8),

FROM events 

  WHERE eventid = 0 AND eventid = 0

  and machine in (11) AND DEVID IN (1,2)
  and CARDNUM <> 0 AND EMPID <> 0
  and EVENT_TIME_UTC between '2006-02-16' AND '2007-02-09'

Many thanks for your help, i´ll keep looking.

FYI Oracle is an implementation of SQL. Do you mean Microsoft SQL Server? — JNK
– JNK, Commented Apr 19, 2011 at 19:23
Providing the existing output would help to arrive at the query much faster than decoding what the SQL Server Counter part is doing. — Chandu
– Chandu, Commented Apr 19, 2011 at 19:28
Yes, i´m using SQL Server... the output is a single column with a string like this: — NicoRiff
– NicoRiff, Commented Apr 19, 2011 at 19:31

Chandu · Accepted Answer · 2011-04-20 03:01:40Z

2

Try this:

SELECT CASE (SELECT timeatt FROM reader WHERE panelid = devid AND readerid = 
       machine) 
         WHEN '1' THEN 'P10' 
         ELSE 'P20' 
       END 
       || '0001' 
       || To_char(event_time_utc, 'RRRRMMDDHH24MISS') 
       || To_char(event_time_utc, 'RRRRMMDDHH24MISS') 
       || Lpad(cardnum, 8, '0') 
       || Lpad((SELECT ssno 
                         FROM   emp 
                         WHERE  id = empid), 8, '0') AS prueba 
FROM   events 
WHERE  eventid = 0 
       AND eventid = 0 
       AND machine IN ( 11 ) 
       AND devid IN ( 1, 2 ) 
       AND cardnum <> 0 
       AND empid <> 0 
       AND event_time_utc BETWEEN TO_DATE('2006-02-16', 'RRRR-MM-DD') AND TO_DATE('2007-02-09', 'RRRR-MM-DD')

edited Apr 20, 2011 at 3:01

answered Apr 19, 2011 at 20:05

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

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

4 Comments

NicoRiff Over a year ago

This query seems to be what i need but i´m getting this error now: ORA-01489: result of string concatenation is too long. I will keep looking. Thanks you very much

Jeffrey Kemp Over a year ago

why is the second parameter to LPAD a query of ssno? Wouldn't that convert ssno to a number, then append that many '0's to the string, thus causing ORA-01489?

Chandu Over a year ago

@Jeffrey: Good catch. Fixed the query. Might have been a mistake during the query conversion.

NicoRiff Over a year ago

Thanks to both, the query worked perfectly now. You have really helped me.

Cos Callis · Accepted Answer · 2011-04-20 00:23:19Z

I have recently had to make the same conversion from a life in tSQL to plSQL (oracle). A couple of "gotcha's" in the code you posted:

1) In tSQL the plus sign (for concatenation)+ is replaced in plSQL with double pipe ||

2) Most of the time you need a "Reference Cursor" (REF CURSOR) declared to put your results into like

PROCEDURE DEMO_SELECT_4_SO(

//other parameters followed by//

P_RESULT OUT REF CURSOR)

IS

BEGIN

OPEN P_RESULT FOR
     SELECT
     //fields///
    FROM
     a_table
    WHERE
    //you want..//

OR (as with a scalar result like your query) a single parameter of the correct type, like:

PROCEDURE DEMO_SELECT_4_SO(

//other parameters followed by//

P_RESULT OUT varchar2(60))

IS

BEGIN

     SELECT
     //concatenated fields///
    INTO
       P_RESULT
    FROM
     a_table
    WHERE
    //you want..//

NOTICE That select into in plSQL assigns the selected value to the target parameter and does not create a table as it would in tSQL

3) RIGHT (or LEFT) are SUBSTR functions in plSQL

I have found a lot of utility out of this link http://www.techonthenet.com/oracle/index.php for clear explanations of plSQL.

Collectives™ on Stack Overflow

transform sql query to oracle

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related