对Oracle中提取的值进行分组

Question

我正在尝试对提取值进行分组。我通过提取案例来创建本季度的开始。我正在尝试将此标识符添加到我的团购中，当在oracle服务器上的Toad中运行时，我收到错误" Sales_Quarter“无效标识符。

with c_prod as (select prod_id_pri, prod_desc,prod_num from rm_mrb.V_PRODUCT_PROD_LIST)
select 'Excluded' as Status, tr.product_id, prod.prod_desc,
 sum(tr.QUANTITY) QUANTITY,  sum(total_Prescriptions) Total_#_OF_PRESCRIPTIONS, count(*) Claims
,CASE 
            When 
                EXTRACT(MONTH FROM Tr.Fill_Date) IN ( 
                    '1', 
                    '2', 
                    '3' )
                THEN
                    '01-01-'||EXTRACT(Year FROM Tr.Fill_Date)
            When 
                EXTRACT(MONTH FROM Tr.Fill_Date) IN ( 
                    '4', 
                    '5', 
                    '6' )
                THEN
                    '04-01-'||EXTRACT(Year FROM Tr.Fill_Date)
            When 
                EXTRACT(MONTH FROM Tr.Fill_Date) IN ( 
                    '7', 
                    '8', 
                    '9' )
                THEN
                    '07-01-' ||EXTRACT(Year FROM Tr.Fill_Date)
            ELSE '10-01-'||EXTRACT(Year FROM Tr.Fill_Date)
    End As Sales_Quarter
  from VALIUSER.ivd_transaction_record_details RD, valiuser.IVD_TRANSACTION_RECORDS TR
  left join c_prod prod on (tr.product_id=prod.prod_id_pri)
  where TR.transaction_record_id=RD.transaction_record_id
  and status='1'
  and tr.File_id in (1765)
  group by Status,Product_id,prod.prod_desc,Sales_Quarter

Answer 1

您可以按年份和季度进行分组

group by Status,Product_id,prod.prod_desc,EXTRACT(Year FROM Tr.Fill_Date),EXTRACT(Quarter FROM Tr.Fill_Date)

您还可以使用以下命令消除case语句：

to_string(
 ((EXTRACT(Quarter FROM Tr.Fill_Date)-1)*3)+1) 
|| '-01-' 
|| EXTRACT(Year FROM Tr.Fill_Date)

Answer 2

您不能像在ORDER BY中那样使用列别名进行GROUP BY。

或者在group by子句中重复Sales_Quarter的整个表达式，或者在WITH子句中计算它，然后选择它并按它分组。我将在下面演示这一方法：

with c_prod as (select prod_id_pri, prod_desc,prod_num from rm_mrb.V_PRODUCT_PROD_LIST),
extracted as (
select 'Excluded' as Status, tr.product_id, prod.prod_desc,
 tr.QUANTITY,  total_prescriptions
,CASE 
            When 
                EXTRACT(MONTH FROM Tr.Fill_Date) IN ( 
                    '1', 
                    '2', 
                    '3' )
                THEN
                    '01-01-'||EXTRACT(Year FROM Tr.Fill_Date)
            When 
                EXTRACT(MONTH FROM Tr.Fill_Date) IN ( 
                    '4', 
                    '5', 
                    '6' )
                THEN
                    '04-01-'||EXTRACT(Year FROM Tr.Fill_Date)
            When 
                EXTRACT(MONTH FROM Tr.Fill_Date) IN ( 
                    '7', 
                    '8', 
                    '9' )
                THEN
                    '07-01-' ||EXTRACT(Year FROM Tr.Fill_Date)
            ELSE '10-01-'||EXTRACT(Year FROM Tr.Fill_Date)
    End As Sales_Quarter
  from VALIUSER.ivd_transaction_record_details RD, valiuser.IVD_TRANSACTION_RECORDS TR
  left join c_prod prod on (tr.product_id=prod.prod_id_pri)
  where TR.transaction_record_id=RD.transaction_record_id
  and status='1'
  and tr.File_id in (1765)
)
SELECT 'Excluded' as Status, product_id, prod_desc, sales_quarter,
 sum(QUANTITY) QUANTITY,  sum(total_Prescriptions) Total_#_OF_PRESCRIPTIONS, count(*) Claims
 from extracted
  group by Status,Product_id,prod.prod_desc,Sales_Quarter