CODEWITHSUNDEEP
sqloracle-database
Slim
Slim

Reputation: 1744

Can I assign a value to a variable inside of an update statement?

I'm kinda new to Oracle and I was wondering, if I can assign a value to a variable inside of an update statement case.

I have this code so far:

PROCEDURE insert_repayment_data_inst(

  date_of_payment_l             IN credit_mo_repayment.date_of_payment%TYPE,
  amoount_to_pay_l              IN credit_mo_repayment.ammount_to_pay%TYPE,
  client_number_l               IN credit_mo_repayment.client_number%TYPE,
  is_paid_l                     IN credit_mo_repayment.is_paid%TYPE, 
  month_l                       IN credit_mo_repayment.is_paid%TYPE,
  year_l                        IN credit_mo_repayment.is_paid%TYPE,
  ending_balance_l              IN credit_mo_repayment.ending_balance%TYPE,
  tests                         IN credit_mo_repayment.ending_balance%TYPE
) AS
  BEGIN

  select monthly_installment into tests
  from credit_mo_repayment
   WHERE (to_char(date_of_payment, 'mm') = to_char(to_date(date_of_payment_l), 'mm'))
         AND (to_char(date_of_payment, 'yy') = to_char(to_date(date_of_payment_l), 'yy'));

  UPDATE credit_mo_repayment
  SET

  monthly_installment = CASE 
                          WHEN (Monthly_installment - amoount_to_pay_l >= 0)  THEN Monthly_installment - amoount_to_pay_l
                          WHEN (Monthly_installment - amoount_to_pay_l < 0) THEN 0
                        END,

  ending_balance = CASE
                     WHEN (Monthly_installment - amoount_to_pay_l < 0) THEN ending_balance - ABS(Monthly_installment - amoount_to_pay_l) tests:= ending_balance - ABS(Monthly_installment - amoount_to_pay_l)
                     WHEN (Monthly_installment - amoount_to_pay_l >= 0)  THEN ending_balance
                   END,
  ammount_to_pay = ammount_to_pay + amoount_to_pay_l
  WHERE (to_char(date_of_payment, 'mm') = to_char(to_date(date_of_payment_l), 'mm'))
         AND (to_char(date_of_payment, 'yy') = to_char(to_date(date_of_payment_l), 'yy'));                                  
END insert_repayment_data_inst;

As you can see I'm trying to assign the value to the variable inside of the case like this tests:= ending_balance - ABS(Monthly_installment - amoount_to_pay_l)

But without luck.

I have also tried to assign the monthly_installment value to the tests variable and then just to do the math, but I also had no luck.

 select monthly_installment into tests
 from credit_mo_repayment
 WHERE (to_char(date_of_payment, 'mm') = to_char(to_date(date_of_payment_l), 'mm'))
        AND (to_char(date_of_payment, 'yy') = to_char(to_date(date_of_payment_l), 'yy'));

I know that I'm missing something in the syntax, but I'm not able to spot it.

Upvotes: 0

Views: 1099

Answers (2)

Armunin
Armunin

Reputation: 996

First of all you have to define your target variable as an OUT-Parameter or a new variable. For example:

PROCEDURE insert_repayment_data_inst(

  date_of_payment_l             IN credit_mo_repayment.date_of_payment%TYPE,
  amoount_to_pay_l              IN credit_mo_repayment.ammount_to_pay%TYPE,
  client_number_l               IN credit_mo_repayment.client_number%TYPE,
  is_paid_l                     IN credit_mo_repayment.is_paid%TYPE, 
  month_l                       IN credit_mo_repayment.is_paid%TYPE,
  year_l                        IN credit_mo_repayment.is_paid%TYPE,
  ending_balance_l              IN credit_mo_repayment.ending_balance%TYPE,
  tests                         IN credit_mo_repayment.ending_balance%TYPE
) AS
-- new variable
  monthly_payments_output credit_mo_repayment.ending_balance%TYPE;
  BEGIN

Second: you can try the RETURNING-clause as described here: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#BABHDGIG and here
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#BABEBDIA
Which should result in something like this (untested):

UPDATE credit_mo_repayment
  SET

  monthly_installment = CASE 
                          WHEN (Monthly_installment - amoount_to_pay_l >= 0)  THEN Monthly_installment - amoount_to_pay_l
                          WHEN (Monthly_installment - amoount_to_pay_l < 0) THEN 0
                        END,

  ending_balance = CASE
                     WHEN (Monthly_installment - amoount_to_pay_l < 0) THEN ending_balance - ABS(Monthly_installment - amoount_to_pay_l)
                     WHEN (Monthly_installment - amoount_to_pay_l >= 0)  THEN ending_balance
                   END,
  ammount_to_pay = ammount_to_pay + amoount_to_pay_l
  WHERE (to_char(date_of_payment, 'mm') = to_char(to_date(date_of_payment_l), 'mm'))
         AND (to_char(date_of_payment, 'yy') = to_char(to_date(date_of_payment_l), 'yy'))
RETURNING monthly_installment INTO tests;

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

You can do it like this:

UPDATE credit_mo_repayment SET
monthly_installment = 
    CASE 
    WHEN (Monthly_installment - amoount_to_pay_l >= 0) THEN Monthly_installment - amoount_to_pay_l
    WHEN (Monthly_installment - amoount_to_pay_l < 0) THEN 0
    END,
ending_balance = 
    CASE
    WHEN (Monthly_installment - amoount_to_pay_l < 0) THEN ending_balance - ABS(Monthly_installment - amoount_to_pay_l) 
    WHEN (Monthly_installment - amoount_to_pay_l >= 0) THEN ending_balance
    END,
ammount_to_pay = ammount_to_pay + amoount_to_pay_l
WHERE (TO_CHAR(date_of_payment, 'mm') = TO_CHAR(TO_DATE(date_of_payment_l), 'mm'))
    AND (TO_CHAR(date_of_payment, 'yy') = TO_CHAR(TO_DATE(date_of_payment_l), 'yy'))
RETURNING  
    CASE
   WHEN (Monthly_installment - amoount_to_pay_l < 0) THEN ending_balance - ABS(Monthly_installment - amoount_to_pay_l)
    ELSE NULL
    END         
INTO tests;

Not tested - I am not sure if CASE expression is allowed in RETURNING clause.

Upvotes: 1

Related Questions