sql - oracle - 案例陈述和分组依据

Question

您好 – 我需要您协助解决以下问题。我正在尝试运行下面的查询以进行分组，但出现以下错误:

ORA-00904:” SALES_ID_CO”:invalid identifier

你能帮忙吗？非常感谢。

Select
count(1),
sales_date,
CASE 
WHEN sales_id like '1%'
THEN substr(sales_id,2,6)
WHEN sales_id  like '3%'
THEN substr(sales_id ,2,6)
WHEN sales_id  like '4%'
THEN substr(sales_id ,2,6)
WHEN sales_id  like '5%'
THEN substr(sales_id ,2,6)
WHEN sales_id  like '9%'
THEN substr(sales_id ,1,6)
WHEN sales_id  like '70%' OR sales_id  like '90%' OR sales_id  like '31%'
THEN substr(sales_id ,2,6)
END AS SALES_ID_CO,

case
WHEN sales_branch like '10%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branch like '200%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branch like '300%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branch like '456%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
END AS SALES_BRANCH

from tbl_sales_cde

where sales_country IN (‘USA’,’ASIA’,’EU’) 
group by SALES_ID_CO, SALES_BRANCH,SALES_DATE

Answer 1

Oracle 不支持 group by 子句中的列别名。使用子查询:

SELECT SALES_ID_CO, SALES_BRANCH,SALES_DATE, COUNT(*)
FROM (Select sales_date,
             (CASE  WHEN sales_id like '1%'
                    THEN substr(sales_id,2,6)
                    WHEN sales_id  like '3%'
                    THEN substr(sales_id ,2,6)
                    WHEN sales_id  like '4%'
                    THEN substr(sales_id ,2,6)
                    WHEN sales_id  like '5%'
                    THEN substr(sales_id ,2,6)
                    WHEN sales_id  like '9%'
                    THEN substr(sales_id ,1,6)
                    WHEN sales_id  like '70%' OR sales_id  like '90%' OR sales_id  like '31%'
               END) AS SALES_ID_CO,
              (case WHEN sales_branchlike '10%' AND sales_code<> '678 '
                    THEN substr(sales_branch,2,6)
                    WHEN sales_branchlike '200%' AND sales_code<> '678 '
                    THEN substr(sales_branch,2,6)
                    WHEN sales_branchlike '300%' AND sales_code<> '678 '
                    THEN substr(sales_branch,2,6)
                    WHEN sales_branchlike '456%' AND sales_code<> '678 '
                    THEN substr(sales_branch,2,6)
                END) AS SALES_BRANCH
       from tbl_sales_cde
       where sales_country IN ('USA', 'ASIA', 'EU') 
      ) s
group by SALES_ID_CO, SALES_BRANCH,SALES_DATE;

注意:您在 where 子句中也有单引号，但这可能是一个复制问题。