Friends,
I need to improve the speed of this query, and don't know how. I
apologize if this isn't the proper group; please point me in the
right direction if not.
The query runs in SQL*Plus and generates a fixed-width output file.
Part of the performance problem is because the tables reside in a
remote DB2 database; the rest comes from my ineptitude as a SQL
programmer.
Any help will be greatly appreciated. Thanks in advance.
Here is the query:
select lpad(AddrBook_T.ABAN8,12,'
0')||rpad(
AddrBook_T
.ABALPH,24
,' ')||
(
select rpad(ALADD1,24,' ')||rpad(ALADD2,24,' ')||rpad(ALCTY1,15,' ')
||rpad(ALADDS,2,' ')||
substr(replace(replace(ALA
DDZ,'-'),'
')||'000000000',1,9)
from F0116@HSODBC
where ALAN8 = AddrBook_T.ABAN8
)||
nvl
(
(
select rpad(replace(translate(WPA
R1,' -()','~'),'~'),3,'0')||rpa
d
(replace(translate(WPPH1,'
-()','~'),'~'),7,'0')
from F0115@HSODBC
where WPAN8 = AddrBook_T.ABAN8 and WPIDLN = 0 and WPPHTP = '' and
rownum = 1
),'0000000000'
)||
nvl
(
(
select rpad(replace(translate(WPA
R1,' -()','~'),'~'),3,'0')||rpa
d
(replace(translate(WPPH1,'
-()','~'),'~'),7,'0')
from F0115@HSODBC
where WPAN8 = AddrBook_T.ABAN8 and WPIDLN = 0 and WPPHTP = 'FAX'
and rownum = 1
),'0000000000'
)||' '||
rpad(replace(translate(upp
er(ABALPH)
,' ()+=?%$@!:;"?,/&-
*#''''.','~'),'~'),8,' ')||' '||
decode
(
Parent_T.MAPA8, null, rpad(' ',89,' ')||lpad('0',29,'0')||rpad
(' ',27,' ')||'|',
(
select rpad(ABALPH,24,' ')||
(
select rpad(ALADD1,24,' ')||rpad(ALADD2,24,' ')||rpad
(ALCTY1,15,' ')||rpad(ALADDS,2,' ')||
substr(replace(replace(ALA
DDZ,'-'),'
')||'000000000',1,9)
from F0116@HSODBC
where ALAN8 = Parent_T.MAPA8
)||
nvl
(
(
select rpad(replace(translate(WPA
R1,' -()','~'),'~'),3,'0')
||rpad(replace(translate(W
PPH1,' -()','~'),'~'),7,'0')
from F0115@HSODBC
where WPAN8 = Parent_T.MAPA8 and WPIDLN = 0 and WPPHTP = ''
and rownum = 1
),'0000000000'
)||
nvl
(
(
select rpad(replace(translate(WPA
R1,' -()','~'),'~'),3,'0')
||rpad(replace(translate(W
PPH1,' -()','~'),'~'),7,'0')
from F0115@HSODBC
where WPAN8 = Parent_T.MAPA8 and WPIDLN = 0 and WPPHTP
= 'FAX' and rownum = 1
),'0000000000'
)||' '||
rpad(replace(translate(upp
er(ABALPH)
,' ()+=?%$@!:;"?,/&-
*#''''.','~'),'~'),8,' ')
from F0101@HSODBC
where ABAN8 = Parent_T.MAPA8
)||' |'
)
FROM F0101@HSODBC AddrBook_T LEFT OUTER JOIN F0150@HSODBC Parent_T ON
Parent_T.MAAN8 = AddrBook_T.ABAN8
WHERE AddrBook_T.ABAT1 = 'C';
