March 5, 2011 at 12:14 am
Hi,
This is my function. I am facing the syntax error in this function. can you please help me to solve this problem.
CREATE OR REPLACE FUNCTION ahcc_proj_budget_cpy_plan1(p_pinstance_id character varying)
RETURNS void AS
$BODY$ DECLARE
v_Process CHAR(1);
v_project_id VARCHAR(32);
v_projbudget VARCHAR(32);
v_client VARCHAR(32);
v_petty NUMERIC;
v_org VARCHAR(32);
v_mrl NUMERIC;
v_hr NUMERIC;
v_eqp NUMERIC;
v_createdby VARCHAR(32);
v_updatedby VARCHAR(32);
v_record_id VARCHAR(32);
v_ResultStr VARCHAR(120);
v_status VARCHAR(32);
v_message VARCHAR(255);
v_userid VARCHAR(32);
v_wbs_id VARCHAR(255);
v_boq_id VARCHAR(255);
v_boqitem_id VARCHAR(255);
v_positionId VARCHAR(255);
v_totalhours VARCHAR(255);
v_mproduct_id VARCHAR(255);
v_quantity VARCHAR(255);
v_hours VARCHAR(255);
v_material VARCHAR(255);
v_mquantity VARCHAR(255);
Cur_boq RECORD;
Cur_wbs RECORD;
Cur_hr RECORD;
Cur_eqp RECORD;
Cur_mrl RECORD;
BEGIN
-- Update AD_PInstance by setting IsProcessing='Y'
RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ;
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
BEGIN
select record_id,ad_user_id into v_record_id,v_userid from ad_pinstance where ad_pinstance_id=p_PInstance_ID;
select ahcc_project_id,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID,
CREATEDBY, UPDATEDBY,process
into v_project_id,v_projbudget,v_client,v_org,v_createdby,v_updatedby,v_process
from ahcc_proj_budget where ahcc_proj_budget_id=v_Record_ID;
update ahcc_proj_budget set process='N' where Process='Y';
select count(*) into v_mrl from ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
select count(*) into v_hr from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
select count(*) into v_eqp from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;
IF (v_mrl<>0 OR v_hr<>0 OR v_eqp<>0) THEN
delete from ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
delete from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
delete from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;
END IF;
select count(*) into v_mrl from ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
select count(*) into v_hr from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
select count(*) into v_eqp from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;
IF (v_mrl=0 AND v_hr=0 AND v_eqp=0) THEN
FOR Cur_wbs IN (select apwbs.ahcc_project_wbs_id as v_wbs_id, apbi.ahcc_boq_item_id as v_boq_id
from ahcc_project_boqitem apbi
left join (select ahcc_project_wbs_id from ahcc_project_wbs
where ahcc_project_wbs_id not in(select wbs_parent_id from ahcc_project_wbs)
and ahcc_project_id=v_project_id) apwbs
on apbi.ahcc_project_wbs_id=apwbs.ahcc_project_wbs_id)
LOOP
FOR Cur_boq IN(SELECT ahcc_boq_item_id into v_boqitem_id FROM connectby('ahcc_boq_item','ahcc_boq_item_id',
'boqitem_parentid','boqitem_name',Cur_wbs.v_boq_id,0 ,'/') AS
t(ahcc_boq_item_id text, boqc_parentid text, level int, branch text ,pos int))
LOOP
FOR Cur_hr IN(select abl.ka003_position_lk_id , sum(abl.nooflabors*abl.hours) as totalhours
into v_positionId, v_totalhours from ahcc_boq_labor abl
where ahcc_boq_item_id=v_boqitem_id
group by abl.ka003_position_lk_id)
LOOP
INSERT INTO AHCC_Proj_budget_hr
(
AHCC_Proj_budget_hr_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
CREATED, CREATEDBY, UPDATED, UPDATEDBY,ka003_position_lk_id,hourprice,totalprice,total_hour)
VALUES
(
GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
Cur_hr.v_positionId,0,0,Cur_hr.v_totalhours
);
END LOOP;
FOR Cur_eqp IN(select abe.m_product_id as v_mproduct_id,sum(abe.equipment_quantity) as v_quantity,sum(abe.noofhours) as v_hours
from ahcc_boq_equipment abe where ahcc_boq_item_id=v_boqitem_id
group by abe.m_product_id)
LOOP
INSERT INTO AHCC_Proj_budget_eqp
(
AHCC_Proj_budget_eqp_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
CREATED, CREATEDBY, UPDATED, UPDATEDBY,m_product_id,no_of_equipment,hour,hourprice,totalprice)
VALUES
(
GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
Cur_eqp.v_mproduct_id,Cur_eqp.v_quantity,Cur_eqp.v_hours,0,0
);
END LOOP;
FOR Cur_mrl IN(select abm.m_product_id as v_material, sum(abm.material_quantity) as v_mquantity from ahcc_boq_material abm
where ahcc_boq_item_id=v_boqitem_id
group by abm.m_product_id)
LOOP
INSERT INTO AHCC_Proj_budget_mrl
(
AHCC_Proj_budget_mrl_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
CREATED, CREATEDBY, UPDATED, UPDATEDBY,m_product_id,quantity,productprice,totalprice)
VALUES
(
GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
Cur_mrl.v_material,Cur_mrl.v_mquantity,0,0
);
END LOOP;
END LOOP;
END LOOP;
END IF;
RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ;
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ;
RETURN;
END; -- BODY
EXCEPTION
WHEN OTHERS THEN
v_ResultStr:= '@ERROR=' || SQLERRM;
RAISE NOTICE '%',v_ResultStr ;
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
RETURN;
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION ahcc_proj_budget_cpy_plan(character varying) OWNER TO tad;
ERROR: syntax error at or near "$1"
LINE 1: (select apwbs.ahcc_project_wbs_id as $1 , apbi.ahcc_boq_it...
^
QUERY: (select apwbs.ahcc_project_wbs_id as $1 , apbi.ahcc_boq_item_id as $2 from ahcc_project_boqitem apbi left join (select ahcc_project_wbs_id from ahcc_project_wbs where ahcc_project_wbs_id not in(select wbs_parent_id from ahcc_project_wbs) and ahcc_project_id= $3 ) apwbs on apbi.ahcc_project_wbs_id=apwbs.ahcc_project_wbs_id)
CONTEXT: SQL statement in PL/PgSQL function "ahcc_proj_budget_cpy_plan1" near line 68
********** Error **********
ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "ahcc_proj_budget_cpy_plan1" near line 68
March 5, 2011 at 11:20 pm
Hi,
There is a slim chance of anyone here helping you as this forum focuses on MS SQL Server.
Perhaps you would be better served posting your question on an Oracle/PL-Sql centric forum.
Good luck.
March 7, 2011 at 2:24 am
That "slim chance" is here:
"$BODY$" - what is that ? Compiler probably asks the same 🙂 Get rid of that.
Functions cannot change database data (UPDATE/DELETE/INSERT are not allowed).
Using loops is bad practice. You can do insert of many rows at one with INSERT-SELECT construct instead of INSERT-VALUS.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply