������� �� ����� ���������������� ������� ����� � ����������

[Boltik Evgeny]

òÅÛÉÌ ÐÏÄÎÑÔØ ÜÔÕ ÔÅÍÕ × ÏÞÅÒÅÄÎÏÊ ÒÁÚ ÐÏ ÐÒÉÞÉÎÅ ÔÏÇÏ ÞÔÏ ÂÁÚÙ Õ ÎÅËÏÔÏÒÙÈ
ËÌÉÅÎÔÏ× ÒÁÓÔÕÔ É ÐÒÏÇÒÁÍÍÁ ÐÏÐÒÏÓÔÕ ÎÁÞÉÎÁÅÔ ÐÏÄÔÏÒÍÁÖÉ×ÁÔØ É ÞÅÍ ÄÁÌØÛÅ
ÔÅÍ ÄÅÌÁ ÈÕÖÅ Ô.Ë. ÄÁÎÎÙÈ ÂÏÌØÛÅ Ó ËÁÖÄÙÍ ÄÎÅÍ. îÁÞÁÌ ÒÁÚÂÉÒÁÔØÓÑ  É
ÐÒÉÍÅÎÉÌ 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] ÈÏÔÅÌÏÓØ ÂÙ ÔÏÖÅ ÕËÁÚÁÔØ ÐÒÉ ÄÏÂÁ×ÌÅÎÉÉ ÕÓÌÏ×ÉÑ