Hi Folks, here is the ORACLE-SQL CODE as promised: In particular I am curious, if someone can tell me, how I "translate" the joins with (+) signs into ANSI-SQL. So far I was able to replace the following oracle functions with equivalent SAS function: decode() <--> CASE WHEN statement nvl <--> coalescec function / coalesce function lpad(to_char(TABLE2.COL2),9,'0') <--> put(table2.COL2 , z9.) SELECT
TABLE1.COL1,
TABLE2.COL1,
TABLE1.COL2,
TABLE1.COL3,
TABLE1.COL4,
decode(TABLE1.COL5,1,'A',2,'B',3,'C',4,'D',5,'E',6,'F',7,'G',8,'H',9,'I','NA'),
nvl(TABLE3_TOP.COL1, TABLE3.COL1),
TABLE4.COL1,
decode(TABLE1.COL6, 'STRING1', 'STRING1', 'STRING2', 'STRING2', 'OTHER'),
TABLE1.COL7,
TABLE1.COL8,
TABLE1.COL9,
TABLE1.COL10,
TABLE1.COL11,
TABLE1.COL12,
TABLE1.COL13,
TABLE5.COL1,
TABLE6.COL1,
decode(TABLE5.COL2,'A','STRING5','D','STRING6','NA'),
TABLE5.COL3+TABLE5.COL4,
TABLE6.COL2,
TABLE6.COL3,
TABLE6.COL4,
trim(TABLE72.COL1),
TABLE8_SRVR.COL1,
TABLE5.COL5,
TABLE5.COL6,
TABLE5.COL7,
TABLE9.COL1,
TABLE5.COL8,
TABLE5.COL9,
TABLE5.COL10,
sum((TABLE5.COL4 * TABLE5.COL6) + (TABLE5.COL3 * TABLE5.COL5)),
TABLE10.COL1,
nvl( TABLE112.COL1, 'NA' ),
TABLE5.COL11,
TABLE12.COL1
FROM
TABLE1,
TABLE2,
TABLE3,
TABLE3 TABLE3_TOP,
TABLE4,
TABLE5,
TABLE6,
TABLE7 TABLE72,
TABLE8 TABLE8_SRVR,
TABLE9,
TABLE10,
TABLE11 TABLE112,
TABLE12,
(
select COL1, to_number (nvl (regexp_replace (COL2, '[^[:digit:]]', ''), 0)) as flag
from TABLE12
where COL2 is not null
) DT_TABLE
WHERE
( TABLE1.COL1=TABLE2.COL2 )
AND ( TABLE1.COL2=TABLE5.COL11 )
AND ( TABLE12.COL1=TABLE1.COL2 )
AND ( TABLE6.COL1=TABLE5.COL1 )
AND ( TABLE72.COL2=TABLE6.COL5 )
AND ( TABLE3.COL2(+)=TABLE1.COL1 )
AND ( TABLE8_SRVR.COL2(+)=TABLE6.COL4 )
AND ( trim(TABLE4.COL2(+)) = to_char(TABLE12.COL2) )
AND ( substr(TABLE10.COL2(+),2,9)=lpad(to_char(TABLE2.COL2),9,'0') )
AND ( TABLE1.COL14=TABLE3_TOP.COL2(+) )
AND ( TABLE9.COL2(+)=TABLE5.COL12 )
AND ( TABLE10.COL1=TABLE112.COL2(+) )
AND ( DT_TABLE.COL1(+)=TABLE1.COL2 )
AND
(
( TABLE1.COL5 in (13,75,986) and TABLE1.COL1 not in ( '??','!!') and DT_TABLE.COL1 is null )
AND
TABLE1.COL1 IN
(
SELECT
TABLE1.COL1
FROM
TABLE1,
TABLE10,
TABLE2
WHERE
( TABLE1.COL1=TABLE2.COL2 )
AND ( substr(TABLE10.COL2(+),2,9)=lpad(to_char(TABLE2.COL2),9,'0') )
AND
TABLE10.COL1 IN ( 'URZTAS' )
)
AND
decode(TABLE5.COL2,'A','STRING5','D','STRING6','NA') IN ( 'STRING5' )
AND
(
(
TABLE5.COL7 <> 0
AND
TABLE5.COL8 = 'Y'
)
OR
TABLE5.COL5 > 0
)
AND
trim(TABLE72.COL1) <> 'TESTSTRING'
AND
(
TABLE8_SRVR.COL1 NOT IN ( 'BLUBB1','BLUBB2','BLUBB3','BLUBB4','BLUBB5','BLUBB6','BLUBB7','BLUBB8' )
OR
TABLE8_SRVR.COL1 Is Null
)
)
GROUP BY
TABLE1.COL1,
TABLE2.COL1,
TABLE1.COL2,
TABLE1.COL3,
TABLE1.COL4,
decode(TABLE1.COL5,1,'A',2,'B',3,'C',4,'D',5,'E',6,'F',7,'G',8,'H',9,'I','NA'),
nvl(TABLE3_TOP.COL1, TABLE3.COL1),
TABLE4.COL1,
decode(TABLE1.COL6, 'STRING1', 'STRING1', 'STRING2', 'STRING2', 'OTHER'),
TABLE1.COL7,
TABLE1.COL8,
TABLE1.COL9,
TABLE1.COL10,
TABLE1.COL11,
TABLE1.COL12,
TABLE1.COL13,
TABLE5.COL1,
TABLE6.COL1,
decode(TABLE5.COL2,'A','STRING5','D','STRING6','NA'),
TABLE5.COL3+TABLE5.COL4,
TABLE6.COL2,
TABLE6.COL3,
TABLE6.COL4,
trim(TABLE72.COL1),
TABLE8_SRVR.COL1,
TABLE5.COL5,
TABLE5.COL6,
TABLE5.COL7,
TABLE9.COL1,
TABLE5.COL8,
TABLE5.COL9,
TABLE5.COL10,
TABLE10.COL1,
nvl( TABLE112.COL1, 'NA' ),
TABLE5.COL11,
TABLE12.COL1 So, there are a couple of things remaining: 1.) How do I translate this into SAS: (regexp_replace (COL2, '[^[:digit:]]', ''), 0) 2.) How do I rewrite for example this join : ( TABLE8_SRVR.COL2(+)=TABLE6.COL4 ) Is this the same as saying TABLE8_SRVR.COL2 left join TABLE6.COL4 ? Any help is much appreciated. FK1
... View more
