insert and update statmetns in a procedure

[Crystalboy1]

HI,

I want to write a store procedure in PLSQL, in the body of the procedure, i will be inserting data into a table, then will updating other fields, when i run the script, it gives me an error dat SQL command not properly ended, if i put a ; in it then its treat ever part as a different script.
help please


insert into Reports_Loan_Periods(overFive)
values(nvl((select NVL(Sum(A.PWLB_ORIGINAL_PRINCIPAL), 0)
from ADVANCES_OPT A
WHERE (A.PWLB_ADVANCE_DATE >= 20080601 AND
A.PWLB_ADVANCE_DATE<= 20080630 and
a.pwlb_loan_period_months<=60)
),0))

update Reports_Loan_Periods set Fiveto10=
(nvl((select NVL(Sum(A.PWLB_ORIGINAL_PRINCIPAL), 0)
from ADVANCES_OPT A
WHERE (A.PWLB_ADVANCE_DATE >= 20080601 AND
A.PWLB_ADVANCE_DATE<= 20080630 and
a.pwlb_loan_period_months>60 and a.pwlb_loan_period_months<=120)
),0))

 

[LKBrwnDBA]

You have your NVL() all wrong, try this:

INSERT INTO reports_loan_periods
            (overfive)
(SELECT SUM (NVL(a.pwlb_original_principal, 0))
   FROM advances_opt a
  WHERE ( a.pwlb_advance_date >= 20080601
    AND a.pwlb_advance_date <= 20080630
    AND a.pwlb_loan_period_months <= 60);

PS: Your insert seems lacking some primary key or some information other than just a SUM()!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Also, you have the same issue with the update...



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Crystalboy1]

no the Script is fine, it give me the right results i want. NVL is also in right palce according to the business logic. the problem is like T-sql i want to have these insert and updates blocks in 1 stored procedure but i dont know how to do this in PLSQL ?

 

[amoise]

Hello,
I'm new to db2 and I'm tring to write a stored procedure to select all the records from the history table with the latest effective date, and loads them into the active table (See below). I'm seeking help with the syntax as I keep getting errors. Thanks in advance.

CREATE PROCEDURE ED.SP_LOAD_ACTIVE_TABLE
(IN TBL_NAME VARCHAR(150), IN PRIMARY_KEY_NAME INT, IN SRC_SYS_CD VARCHAR(20), SCHEMA_NAME VARCHAR(10))
LANGUAGE SQL
DYNAMIC RESULT SETS 1

--To be able to rollback data if needed
BEGIN

-- Local variable declaration
DECLARE @TBL_NAME VARCHAR(150);

DECLARE @PRIMARY_KEY_NAME INT;

DECLARE @SRC_SYS_CD VARCHAR(150);

DECLARE @SCHEMA_NAME VARCHAR(150);

INSERT INTO @SCHEMA_NAME.@TBL_NAME
SELECT HISTORY.* FROM @SCHEMA_NAME.@TBL_NAME_HST as HISTORY
WHERE @PRIMARY_KEY_NAME IN
(SELECT @PRIMARY_KEY_NAME, MAX(EFF_DT)as MAXDATE
FROM @SCHEMA_NAME.@TBL_NAME
GROUP BY @PRIMARY_KEY_NAME)as CURR
AND HIST.@PRIMARY_KEY_NAME = CURR.@PRIMARY_KEY_NAME
AND HIST.EFF_DT = CURR.MAXDATE
AND @SRC_SYS_CD IN ('AL','PS','ME','MP','SS')
END

 

[LKBrwnDBA]

This is an Oracle forum, maybe if you posted in the DB2 forum you would get a better answer.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb