òÅÛÉÌ ÐÏÄÎÑÔØ ÜÔÕ ÔÅÍÕ × ÏÞÅÒÅÄÎÏÊ ÒÁÚ ÐÏ ÐÒÉÞÉÎÅ ÔÏÇÏ ÞÔÏ ÂÁÚÙ Õ ÎÅËÏÔÏÒÙÈ
ËÌÉÅÎÔÏ× ÒÁÓÔÕÔ É ÐÒÏÇÒÁÍÍÁ ÐÏÐÒÏÓÔÕ ÎÁÞÉÎÁÅÔ ÐÏÄÔÏÒÍÁÖÉ×ÁÔØ É ÞÅÍ ÄÁÌØÛÅ
ÔÅÍ ÄÅÌÁ ÈÕÖÅ Ô.Ë. ÄÁÎÎÙÈ ÂÏÌØÛÅ Ó ËÁÖÄÙÍ ÄÎÅÍ. îÁÞÁÌ ÒÁÚÂÉÒÁÔØÓÑ É
ÐÒÉÍÅÎÉÌ EXECUTE STATEMENT ÏÔ ÂÅÚÙÓÈÏÄÎÏÓÔÉ É ÐÏÌÕÞÉÌ ÐÒÉÒÏÓÔ ×
ÐÒÏÉÚ×ÏÄÉÔÅÌØÎÏÓÔÉ ÂÏÌÅÅ 10 ÒÁÚ. òÁÚÒÁÂÏÔÞÉËÉ ÍÏÇÕÔ ÓËÁÚÔØ ×ÏÔ É ÐÉÛÉ ÎÏ ËÔÏ
ÐÉÓÁÌ ÉÓÐÏÌØÚÕÀÑ EXECUTE STATEMENT ÞÔÏ ÐÉÓÁÔØ ÎÅ ÔÁË ÕÖ É ÐÒÏÓÔÏ Ô.Ë. ÐÒÉ
ÄÏÐÕÝÅÎÉÉ ÏÛÉÂËÉ ÓÔÁÎÏ×ÉÔÓÑ ÎÅ ÐÏÓÉÂÅ ÞÉÔÁÑ ×ÏÔÔÁËÏÊ ËÏÄ ÍÉÎÕÔ 20 ÉÓËÁÌ
ÏÛÉÂËÕ Ô.Ë. ÐÒÏÐÕÓÔÉÌ ÐÒÏÂÅÌ :(.
CREATE PROCEDURE PVD248_ItemB (ID500_1 varchar(38), ID500_6 INTEGER, ID500_7
DATE, ID500_8 DATE,
ID026_1 varchar(38))
returns
(D009_1 varchar(38))
AS
declare variable sql varchar(1024);
declare variable sqlD503T varchar(300);
declare variable sqlD503W varchar(300);
BEGIN
sqlD503T = case ID500_6
when 0 then ''
else ', D503'
end;
sqlD503W = case ID500_6
when 1 then ' and D503.D500_1 = ''' || :ID500_1 || ''' and D503.D009_1
= T002.D009_1'
when 2 then ' and D503.D500_1 = ''' || :ID500_1 || ''' and not
D503.D009_1 = T002.D009_1'
else ''
end;
sql = 'SELECT T002.D009_1 FROM T002, D505' || sqlD503T || ' WHERE' ||
' D505.D500_1 = ''' || :ID500_1 || ''' and D505.D009_1 = T002.D009x and'
||
' T002.T003_3 <= ''' || :ID500_8 || ''' and' ||
' (''' || COALESCE(:ID026_1, 'null') || ''' = T002.D026_1 or' ||
' ''' || COALESCE(:ID026_1, 'null') || ''' = T002.D026_1b1 or' ||
' ''' || COALESCE(:ID026_1, 'null') || ''' = T002.D026_1b or' ||
' ''' || COALESCE(:ID026_1, 'null') || ''' = T002.D026_1b1b) and' ||
' not T002.D009_1 IS NULL' || sqlD503W ||
' GROUP BY T002.D009_1';
FOR EXECUTE STATEMENT sql
INTO :D009_1
DO BEGIN
SUSPEND;
END
sqlD503T = case ID500_6
when 0 then ''
else ', D503'
end;
sqlD503W = case ID500_6
when 1 then ' and D503.D500_1 = ''' || :ID500_1 || ''' and D503.D009_1
= T002.D009_1b'
when 2 then ' and D503.D500_1 = ''' || :ID500_1 || ''' and not
D503.D009_1 = T002.D009_1b'
else ''
end;
sql = 'SELECT T002.D009_1b FROM T002, D505' || sqlD503T || ' WHERE' ||
' D505.D500_1 = ''' || :ID500_1 || ''' and D505.D009_1 = T002.D009x and'
||
' T002.T003_3 <= ''' || :ID500_8 || ''' and' ||
' (''' || COALESCE(:ID026_1, 'null') || ''' = T002.D026_1 or' ||
' ''' || COALESCE(:ID026_1, 'null') || ''' = T002.D026_1b1 or' ||
' ''' || COALESCE(:ID026_1, 'null') || ''' = T002.D026_1b or' ||
' ''' || COALESCE(:ID026_1, 'null') || ''' = T002.D026_1b1b) and' ||
' not T002.D009_1b IS NULL' || sqlD503W ||
' GROUP BY T002.D009_1b';
FOR EXECUTE STATEMENT sql
INTO :D009_1
DO BEGIN
SUSPEND;
END
END
ñ ÍÏÌÞÕ ÕÖÅ ÐÒÏ ËÕÞÕ ËÏ×ÙÞÅË ËÏÔÏÒÙÅ ÎÕÖÎÏ ÎÁ×ÔÙËÁÔØ. ïÓÏÂÅÎÎÏ ÍÅÎÑ
ÐÒÉÂÉ×ÁÅÔ COALESCE( ËÏÔÏÒÙÊ ÐÒÉÈÏÄÉÔÓÑ ×ÔÙËÁÔØ ....
ëÁË ÉÚ×ÅÓÔÎÏ ÎÁ ×ÓÅ ÓÌÕÞÁÉ ÖÉÚÎÉ ÚÁÐÒÏÓ ÎÅ ÎÁÐÉÛÅÛØ Ô.Ë. ÑÚÙË SQL ÍÁÌÏ Ë
ÜÔÏÍÕ ÐÒÅÓÐÏÓÏÂÌÅÎ × ÓÉÌÕ Ó×ÏÅÊ ÏÓÏÂÅÎÎÏÓÔÉ É ÐÏÜÔÏÍÕ ÐÒÉÈÏÄÉÔÓÑ ÐÉÓÁÔØ ÔÁË
for select ... where
(
(:X = 0) or
(:X = 1 and exists()) or
(:X = 2 and not exists())
) and
(
(:Y = 0) or
(:Y = 1 and exists()) or
(:Y = 2 and not exists())
)
X & Y ÎÉËÏÇÄÁ ÎÅÍÅÎÑÀÔÓÑ × ÈÏÄÅ ×ÙÐÏÌÎÅÎÉÑ ÚÁÐÒÏÓÁ.
åÓÔÅÓÔ×ÅÎÎÏ ËÏÇÄÁ ÐÅÒÅÐÉÓÁÌ ÎÁ EXECUTE STATEMENT ÔÁÍ ÐÏÌÕÞÉÌÓÑ join É ÔÅ
ÓÁÍÙÅ 160000 ÚÁÐÉÓÅÊ ÐÒÅ×ÒÁÔÉÌÉÓØ × 600 Ô.Ë. ÄÌÑ exists ÂÙÌ ÐÅÒÅÂÏÒ ÚÁÐÉÓÅÊ.
ëÔÏ ÕÖÅ ÐÏÎÑÌ Ï ÞÅÍ ÒÅÞØ ÐÏÊÍÅÔ É ÔÏ ÞÔÏ × ÄÁÎÎÏÍ ÐÒÉÍÅÒÅ 9 ×ÁÒÉÁÎÔÏ×
ÚÁÐÒÏÓÏ× ÎÁÄÏ ÎÁÐÉÓÁÔØ ÞÔÏÂÙ ×ÓÅ ÂÙÌÉ ÎÁ 100% ÂÙÓÔÒÏÄÅÊÓÔ×ÕÀÝÉÍÉ. á ÔÁË
ÐÏÌÕÞÁÅÔÓÑ ÖÅÒÔ×ÕÅÔÓÑ ÐÒÏÉÚ×ÏÄÉÔÅÌØÎÏÓÔØ Ô.Ë. × ÏÔÞÅÔÁÈ ÚÁÄÅÊÓÔ×Ï×ÁÎÏ ÎÅ 2
ÐÁÒÁÍÅÔÒÁ Ó ÔÁËÉÍÉ ÕÓÌÏ×ÉÑÍÉ Á ËÁË ÐÒÁ×ÉÌÏ ÂÏÌØÛÅ, Á ÚÎÁÞÉÔ É ÕÞÅÓÔØ ×ÓÅ
×ÁÒÉÁÎÔÙ ÐÒÁËÔÉÞÅÓËÉ ÎÅ×ÏÚÍÏÖÎÏ.
ÎÏ ÅÓÌÉ ÂÙ ÓÅÒ×ÅÒ ÕÍÅÌ ÓÌÅÇËÁ ÆÏÒÍÉÒÏ×ÁÔØ ÓÅÌÅËÔ ÓÁÍ ÔÏ ÂÙÌÏ ÂÙ ÏÇÒÏÍÎÏÅ
ÓÞÅÓÔØÅ ×ÅÍ
ÔÏÂÉÛØ ÍÏÖÎÏ ÂÙÌÏ ÂÙ ÎÁÐÉÓÁÔØ ÔÁË
for select ... from T1
where ...
casefromwhere :X
when 1 then TX, TX.F1 = :F1 and T1.Fx = TX.Fx
when 2 then TX, TX.F1 = :F1 and not T1.Fx = TX.Fx
end
casefromwhere :Y
when 1 then TY, TY.F1 = :F1 and T1.Fy = TX.Fy
when 2 then TY, TY.F1 = :F1 and not T1.Fy = TX.Fy
end
ÐÏÞÅÍÕ casefromwhere ÐÏÔÏÊ ÐÒÉÞÉÎÅ ÞÔÏÂÙ ÍÏÖÎÏ ÂÙÌÏ ÕËÁÚÁÔØ ÞÔÏ Ë ÕÓÌÏ×ÉÀ
ÂÕÄÅÔ ÄÁÂÁ×ÌÅÎÁ É ÔÁÂÌÉÃÁ ÐÏ ×ÏÚÍÏÖÎÏÓÔÉ ÍÏÖÎÏ ËÏÎÅÞÎÏ É ÔÁË
case :Y
when 1 then from TY where TY.F1 = :F1 and T1.Fy = TX.Fy
when 2 then from TY where TY.F1 = :F1 and not T1.Fy = TX.Fy
end
ÜÔÏ ËÁË ÒÁÚÒÁÂÏÔÞÉËÉ ÓÏÞÔÕÔ ÎÕÖÎÙÍ ÌÉÖÂÙ ÂÙÌÏ É ÒÁÂÏÔÁÌÏ
ÐÒÉÍÅÒ ÐÏÎÉÍÁÎÉÑ ÓÅÒ×ÅÒÏÍ ÜÔÏÇÏ ÔÅËÓÔÁ
ÅÓÌÉ X = 0, Á Y = 2 ÔÏ ÓÅÒ×ÅÒ ÄÌÑ ÓÅÂÅ ÐÒÅÏÂÒÏÚÕÅÔ ÚÁÐÒÏÓ ×
for select ... from T1, TY
where ...
TY.F1 = :F1 and not T1.Fy = TX.Fy
ÅÓÌÉ X = 2 Á Y = 1 ÔÏ ÓÅÒ×ÅÒ ÄÌÑ ÓÅÂÅ ÐÒÅÏÂÒÏÚÕÅÔ ÚÁÐÒÏÓ ×
for select ... from T1, TX, TY
where ...
TX.F1 = :F1 and not T1.Fx = TX.Fx and
TY.F1 = :F1 and T1.Fy = TX.Fy
ÅÓÔÅÓÔ×ÅÎÎÏ [and | or] ÈÏÔÅÌÏÓØ ÂÙ ÔÏÖÅ ÕËÁÚÁÔØ ÐÒÉ ÄÏÂÁ×ÌÅÎÉÉ ÕÓÌÏ×ÉÑ
