如何使用案例分组？

the query works well but when iam adding group by it gives me [Error] ORA-01427 here is the main query

查询运行良好,但当我添加组给它[错误] ORA-01427这里是主查询

 SELECT DISTINCT Contract_number,
              area_number,
              area_name,
              ADVANCE_PAY,
              Postponed_Amount,
              extract_number,
              total
FROM (SELECT xxr.Contract_num Contract_number,
             xxr.p_area_no area_number,
             xxr.p_area_name area_name,
             xxr.ADVANCE_PAY ADVANCE_PAY,
             xxr.DEFERRED_BOOST Postponed_Amount,
             xxr.release_num extract_number,

and here is the case statement :

以下是案例陈述:

 (SELECT DISTINCT
                     CASE
                        WHEN :p_item_code IS NOT NULL
                        THEN
                           TOTAL_AMOUNT
                        WHEN :p_item_code IS NULL
                        THEN
                           ( (SELECT NVL (SUM (TOTAL_AMOUNT), 0)
                                FROM XXEXTRACT.XXNATGAS_REALSES_LINES
                               WHERE XXEXTRACT.XXNATGAS_REALSES.release_id =
                                        XXEXTRACT.XXNATGAS_REALSES_LINES.release_id))
                        ELSE
                           NULL
                     END
                FROM XXEXTRACT.XXNATGAS_REALSES_LINES xxrl,
                     XXEXTRACT.XXNATGAS_REALSES
               WHERE     1 = 1
                     AND xxrl.release_id =
                            XXEXTRACT.XXNATGAS_REALSES.release_id)
                AS total

and here is the from part :

这是从部分:

  FROM XXEXTRACT.XXNATGAS_REALSES_LINES xxrl,
             XXEXTRACT.XXNATGAS_REALSES xxr
       WHERE 1 = 1 
       AND xxrl.release_id = xxr.release_id
       AND xxr.release_date >= NVL (:p_date_from, xxr.release_date)
             AND xxr.release_date <= NVL (:p_date_to, xxr.release_date)
             AND xxr.Contract_num = NVL (:p_cont_num, xxr.Contract_num)
             AND xxr.vendor_id = NVL (:p_ven_id, xxr.vendor_id)
             AND xxr.vendor_site_id = NVL (:p_site_id, xxr.vendor_site_id)

            )

and here is the group by :

这是小组:

GROUP BY Contract_number,
     area_number,
     area_name,
     ADVANCE_PAY,
     Postponed_Amount,
     extract_number,
     total;

these is the full query so please any help

这些是完整的查询,所以请任何帮助

1 个解决方案

#1

For sure I couldn't understand very well your query. You could improve your post for next time.

当然,我无法理解你的查询。您可以在下次改进帖子。

As answer, I think you should encapsulate your select statment and group by using your select as subquery. It is not the best approach but it may works fine.

作为回答,我认为您应该使用select as子查询来封装您的选择参数和组。这不是最好的方法,但它可能工作正常。

select *
from (
    select distinct Contract_number
        ,area_number
        ,area_name
        ,ADVANCE_PAY
        ,Postponed_Amount
        ,extract_number
        ,total
    from (
        select xxr.Contract_num Contract_number
            ,xxr.p_area_no area_number
            ,xxr.p_area_name area_name
            ,xxr.ADVANCE_PAY ADVANCE_PAY
            ,xxr.DEFERRED_BOOST Postponed_Amount
            ,xxr.release_num extract_number
            ,(
                select distinct case 
                        when :p_item_code is not null
                            then TOTAL_AMOUNT
                        when :p_item_code is null
                            then (
                                    (
                                        select NVL(SUM(TOTAL_AMOUNT), 0)
                                        from XXEXTRACT.XXNATGAS_REALSES_LINES
                                        where XXEXTRACT.XXNATGAS_REALSES.release_id = XXEXTRACT.XXNATGAS_REALSES_LINES.release_id
                                        )
                                    )
                        else null
                        end
                from XXEXTRACT.XXNATGAS_REALSES_LINES xxrl
                    ,XXEXTRACT.XXNATGAS_REALSES
                where 1 = 1
                    and xxrl.release_id = XXEXTRACT.XXNATGAS_REALSES.release_id
                ) as total
        from XXEXTRACT.XXNATGAS_REALSES_LINES xxrl
            ,XXEXTRACT.XXNATGAS_REALSES xxr
        where 1 = 1
            and xxrl.release_id = xxr.release_id
            and xxr.release_date >= NVL(:p_date_from, xxr.release_date)
            and xxr.release_date <= NVL(:p_date_to, xxr.release_date)
            and xxr.Contract_num = NVL(:p_cont_num, xxr.Contract_num)
            and xxr.vendor_id = NVL(:p_ven_id, xxr.vendor_id)
            and xxr.vendor_site_id = NVL(:p_site_id, xxr.vendor_site_id)
        )
    ) TBL1
group by TBL1.Contract_number
    ,TBL1.area_number
    ,TBL1.area_name
    ,TBL1.ADVANCE_PAY
    ,TBL1.Postponed_Amount
    ,TBL1.extract_number
    ,TBL1.total;

#1