Use a case with data from a table and have the results of that case save them in a table

Question

Use a case with data from a table and have the results of that case save them in a table

Navigation

#1 by (1 votes)

0

I have 1 table called 'CFACTURACOMISION' which has the columns. CLFILIAL, CLCLIENTE, CLASESOR, FOFACTURA, DACHAFACTURA, MNFACTURA, MNDESCUENTO MNIVA, MNTOTAL, FEVENCIMIENTO, CONDICIONESDEPAGO, PROMVENTA.

The main objective of this table is to capture the invoices and then verify that all payments were made and then give a commission to the seller who made that sale.

Firstly, I make the sum of all the payments made to an invoice, obtaining the data from the table 'BILL_PAYMENTS'.

Select FACTURA_PAGOS.FOFACTURA, sum(factura_pagos.imp_cobro) from factura_pagos, CFACTURACOMISION where CFACTURACOMISION.FOFACTURA = FACTURA_PAGOS.FOFACTURA AND  FACTURA_PAGOS.FECHA_DEP<= CFACTURACOMISION.FEVENCIMIENTO group by FACTURA_PAGOS.FOFACTURA;

After that I would have to make an IF to verify if all payments are = MNFACTURA in case the invoice was paid to run this case that I have, I currently have it with default values, so I need you to take the data from the table.

SET SERVEROUTPUT ON
DECLARE
MNDESCUENTO NUMBER:=0;
COMISION NUMBER:=0;
CONDICIONESDEPAGO NUMBER:=0;
PROMVENTA NUMBER :=0;
BEGIN
CONDICIONESDEPAGO:=4;
PROMVENTA:=30000;
MNDESCUENTO:=12;
CASE
WHEN PROMVENTA BETWEEN '0' AND '30000' AND CONDICIONESDEPAGO BETWEEN '0' AND '4' AND  MNDESCUENTO>0 AND MNDESCUENTO<=12 THEN COMISION:=.05;
WHEN PROMVENTA BETWEEN '0' AND '30000' AND CONDICIONESDEPAGO BETWEEN '5' AND '35' AND MNDESCUENTO>0 AND MNDESCUENTO<=12 THEN COMISION:=.03;
WHEN PROMVENTA BETWEEN '30000' AND '100000' AND CONDICIONESDEPAGO BETWEEN '0' AND '35' AND MNDESCUENTO>0 AND MNDESCUENTO<=12 THEN COMISION:=.03;
WHEN PROMVENTA BETWEEN '0' AND '100000' AND CONDICIONESDEPAGO BETWEEN '0' AND '35' AND MNDESCUENTO>12 AND MNDESCUENTO<=13 THEN COMISION:=.03;
WHEN PROMVENTA BETWEEN '0' AND '1000000' AND CONDICIONESDEPAGO BETWEEN '0' AND '35' AND MNDESCUENTO>13 AND MNDESCUENTO<=17.5 THEN COMISION:=.02;
WHEN PROMVENTA BETWEEN '30001' AND '100000' AND CONDICIONESDEPAGO BETWEEN '0' AND '35' AND MNDESCUENTO>17.5 AND MNDESCUENTO <=20 THEN COMISION:=.01;
WHEN PROMVENTA BETWEEN '100001' AND '10000000' AND CONDICIONESDEPAGO BETWEEN '0' AND '36' AND MNDESCUENTO>17.5 AND MNDESCUENTO <=20 THEN COMISION:=.01;
WHEN PROMVENTA BETWEEN '0' AND '10000000' AND  CONDICIONESDEPAGO BETWEEN '36' AND '45' AND MNDESCUENTO>=1 AND MNDESCUENTO <=7 THEN COMISION:=.03;
WHEN PROMVENTA BETWEEN '0' AND '10000000' AND CONDICIONESDEPAGO BETWEEN '46' AND '60' THEN COMISION:=.03;
END CASE;
DBMS_OUTPUT.PUT_LINE( ' COMISIÓN DEL ' ||COMISION ||'%');
END;

I do not know if you can help me, now the commission returns to me through a DBMS but without validating the payments.

mysql oracle oracle-11g oracle-sqldeveloper toad

asked by Carlos Valentin 20.03.2018 в 19:36

source

1 answer

Error due to insufficient memory to run algorithm in ApacheSpark Dependent php combos postgres

score 1 · Answer 1

Carlos, but I do not see that you are adding the values, you are only going through a case. You could do the following if you want the result of the commission.

Put the SUM sum (case ...) and group the other variables.

Load your select in a cursor, move the cursor and in the path, add it to the case and load the value in a parameter.

Your question is not very clear, but I think the latter is what you are looking for.

CURSOR C_MICURSOR IS
SELECT * FROM MITABLA
V_COMISION := 0;

BEGIN 
    FOR C IN C_MICURSOR 
    LOOP
    /* Hacer lo que se necesite aquí */
    V_COMISION := V_COMISION + (Nuevos valores)
    END LOOP;
    COMMIT;

    EXCEPTION 
    WHEN OTHERS THEN
    dbms_output.put_line('Error en la transaccion:'||SQLERRM);
    ROLLBACK;
END;