Birt report design in eclipse with subreport filte

Question

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

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.

Answer 1

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

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)

to this:

                                b.grn_date<=tp.to_dt)