Oracle to SQL Formatting Issues

Question

Hi All,
I am trying to convert code from Oracle SQL to T SQL. But it is not formatting the exact way it does in Oracle . I used corresponding functions in TSQL, like using RIGHT and LEFT functions instead of RPAD and LPAD, Using CASE instead of DECODE. Please see the below queries and attached pics of the OUTPUT. Please advice where I am getting it wrong.
ORACLE VERSION
select '"RECORDER ID" " DATE" " HOUR" " IN" " UN" " INTSTAT" " KW" "CHNSTAT" " KVAR" "CHNSTAT" " " " " " " " " ' from dual

union  
  
   
  
   
  
SELECT RPAD('"' || RPAD(a.p_mtrid,14,' ') || '" ' || decode(to_char((TO_DATE(a.p_dtm,'YYYYMMDDHH24MI')),'HH24MI'),'0000',  
  
                                                            to_char((TO_DATE(a.p_dtm,'YYYYMMDDHH24MI')-(1)),'MMDDYY' )|| ' ' || (to_number(to_char((TO_DATE(a.p_dtm,'YYYYMMDDHH24MI')-(1)),'HH24MI' ))+24)|| to_char((TO_DATE(a.p_dtm,'YYYYMMDDHH24MI')-(1)),'MI' ),  
  
                                                            to_char((TO_DATE(a.p_dtm,'YYYYMMDDHH24MI')),'MMDDYY HH24MI' )) || ' 15 "' ||  
  
       LPAD(a.UOM_SEC,5,' ') || '" "' ||  
  
       a.int_stat1 || '" ' ||  
  
       LPAD(TO_CHAR(a.chan1_usage,'999999990D999'),14,' ') || ' "' ||  
  
       a.chan1_stat || '" ' ||  
  
       LPAD(TO_CHAR(a.chan2_usage,'999999990D999'),14,' ') || ' "' ||  
  
       a.chan2_stat || '" ' ||  
  
       LPAD(TO_CHAR(NVL(a.chan3_usage,0),'999999990D999'),14,' ') || ' "' ||  
  
       NVL(a.chan3_stat,'  ') || '" ' ||  
  
       LPAD(TO_CHAR(NVL(a.chan4_usage,0),'999999990D999'),14,' ') || ' "' ||  
  
       NVL(a.chan4_stat,'  ') || '" ',374,' ') AS tmp  
![select '"RECORDER ID"  " DATE" " HOUR" " IN" " UN" " INTSTAT"   "     KW" "CHNSTAT" "   KVAR" "CHNSTAT" "      " " "        "      " " " '  AS  TEST                                                                                                                                                                                                                                  
  
union  
  
   
  
SELECT ('"'+K.MTRID1+'"'+k.DTM_MMDDYY_SPACE_HHMIN+'"'+'  '+k.FIFTEEN+'"'+k.KW+'"'+k.CHAN1USAGE+'"'+k.CHAN2USAGE+'"'+k.CHAN3USAGE+'"'+K.CHAN4USAGE)  
  
---+K.DECODEDSTUFF+''+K.DTM_PLUS24MIN+K.DTM_MMDDYY_PREVDAY+K.DTM_MMDDYY_SPACE_HHMIN+K.FIFTEEN+'"'+K.KW+'"'+K.int_stat1+'"'+K.CHAN1USAGE+'"'+K.chan1_stat+'"'+K.CHAN2USAGE+  
  
---'"'+K.chan2_stat+'"'+K.CHAN3USAGE+'"'+K.chan3_stat+'"'+K.CHAN4USAGE+'"'+K.chan4_stat+'"'+'"'+K.THREESEVENFOUR+'"'+'') AS TMP  
  
FROM  
  
(SELECT  
  
A.P_MTRID, A.P_DTM  
  
,RIGHT(A.P_DTM,4) AS DTM_HHMM  
  
--,CONVERT(DATE,SUBSTRING(A.P_DTM,5,4),'MMYY')  
  
----,CONVERT(DATE,A.P_DTM,'MMYY')  
  
---,CASE WHEN LEN((CAST(STUFF(STUFF(A.P_DTM,1,4,''),5,4,'') AS numeric)-1))=3 THEN (CAST(STUFF(STUFF(A.P_DTM,1,4,''),5,4,'') AS numeric)-1) ELSE (CAST(STUFF(STUFF(A.P_DTM,1,4,''),5,4,'') AS numeric)-1) END  
  
,CONCAT('0',CONVERT(VARCHAR,(CAST(STUFF(STUFF(A.P_DTM,1,4,''),5,4,'') AS numeric)-1)))  DTM_MMDDYY_PREVDAY  
  
,SUBSTRING(A.P_DTM,3,2) AS R1  
  
,convert(varchar,RIGHT(A.P_DTM,4)+24) DTM_PLUS24MIN  
  
,RIGHT(A.P_DTM,2) DTM_MIN  
  
,STUFF(STUFF(A.P_DTM,1,4,''),5,4,'')+SUBSTRING(A.P_DTM,3,2)+'  '+RIGHT(A.P_DTM,4) DTM_MMDDYY_SPACE_HHMIN  
  
,LEFT(A.P_MTRID+ space(14), 14) MTRID1  
  
,CASE WHEN RIGHT(A.P_DTM,4)='0000' THEN CONCAT('0',CONVERT(VARCHAR,(CAST(STUFF(STUFF(A.P_DTM,1,4,''),5,4,'') AS numeric)-1))) ELSE RIGHT(A.P_DTM,4) END AS DECODEDSTUFF  
  
,RIGHT(space(5) + A.UOM_SEC, 5) AS KW  
  
,A.int_stat1  
  
-----,LEFT(A.chan1_usage+ space(14), 14) AS CHAN1USAGE  
  
,RIGHT(space(14) + A.chan1_usage, 14) AS CHAN1USAGE  
  
,A.chan1_stat  
  
,RIGHT(space(14) + A.chan2_usage, 14) AS CHAN2USAGE  
  
,A.chan2_stat  
  
,ISNULL(RIGHT(space(14) + A.chan3_usage, 14),0) AS CHAN3USAGE  
  
,A.chan3_stat  
  
,ISNULL(RIGHT(space(14) + A.chan4_usage, 14),0) AS CHAN4USAGE  
  
,A.chan4_stat  
  
,CONVERT(VARCHAR,15) AS FIFTEEN  
  
,CONVERT(VARCHAR,374) AS THREESEVENFOUR][1]![245361-capture1.png][2]  

Thanks

Answer 1

But it is not formatting the exact way it does in Oracle .

Not the "formating", it's the SQL syntax is different, beside ANSI SQL function, it's very different. You have to re-write the SQL code.

Answer 2

@Olaf Helper thanks, but the data matches and which function is different syntax wise?
Thanks