Add a column with data in Varchar2 format

Asked

Viewed 289 times

1

I want to sum up the column Value that is of the type Varchar2, I know that to use the SUM() I also have to use the GROUP BY, only that I am not able to make the sum. I don’t know which fields I have to put on GROUP BY.

SELECT DISTINCT z.sales_office AS kam, z.customer,g.sales_order,
d.nfnum, d.vlr_liq_nf AS valor FROM TPL_GUARDA g 
LEFT JOIN TPL_COLETAS c     ON g.SALES_ORDER   = c.SALES_ORDER 
LEFT JOIN humo h            ON h.HANDLING_UNIT = g.handling_unit 
LEFT JOIN DANFE_DELIVERY dd ON dd.delivery     = h.delivery 
LEFT JOIN danfe d           ON d.nfnum         = dd.nfnum 
LEFT JOIN zzcustmon z       ON z.sales_order   = g.sales_order 
where c.dt_exp is null and d.nfnum is not null and z.delivery is not null 
GROUP BY
sql database oracle select

  • What do you want as a result? Do you want the sum of each customer’s Value? Do you want the sum of each office’s Value?

  • I want to sum all the values of the column VALUE(d.vlr_liq.nf). I did the same as the friend commented right down, but it didn’t work.

2 answers

1

I managed to solve the problem, in the oracle, the comma treatment is different, comma does not mean comma. It was giving the error Invalid Number, because of the comma, the oracle could not do the conversion. So I used replace to replace the comma with a dot, and I used another select so that I didn’t have to use group by. Follow the code working:

SELECT SUM(REPLACE(valor,',','.')) FROM (SELECT DISTINCT z.sales_office AS 
kam, z.customer,g.sales_order,
d.nfnum, d.vlr_liq_nf AS valor FROM TPL_GUARDA g 
LEFT JOIN TPL_COLETAS c     ON g.SALES_ORDER   = c.SALES_ORDER 
LEFT JOIN humo h            ON h.HANDLING_UNIT = g.handling_unit 
LEFT JOIN DANFE_DELIVERY dd ON dd.delivery     = h.delivery 
LEFT JOIN danfe d           ON d.nfnum         = dd.nfnum 
LEFT JOIN zzcustmon z       ON z.sales_order   = g.sales_order 
where c.dt_exp is null and d.nfnum is not null and z.delivery is not null)

0

Considering you need to add a column that’s kind of VARCHAR, need to convert the column to this.

Then, in the GROUP BY need to put all other columns that are not being summed.

In your case it would be something like this:

SELECT      z.sales_office                          AS kam
        ,   z.customer
        ,   g.sales_order
        ,   d.nfnum
        ,   SUM(TO_NUMBER(d.vlr_liq_nf, '9999.99')) AS valor 
FROM        TPL_GUARDA      g 
LEFT JOIN   TPL_COLETAS     c   ON g.SALES_ORDER   = c.SALES_ORDER 
LEFT JOIN   humo            h   ON h.HANDLING_UNIT = g.handling_unit 
LEFT JOIN   DANFE_DELIVERY  dd  ON dd.delivery     = h.delivery 
LEFT JOIN   danfe           d   ON d.nfnum         = dd.nfnum 
LEFT JOIN   zzcustmon       z   ON z.sales_order   = g.sales_order 
WHERE       c.dt_exp    IS NULL 
        AND d.nfnum     IS NOT NULL 
        AND z.delivery  IS NOT NULL 
GROUP BY    z.sales_office
        ,   z.customer
        ,   g.sales_order
        ,   d.nfnum

Take a look at the method TO_NUMBER to figure out which format to use, taking into account the format in the column d.vlr_liq_nf.

https://www.techonthenet.com/oracle/functions/to_number.php

  • Failed, error appeared ORA-01722: invalid number

  • This is because its value in VARCHAR cannot be converted to a number. I will edit the answer so I can test it differently.

  • Thank you very much, I’ll be waiting

  • Done! Experiment with the correct format in the method TO_NUMBER and see if it works.

  • Still keeps showing the same error.

  • What is the format of the value in the column d.vlr_liq_nf?

  • It is of type VARCHAR2. This is a value that has in the column: 44986,54

  • Thank you very much @Joaomartins, you helped a lot.

  • You are welcome, always at your beck and call! Give a U-TURN in your reply if you have helped. Thank you.

Show 4 more comments

Browser other questions tagged sql database oracle select

You are not signed in. Login or sign up in order to post.