Oracle Sql, How do I group day values into a months summary

Question

wondering if someone can assist.

I'm trying to take the values in a date column, the day values and grouping the days into a month to get a summary of total pallets received for the month.

I'm getting an error, 'ORA-01722: invalid number' when adding to_char(conf_date, 'MM-YYYY') to my select/group statement.

Thanks in advance!

My Current SQL

with pallets_received as (
select a.comp_code, 
       a.cust_code, 
       a.rcpt_num, 
       to_char(a.rcpt_conf_date,'mm-dd-yyyy')conf_date,
       sum(b.rcpt_loc_qty)/(c.item_qty_bkd_qty) pallets
from e_rcpt_h a
left join e_rcpt_d5D1 b
       on a.comp_code=b.comp_code 
       and a.rcpt_num=b.rcpt_num
left join m_item_d1 c
       on b.comp_code=c.comp_code 
       and b.cust_code=c.cust_code 
       and b.invt_lev1=c.item_code
where a.comp_code='T3'
       and c.item_qty_bkd_lev_num=1
       and a.cust_code='101029'
       and a.flow_pros_code='CORE'
       and trunc(a.rcpt_conf_date) >= to_date('02-01-2020','mm-dd-yyyy')
group by a.comp_code, 
         a.cust_code, 
         a.rcpt_num, 
         a.rcpt_conf_date, 
         c.item_qty_bkd_qty
order by conf_date
)
select comp_code, 
       cust_code, 
       conf_dateas month, 
       sum(ceil(pallets)) pallets
from pallets_received
group by comp_code, 
         cust_code, 
         conf_date
order by conf_date

Table

comp_code | cust_code | conf_date  | pallets
---------------------------------------------
T3          101029      03-06-2020   2
T3          101029      03-09-2020   8
C4          101029      04-05-2020   2
C4          101029      04-05-2020   8

Output I'm trying to achieve

comp_code | cust_code | month   | pallets
---------------------------------------------
T3          101029      03-2020   10
C4          101029      04-2020   10

is conf_date a DATE type in the table? Also, please indent your code so it is easier to read. thanks — OldProgrammer
Hi OldProgrammer, sorry about the indent, I don't code much and not really sure what to indent, it would probably just be random indents, lol. The original date from table e_rcpt_h shows as 2020-03-06 00:00. So I converted that date using the sql in With. — mrdiu

EdStevens EdStevens · Accepted Answer · 2020-06-30T19:03:02

CONF_DATE is just an alias for the string result of 'to_char(a.rcpt_conf_date,'mm-dd-yyyy')' Therefore CONF_DATE is effectively just a string. Yet, in your GROUP BY, you are feeding it to TO_CHAR. This forces oracle to first do an implied TO_DATE, using the controlling value of NLS_DATE_FORMAT. That is almost guaranteed to return an error.

group by comp_code, 
         cust_code, 
         trunc(rcpt_conf_date,'MON')

(yes, you need to learn to format your code .. for your own sanity as well as that of others who have to read it.)

Oracle Sql, How do I group day values into a months summary

1 Answers