Add a column with data in Varchar2 format [duplicate]

Question

Add a column with data in Varchar2 format [duplicate]

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I want to add the sum of the Value column that is of type Varchar2, I know that to use SUM () I have to use GROUP BY as well, but I can not do the summation. I do not know what fields I have to put in 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 oracle select

asked by anonymous 26.09.2018 / 14:06

2 answers

update sql relating a column with external value How to make a box and a box in this box

score 1 · Answer 1

I was able to solve the problem, in oracle, the comma is different, comma does not mean comma. It was giving the Invalid Number error, because of the comma, oracle could not do the conversion. So I used replace to replace the comma by one point, and I used another select so I did not have to use group by. Here is 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)

score 0 · Answer 2

Given that you need to add a column that is of type VARCHAR , you need to convert the column to that.

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

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 TO_NUMBER method to see what format you should use, given the format you have in the d.vlr_liq_nf column.

link