777uraganЖуравлев Денис,
не работает....ошибка в синтаксисе
SQL Error (-201): A syntax error has occurred.
Error Position: Ln: 5 Col: 26
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE PROCEDURE search(p1 VARCHAR(15),p2 VARCHAR(15),p3 VARCHAR(15),p4 VARCHAR(15),p5 VARCHAR(15))
returning VARCHAR(20) AS tipteh,VARCHAR(50) AS naim,VARCHAR(15) AS invent,VARCHAR(20) AS datevvod,VARCHAR(20) AS dategar,VARCHAR(20) AS sernum,VARCHAR(50) AS prim;
define tipteh,naim,invent,datevvod,dategar,sernum,prim VARCHAR(50);
foreach c1 FOR
SELECT tabl.name_tabl AS Тип_техники,spr_proc.name_proc||'/'||spr_chast.name_chast AS Наименование,inv_num AS Инвентарный,date_vvod AS Дата_ввода_в_экспл,date_gar AS Дата_окон_гаран,ser_num AS Серийн,primech AS Примечание INTO tipteh,naim,invent,datevvod,dategar,sernum,prim FROM ((((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN comp ON comp.num_comp=tehnika.num_teh)INNER JOIN spr_proc ON spr_proc.num_proc=comp.num_proc)INNER JOIN spr_chast ON spr_chast.num_chast=comp.num_chast) WHERE tehnika.inv_num=p1 AND tehnika.num_tabl=1 UNION
SELECT tabl.name_tabl,spr_prin.name_prin,inv_num,date_vvod,date_gar,ser_num,primech FROM ((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN spr_prin ON spr_prin.num_prin=tehnika.num_teh) WHERE tehnika.inv_num=p2 AND tehnika.num_tabl=2 UNION
SELECT tabl.name_tabl,spr_ups.name_ups,inv_num,date_vvod,date_gar,ser_num,primech FROM ((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN spr_ups ON spr_ups.num_ups=tehnika.num_teh) WHERE tehnika.inv_num=p3 AND tehnika.num_tabl=3 UNION
SELECT tabl.name_tabl,spr_lan.name_lan,inv_num,date_vvod,date_gar,ser_num,primech FROM ((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN spr_lan ON spr_lan.num_lan=tehnika.num_teh) WHERE tehnika.inv_num=p4 AND tehnika.num_tabl=4 UNION
SELECT tabl.name_tabl,spr_teh_ofr.name_teh_ofr,inv_num,date_vvod,date_gar,ser_num,primech FROM ((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN spr_teh_ofr ON spr_teh_ofr.num_teh_ofr=tehnika.num_teh) WHERE tehnika.inv_num=p5 AND tehnika.num_tabl=5;
RETURN tipteh,naim,invent,datevvod,dategar,sernum,prim WITH resume;
end for;
END foreach;
END PROCEDURE;
вот так сработало но почему-то без AS
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE PROCEDURE search(p1 VARCHAR(15),p2 VARCHAR(15),p3 VARCHAR(15),p4 VARCHAR(15),p5 VARCHAR(15))
returning VARCHAR(20) AS tipteh,VARCHAR(50) AS naim,VARCHAR(15) AS invent,VARCHAR(20) AS datevvod,VARCHAR(20) AS dategar,VARCHAR(20) AS sernum,VARCHAR(50) AS prim;
define tipteh,naim,invent,datevvod,dategar,sernum,prim VARCHAR(50);
foreach c1 FOR
SELECT tabl.name_tabl,spr_proc.name_proc||'/'||spr_chast.name_chast,inv_num,date_vvod,date_gar,ser_num,primech INTO tipteh,naim,invent,datevvod,dategar,sernum,prim FROM ((((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN comp ON comp.num_comp=tehnika.num_teh)INNER JOIN spr_proc ON spr_proc.num_proc=comp.num_proc)INNER JOIN spr_chast ON spr_chast.num_chast=comp.num_chast) WHERE tehnika.inv_num=p1 AND tehnika.num_tabl=1 UNION
SELECT tabl.name_tabl,spr_prin.name_prin,inv_num,date_vvod,date_gar,ser_num,primech FROM ((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN spr_prin ON spr_prin.num_prin=tehnika.num_teh) WHERE tehnika.inv_num=p2 AND tehnika.num_tabl=2 UNION
SELECT tabl.name_tabl,spr_ups.name_ups,inv_num,date_vvod,date_gar,ser_num,primech FROM ((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN spr_ups ON spr_ups.num_ups=tehnika.num_teh) WHERE tehnika.inv_num=p3 AND tehnika.num_tabl=3 UNION
SELECT tabl.name_tabl,spr_lan.name_lan,inv_num,date_vvod,date_gar,ser_num,primech FROM ((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN spr_lan ON spr_lan.num_lan=tehnika.num_teh) WHERE tehnika.inv_num=p4 AND tehnika.num_tabl=4 UNION
SELECT tabl.name_tabl,spr_teh_ofr.name_teh_ofr,inv_num,date_vvod,date_gar,ser_num,primech FROM ((tehnika INNER JOIN tabl ON tabl.num_tabl=tehnika.num_tabl)INNER JOIN spr_teh_ofr ON spr_teh_ofr.num_teh_ofr=tehnika.num_teh) WHERE tehnika.inv_num=p5 AND tehnika.num_tabl=5
RETURN tipteh,naim,invent,datevvod,dategar,sernum,prim WITH resume;
END foreach;
END PROCEDURE;
|
