CODEWITHSUNDEEP
sqloracle-database
DIVYANSHU SINGH
DIVYANSHU SINGH

Reputation: 27

Unable to calculate the costing amount

CREATE TABLE Purchase_payment
(  
     supplier_id int 
         foreign key references purchase_supplier_master_details(supplier_id)
                 on delete cascade
                 on update cascade,
     stock_name varchar(25),
     QUANTITY INT NOT NULL,
     Currency_type VARCHAR(15),-----FOR BOTH NATIONAL AND INTERNATIONAL SUPPLIER
     COSTING_Amount INT NOT NULL,
     INTERVALS INT,
     PERMONTH_PAYMENTS INT,
     PAYMENT_MODE VARCHAR(25) NOT NULL,
     PAID VARCHAR(5),

     CONSTRAINT PAID CHECK (PAID = 'YES' OR PAID = 'NO')
)

My query:

select 
    supplier_id,
    stock_name,
    QUANTITY,
    Currency_type,
    COSTING_Amount as "Discounted_amount",
    case
       when  supplier_id= 41256 and Currency_type ='RUPEE' 
          then COSTING_Amount=(COSTING_Amount-0.25*COSTING_Amount) as "Discounted_amount",
       when supplier_id=41257 and Currency_type='RUPEE'
          then COSTING_Amount=(COSTING_Amount-0.50*COSTING_Amount) as "Discounted_amount"
       else 'no discount'
    end 
from Purchase_payment

This query is valid only when currency is rupee. In purchase table there is also euro and pound so different for scenario can you add all currency conversion in single case statement?

Upvotes: 1

Views: 57

Answers (2)

APC
APC

Reputation: 146329

There seems to be a problem with your data model. It's bad practice to have supplier IDs hardcoded like that; probably true for the rates as well. What you should do is have a separate table to hold the values. Something like this:

CREATE TABLE supplier_discount
(  
     supplier_id int 
         foreign key references purchase_supplier_master_details(supplier_id)
                 on delete cascade,
     Currency_type VARCHAR(15),
     discount_rate number);

Include this table in your query, using an outer join (to handle suppliers who don't have discounts for a given currency). Note that it's also bad practice to return numbers and strings in the same column, so my CASE statement returns a zero when there is no discount to be made.

select 
    pp.supplier_id,
    pp.stock_name,
    pp.quantity,
    pp.currency_type,
    pp.costing_amount,
    case
       when sd.discount_rate is not null then 
       then pp.costing_amount - (sd.discount_rate * pp.costing_amount)
       else 0 
       end as discounted_amount
from purchase_payment pp
left outer join supplier_discount sd
   on sd.supplier_id = pp.supplier_id
   and sd.currency_type = pp.currency_type

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

Your case syntax is wrong and that's why couldn't be able to calculate the costing amount

select supplier_id,stock_name,
       QUANTITY,Currency_type,COSTING_Amount,
       case 
          when  supplier_id= 41256 and Currency_type ='RUPEE'  then COSTING_Amount-0.25*COSTING_Amount
          when supplier_id=41257 and Currency_type ='RUPEE'  then COSTING_Amount-0.50*COSTING_Amount 
          else 0 
       end as "Discounted_amount"
from Purchase_payment

Upvotes: 1

Related Questions