EugeneBoss3
Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
ALTER PROCEDURE GET_FULLADDRESS_2 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT)
RETURNS (
SA VARCHAR(500))
AS
DECLARE VARIABLE NOA BIGINT;
DECLARE VARIABLE NOP BIGINT;
DECLARE VARIABLE NOL INTEGER;
DECLARE VARIABLE NO3 INTEGER;
DECLARE VARIABLE SPINDEX INTEGER;
DECLARE VARIABLE SPINDEX1 INTEGER;
DECLARE VARIABLE MPOS SMALLINT;
DECLARE VARIABLE OPOS SMALLINT;
DECLARE VARIABLE SP1 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SP2 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SFX VARCHAR(2);
BEGIN
SA = '';
SPINDEX = 0;
NOA = CA;
NO3 = 5;
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (ADDRESS_TYPE = 0) THEN SFX = ', ';
ELSE SFX = ',';
/* ПОСТРОЕНИЕ СТРОКИ АДРЕСА */
WHILE (NOA >= 0) DO
BEGIN
/* ДЛЯ НАЛОГОВОГО СЛУЧАЯ НАЗВАНИЯ РЕГИОНА НЕ ВЫЧИСЛЯЕТСЯ. ВЫХОДИМ */
IF ((ADDRESS_TYPE > 0) AND (NOA = 0)) THEN LEAVE;
NOP = -1;
FOR
SELECT A.ADDRESS_NAME, A.ADDRESS_PAR,
A.ABBREV_LEVEL, A.ABBREV_POS, A.ADDRESS_INDEX, B.ABBREV_SHNAME, B.ABBREV_POS
FROM KLADR_ADDRESS_2 A
LEFT JOIN KLADR_ABBREV B ON (B.ABBREV_LEVEL = A.ABBREV_LEVEL AND B.ABBREV_ID = A.ABBREV_ID)
WHERE (A.ADDRESSREGION_ID = :CR) AND (A.ADDRESS_ID = :NOA)
INTO :SP1, :NOP, :NOL,  POS, :SPINDEX1, :SP2, :MPOS
DO BEGIN
IF ((SPINDEX = 0) AND (SPINDEX1 <> 0)) THEN SPINDEX = SPINDEX1;
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:OPOS < 0) THEN OPOS = MPOS;
IF (:OPOS = 0) THEN SA = SP2 || ' ' || SP1 || SFX || SA;
ELSE
IF (:OPOS = 1) THEN SA = SP1 || ' ' || SP2 || SFX || SA;
ELSE SA = SP1 || SFX || SA;
END ELSE BEGIN
NOA = NOP;
WHILE (NO3 > NOL) DO
BEGIN
NO3 = NO3 - 1;
SA = SFX || SA;
END
SA = SP1 || ' ' || SP2 || SFX || SA;
NO3 = NO3 - 1;
END
END
IF ((NOA = 0) AND (NOP = 0)) THEN LEAVE;
NOA = NOP;
END
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:SPINDEX > 0) THEN SA = SPINDEX || SFX || SA;
END ELSE BEGIN
WHILE (NO3 > 1) DO
BEGIN
SA = SFX || SA;
NO3 = NO3 - 1;
END
IF (CR < 10) THEN SA = '0' || CR || SFX || SA;
ELSE SA = CR || SFX || SA;
IF (:SPINDEX <> 0) THEN SA = SFX || SPINDEX || SFX || SA;
ELSE SA = SFX || SFX || SA;
END
SUSPEND;
END
^
ALTER PROCEDURE GET_FULLADDRESS_20 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT,
CV SMALLINT)
RETURNS (
SA VARCHAR(500))
AS
DECLARE VARIABLE NOA BIGINT;
DECLARE VARIABLE NOP BIGINT;
DECLARE VARIABLE NOV INTEGER;
DECLARE VARIABLE NOL INTEGER;
DECLARE VARIABLE NO3 INTEGER;
DECLARE VARIABLE SPINDEX INTEGER;
DECLARE VARIABLE SPINDEX1 INTEGER;
DECLARE VARIABLE MPOS SMALLINT;
DECLARE VARIABLE OPOS SMALLINT;
DECLARE VARIABLE SP1 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SP2 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SFX VARCHAR(2);
BEGIN
SA = '';
SPINDEX = 0;
NOA = CA;
NOV = CV;
NO3 = 5;
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (ADDRESS_TYPE = 0) THEN SFX = ', ';
ELSE SFX = ',';
/* ПОСТРОЕНИЕ СТРОКИ АДРЕСА */
WHILE (NOA >= 0) DO
BEGIN
/* ДЛЯ НАЛОГОВОГО СЛУЧАЯ НАЗВАНИЯ РЕГИОНА НЕ ВЫЧИСЛЯЕТСЯ. ВЫХОДИМ */
IF ((ADDRESS_TYPE > 0) AND (NOA = 0)) THEN LEAVE;
NOP = -1;
FOR
SELECT A.ADDRESS_NAME, D.ADDRESS_PAR, D.ADDRESS_PARVERS,
A.ABBREV_LEVEL, A.ABBREV_POS, A.ADDRESS_INDEX, B.ABBREV_SHNAME, B.ABBREV_POS
FROM KLADR_ADDRESS_20 A
LEFT JOIN KLADR_ABBREV B ON (B.ABBREV_LEVEL = A.ABBREV_LEVEL AND B.ABBREV_ID = A.ABBREV_ID)
LEFT JOIN KLADR_ADDRESS_20_DEPEND D ON (
D.ADDRESSREGION_ID = A.ADDRESSREGION_ID AND
D.ADDRESS_ID = A.ADDRESS_ID AND
D.ADDRESS_VERSION = A.ADDRESS_VERSION)
WHERE (A.ADDRESSREGION_ID = :CR) AND (A.ADDRESS_ID = :NOA) AND (A.ADDRESS_VERSION = :NOV)
INTO :SP1, :NOP, :NOV, :NOL, POS, :SPINDEX1, :SP2, :MPOS
DO BEGIN
IF ((SPINDEX = 0) AND (SPINDEX1 <> 0)) THEN SPINDEX = SPINDEX1;
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:OPOS < 0) THEN OPOS = MPOS;
IF (:OPOS = 0) THEN SA = SP2 || ' ' || SP1 || SFX || SA;
ELSE
IF (:OPOS = 1) THEN SA = SP1 || ' ' || SP2 || SFX || SA;
ELSE SA = SP1 || SFX || SA;
END ELSE BEGIN
NOA = NOP;
WHILE (NO3 > NOL) DO
BEGIN
NO3 = NO3 - 1;
SA = SFX || SA;
END
SA = SP1 || ' ' || SP2 || SFX || SA;
NO3 = NO3 - 1;
END
END
IF ((NOA = 0) AND (NOP = 0)) THEN LEAVE;
NOA = NOP;
END
--
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:SPINDEX > 0) THEN SA = SPINDEX || SFX || SA;
END ELSE BEGIN
WHILE (NO3 > 1) DO
BEGIN
SA = SFX || SA;
NO3 = NO3 - 1;
END
IF (CR < 10) THEN SA = '0' || CR || SFX || SA;
ELSE SA = CR || SFX || SA;
IF (:SPINDEX <> 0) THEN SA = SFX || SPINDEX || SFX || SA;
ELSE SA = SFX || SFX || SA;
END
SUSPEND;
END
^
|
