Birt报告设计在eclipse与subreport过滤器 [英] Birt report design in eclipse with subreport filter

问题描述

我的查询具有太多的子查询，每个查询都有重复的参数。如何在日食中设计报告。这是我的查询

My query has too many subqueries and each query has repeated parameters. How to design the report in eclipse. This is my query

    SELECT 
C.COMP_CODE,C.MATCODE,C.ATTRIB1,C.ATTRIB2,C.MAT_NAME,C.SUP_PROD_CODE,
C.SUP_CODE,C.BRAND_CODE,C.CAT_CODE,SGRPCODE,SUB_SGRPCODE,C.UNIT_CODE,
NVL(SUM(D.SALES_QTY),0)SALES_QTY,
NVL(SUM(D.SALES_VAL),0) SALES_VAL,
MAX(COST_PRICE) GRN_COST_PRICE,GRN_DATE,'sales qty' a, 'sales val' b,'stock' c,'stock val' d
FROM

        (
        SELECT  A.COMP_CODE,A.MATCODE,B.UNIT_CODE,A.ATTRIB1,A.ATTRIB2,MAT_NAME,SUP_PROD_CODE,
        SUP_CODE,BRAND_CODE,CAT_CODE,SGRPCODE,SUB_SGRPCODE,COST_PRICE,GRN_DATE FROM 

                (
                SELECT A.COMP_CODE,A.MATCODE,A.ATTRIB1,A.ATTRIB2,MAT_NAME,SUP_PROD_CODE,
                SUP_CODE,BRAND_CODE,CAT_CODE,SGRPCODE,SUB_SGRPCODE,B.COST_PRICE,B.GRN_DATE FROM

                        (
                        SELECT A.COMP_CODE,A.MATCODE,A.ATTRIB1,A.ATTRIB2,B.MAT_NAME,B.SUP_PROD_CODE,
                        B.SUP_CODE,B.BRAND_CODE,B.CAT_CODE,B.SGRPCODE,B.SUB_SGRPCODE FROM
                        MAT_LIST A,
                        MATERIAL_MASTER B
                        WHERE A.COMP_CODE=B.COMP_CODE
                        AND A.MATCODE=B.MATCODE
                        --AND A.MATCODE='168847'
                        )A,

                        (SELECT A.COMP_CODE,A.MAIN_CODE,A.MATCODE,NVL(A.ATTRIB_CODE1,0) ATTRIB1,NVL(A.ATTRIB_CODE2,0) ATTRIB2,
                        A.MAT_TYPE,MAX(A.MAT_COST) COST_PRICE,GRN_DATE   
                        FROM INV_GRN_DTL_V A
                        WHERE  a.grn_date=(select max(b.grn_date) from  inv_grn_dtl_v b 
                                where b.comp_code=a.comp_code and
                                 b.main_code=a.main_code and
                                b.matcode=a.matcode and
                                nvl(b.grn_status,'P')='A' and
                                nvl(b.auth_status,'P')='A' and
                                b.supcode<>'GDS1' and
                                b.grn_date<=:TO_DT)     
                        AND NVL(A.GRN_STATUS,'P')='A'
                        AND NVL(A.AUTH_STATUS,'P')='A' 
                        GROUP BY A.COMP_CODE,A.MAIN_CODE,A.MATCODE,A.ATTRIB_CODE1,A.ATTRIB_CODE2,A.MAT_TYPE,GRN_DATE
                        ) B
                WHERE A.COMP_CODE=B.COMP_CODE(+)
                AND A.MATCODE=B.MATCODE(+)
                AND A.ATTRIB1=B.ATTRIB1(+)
                AND A.ATTRIB2=B.ATTRIB2(+)
                AND A.COMP_CODE=:P_COMP_CODE)

                A,(
                SELECT COMP_CODE,MAIN_CODE,UNIT_CODE
                 FROM   UNIT_MST WHERE COMP_CODE=56
                AND UNIT_CODE IN (SELECT DISTINCT UNIT_CODE FROM  STK_SALES_VU 
                WHERE ORD_DATE BETWEEN :FR_DT AND :TO_DT
                AND COMP_CODE=:P_COMP_CODE)
                --UNION ALL
                --SELECT DISTINCT COMP_CODE,MAIN_CODE,'STOCK' FROM UNIT_MST WHERE COMP_CODE=:P_COMP_CODE
                ) B

        WHERE A.COMP_CODE=B.COMP_CODE
        AND A.COMP_CODE=:P_COMP_CODE
        AND UNIT_CODE=DECODE(:P_UNIT_CODE,'ALL',UNIT_CODE,:P_UNIT_CODE)
        AND CAT_CODE BETWEEN DECODE(:FR_CAT,'ALL',CAT_CODE,:FR_CAT)
        AND DECODE(:TO_CAT,'ALL',CAT_CODE,:TO_CAT)
        AND SUP_CODE=DECODE(:P_SUP_CODE,'ALL',SUP_CODE,:P_SUP_CODE)) 

        C,(
        SELECT COMP_CODE,MAIN_CODE,UNIT_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0) ATTRIB_CODE1,NVL(ATTRIB_CODE2,0) ATTRIB_CODE2,  
        NVL(SUM(SALES_QTY),0) SALES_QTY, SUM(COST_VAL) SALES_VAL
        FROM
        (

                SELECT COMP_CODE,MAIN_CODE,UNIT_CODE,MAT_TYPE,MATCODE,NVL(B.ATTRIB_CODE1,0) ATTRIB_CODE1,NVL(B.ATTRIB_CODE2,0) ATTRIB_CODE2,  
                NVL(SUM(B.SALE_QTY),0) SALES_QTY, SUM(B.VAL) COST_VAL
                FROM  STK_SALES_VU_ATT B 
                WHERE  ORD_DATE BETWEEN :FR_DT AND :TO_DT
                AND UNIT_CODE=DECODE(:P_UNIT_CODE,'ALL',UNIT_CODE,:P_UNIT_CODE)
                AND COMP_CODE=:P_COMP_CODE
                GROUP BY COMP_CODE,MAIN_CODE,UNIT_CODE,MAT_TYPE,MATCODE,NVL(B.ATTRIB_CODE1,0),NVL(B.ATTRIB_CODE2,0)

                UNION ALL

                SELECT COMP_CODE,MAIN_CODE,'STOCK' UNIT_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0),NVL(ATTRIB_CODE2,0),SUM(INC_QTY)-SUM(DEC_QTY) OB_QTY, 0   SALES_VAL
                FROM INV_TRN_DAY_SUM_VU_ATT
                WHERE  TRN_DATE BETWEEN :FR_DT  AND :TO_DT
                AND UNIT_CODE=DECODE(:P_UNIT_CODE,'ALL',UNIT_CODE,:P_UNIT_CODE)
                AND COMP_CODE=:P_COMP_CODE
                GROUP BY COMP_CODE,MAIN_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0),NVL(ATTRIB_CODE2,0)

                UNION ALL

                 SELECT COMP_CODE,MAIN_CODE,'STOCK' UNIT_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0),NVL(ATTRIB_CODE2,0),SUM(QTY)QTY, 0  SALES_VAL
                 FROM MATERIAL_DETAIL
                 WHERE  SERIAL=:P_FNYR
                 AND UNIT_CODE=DECODE(:P_UNIT_CODE,'ALL',UNIT_CODE,:P_UNIT_CODE)
                 AND COMP_CODE=:P_COMP_CODE
                GROUP BY COMP_CODE,MAIN_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0),NVL(ATTRIB_CODE2,0)

        )
        --WHERE MATCODE='168847'
        GROUP BY COMP_CODE,MAIN_CODE,UNIT_CODE,MAT_TYPE,MATCODE,ATTRIB_CODE1,ATTRIB_CODE2
        ) D

WHERE    C.COMP_CODE           =  D.COMP_CODE (+)  
AND      C.UNIT_CODE           =  D.UNIT_CODE(+)   
AND      C.MATCODE            =  D.MATCODE(+)
--AND C.MATCODE='168847'
AND      C.ATTRIB1            =  D.ATTRIB_CODE1(+)
AND      C.ATTRIB2            =  D.ATTRIB_CODE2(+)
AND C.COMP_CODE=:P_COMP_CODE
AND C.UNIT_CODE=DECODE(:P_UNIT_CODE,'ALL',C.UNIT_CODE,:P_UNIT_CODE)
GROUP BY 
C.COMP_CODE,C.MATCODE,C.ATTRIB1,C.ATTRIB2,C.MAT_NAME,C.SUP_PROD_CODE,
C.SUP_CODE,C.BRAND_CODE,C.CAT_CODE,SGRPCODE,SUB_SGRPCODE,C.UNIT_CODE,GRN_DATE
order by c.unit_code

参数是（：FR_DT，：TO_DT，：p_COMP_CODE，：FR_CAT，TO_CAT，p_SUP_CODE），需要用'？ '在数据集中写入查询。但是我不知道如何用查询参数替换在多个地方发生的相同参数。以及如何处理参数之间的 DECODE 和参数。

The parameters are (:FR_DT,:TO_DT, : p_COMP_CODE, :FR_CAT, :TO_CAT, : p_SUP_CODE) which need to be replaced with '?' while writing the query in dataset. But i don't know how to replace same parameter which is occurring at multiple places with the query parameters. and How to handle DECODE and between parametes.

推荐答案

是使用 WITH 子句将您的参数分配给一个虚拟表。

One option is to use a WITH clause to assign your parameters to a dummy table.

WITH tmp_parms AS (
    SELECT ? as fr_dt, ? as to_dt, ? as p_comp_code,
        ? as fr_cat, ? as to_cat, ? as p_sub_code
    FROM dual
)
SELECT C.COMP_CODE,C.MATCODE, ... etc
FROM tmp_parms tp,
        (
        SELECT A.COMP_CODE,A.MATCODE, ... etc

或者如果您想要另一个内联视图：

Or if you'd prefer yet another inline view:

SELECT C.COMP_CODE,C.MATCODE, ... etc
FROM (
    SELECT ? as fr_dt, ? as to_dt, ? as p_comp_code,
        ? as fr_cat, ? as to_cat, ? as p_sub_code
    FROM dual
) tp,
        (
        SELECT A.COMP_CODE,A.MATCODE, ... etc

然后用r替换所有现有的绑定变量从临时parms表中的等效列引用，即更改：

And then replace all the existing bind variables with references to the equivalent column from the temporary parms table, i.e. change this:

                                b.grn_date<=:TO_DT)

到：

                                b.grn_date<=tp.to_dt)

这篇关于Birt报告设计在eclipse与subreport过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！