1 Reply Latest reply: Jul 27, 2017 3:48 AM by abhishek shukla RSS

    SQL query working when hardcoded but not loading rows when i am making it use 'PK_ID IN (select distinct pk_id from *****) instead of 'PK_ID = ****'

    abhishek shukla

      Hi everyone,

       

      I am New to Qlik and in need of help.

       

      I am using queries instead of loading tables then filtering because tables are having huge data and many tables are there so it will take more time than we can afford.

       

      Below query is working fine when I am using PK_ID = 1234 (some hardcoded value) instead of highlighted one.

       

      Please help.

       

      This is my query

       

      SELECT info.CONSTRAINTID,

        info.CONST_DESC,

        info.CONSTRAINT_TYPE,

        info.OSLTYPE,

        info.PROFILE_TYPE,

        info.GATE ,

        info.TYPE_H_S,

        mdi.WEEK_NUMBER as WEEK,

        mdi.PERIOD_DATE  PERIODDATEDISP,

        info.CODE PK_CODE,

        info.DESCRIPTION CON_DESCRIPTION,

        mdi.REF_WEEK REFWEEK,

        type O_TYPE,

        MN_BACK_LOG_COUNT_WK,

        NULL MN_BACK_ORD_COUNT_WK,

        MN_CURR_CAP_SUM_WK,

        MN_TOT_ORD_COUNT_WK,

        MN_DELTA

      FROM

        ( SELECT DISTINCT DT.WEEK_NUMBER,

          TO_CHAR(DT.PERIOD_DATE,'MM/DD/YYYY') PERIOD_DATE,

          DT.PERIOD_DATE REF_WEEK

        FROM

          (SELECT year_date,

            WEEK_NUMBER,

            MIN(period_Date) over (partition BY year_number,week_number) period_Date

          FROM xyZ.***_****_***

          ) DT

        WHERE DT.year_date>=TRUNC(sysdate)

        AND YEAR_DATE BETWEEN

          (SELECT to_date(param_value,'YYYY-MM-DD')

          FROM random_par

          WHERE PARAM_KEY = 'Todays_Date'

          )

        AND (SELECT add_months(to_date(param_value,'YYYY-MM-DD'),6)

          FROM random_par

          WHERE PARAM_KEY = 'Todays_Date')

        ) mdi

      LEFT OUTER JOIN

        (SELECT MD_DATE.CONSTRAINTID CONSTRAINTID,

          MD_DATE.CONST_DESC,

          MD_DATE.CONSTRAINT_TYPE,

          MD_DATE.OSLTYPE,

          MD_DATE.PROFILE_TYPE,

          MD_DATE.GATE ,

          MD_DATE.TYPE_H_S,

          MD_DATE.WEEK_NUMBER,

          MD_DATE.PERIOD_DATE PERIOD_DATE_DISP,

          MD_DATE.CODE,

          MD_DATE.DESCRIPTION,

          MD_DATE.REF_WEEK,

          'Production' type,

          MN_BACK_LOG_COUNT_WK,

          NULL MN_BACK_ORD_COUNT_WK,

          MN_CURR_CAP_SUM_WK,

          MN_TOT_ORD_COUNT_WK,

          NVL(MN_TOT_ORD_COUNT_WK,0)-NVL(MN_CURR_CAP_SUM_WK,0) MN_DELTA

        FROM

          (SELECT *

          FROM

            ( SELECT DISTINCT uc.ccmid,

              RDC.CONSTRAINTID,

              RDC.CONSTRAINTNAME CONST_DESC,

              RDC.UITYPE CONSTRAINT_TYPE,

              RDC.OSLTYPE OSLTYPE,

              MP1.CODE,

              MP1.DESCRIPTION,

              CASE

                WHEN UC.AUTOUPDATE = 'TRUE'

                THEN 'Calendar Dependent'

                WHEN UC.BANKABLE = 'TRUE'

                THEN 'Bankable'

                WHEN UC.AUTOGENERATE = 'TRUE'

                THEN 'Autogenerated'

                ELSE TO_CHAR(UC.SPK_ID)

              END PROFILE_TYPE,

              CASE

                WHEN UC.CONSTRAINTSTEP=1

                THEN

                  CASE

                    WHEN UC.LINKCONST IS NOT NULL

                    THEN 'Production'

                      ||','

                      ||NVL(UC.LINKCONST ,'')

                    ELSE 'Production'

                  END

                WHEN UC.CONSTRAINTSTEP=2

                THEN

                  CASE

                    WHEN UC.LINKCONST IS NOT NULL

                    THEN 'Ex-Factory'

                      ||','

                      ||NVL(UC.LINKCONST ,'')

                    ELSE 'Ex-Factory'

                  END

              END GATE,

              CASE

                WHEN UC.N1=0

                THEN

                  CASE

                    WHEN UP.CCMID IS NULL

                    THEN 'Hard'

                    WHEN UP.CCMID   IS NOT NULL

                    AND UP.AMOUNTOVER=1

                    AND UP.PENALTY   =-1

                    THEN 'Hard'

                    ELSE 'Soft'

                  END

                WHEN UC.N1=1

                THEN 'Soft'

              END TYPE_H_S

            FROM xyZ.rep_agdud_akaa RDC

            JOIN xyZ.UDT_CONSTRAINTMAP UCM

            ON RDC.CONSTRAINTID=UCM.CONSTRAINTID

              -- AND

              --        UCM.MODELID   IN

              --        ( SELECT DISTINCT MODELID

              --        from xyZ.***_**_MAPPING

              --        WHERE PK_ID = 9103

              --        and PK_LEVEL='WSL'

              --        )

            JOIN

              (SELECT MPM.PK_ID,

                MP.CODE,

                MP.DESCRIPTION,

                MPM.MODELID

              FROM

                (SELECT *

                FROM xyZ.***_**_MAPPING

                WHERE PK_LEVEL='WSL'

               AND PK_ID    IN

                  ( SELECT DISTINCT pk_id FROM ***_** WHERE active = 'true'

                  )

                ) MPM

              JOIN ***_** MP

              ON MPM.PK_ID         = MP.PK_ID

              ) MP1 ON UCM.MODELID = MP1.MODELID

            AND RDC.CONSTRAINTID  IN

              (SELECT DISTINCT(constraintid) FROM REPORT_D_CONSTRAINT

              )

            AND UCM.MODELID    = RDC.MODELID

            AND RDC.SCENARIOID = 1

            JOIN xyZ.ghd_dhkajanaUC

            ON UCM.CCMID=UC.CCMID

            LEFT OUTER JOIN xyZ.abhc_aihd UP

            ON UP.CCMID            =UC.CCMID

            WHERE uC.CONSTRAINTSTEP=1

            AND UC.CONSTRAINTTAG  <>'Objective'

            ) CONS_INFO,

            ( SELECT DISTINCT DT.WEEK_NUMBER,

              TO_CHAR(DT.PERIOD_DATE,'MM/DD/YYYY') PERIOD_DATE,

              DT.PERIOD_DATE REF_WEEK

            FROM

              (SELECT year_date,

                WEEK_NUMBER,

                MIN(period_Date) over (partition BY year_number,week_number) period_Date

              FROM xyZ.***_****_***

              ) DT

            WHERE DT.year_date>=TRUNC(sysdate)

            AND YEAR_DATE BETWEEN

              (SELECT TO_DATE(PARAM_VALUE,'YYYY-MM-DD')

              FROM random_par

              WHERE PARAM_KEY = 'Todays_Date'

              )

            AND (SELECT ADD_MONTHS(TO_DATE(PARAM_VALUE,'YYYY-MM-DD'),6)

              FROM random_par

              WHERE PARAM_KEY = 'Todays_Date')

            ) DT

            --      ,

            --      (SELECT CODE,

            --        DESCRIPTION

            --      from xyZ.***_**

            --      WHERE PK_ID = 9103

            --      ) PK

          ) MD_DATE

        LEFT JOIN

          (SELECT RDC.CONSTRAINTID,

            COUNT(DISTINCT RFS.ORDERKEY) MN_BACK_LOG_COUNT_WK

          FROM xyZ.rep_gfd_xyz RFS,

            xyZ.rep_gdkah_djad_fdhlad  MISC,

            xyZ.rep_agdud_akaa RDC,

            xyZ.jghsad_dksad_dsa  GFO,

            xyZ.***_****_*** DT

          WHERE RFS.MODELID IN

            ( SELECT DISTINCT MODELID

            FROM xyZ.***_**_MAPPING

            WHERE PK_ID IN

              ( SELECT DISTINCT pk_id FROM ***_** WHERE active = 'true'

              )

            AND PK_LEVEL= 'WSL'

            )

          AND RFS.SCENARIOID    = 1

          AND RDC.CONSTRAINTID IN

            (SELECT DISTINCT(constraintid) FROM REPORT_D_CONSTRAINT

            )

          AND RFS.PRODUCTIONDATE>=TRUNC(SYSDATE)

          AND GFO.FACTORYORDERID =RFS.FACTORYORDERID

          AND RDC.ROWKEY         =MISC.CONSTRAINTKEY

          AND RDC.MODELID        =RFS.MODELID

          AND RDC.MODELID        =MISC.MODELID

          AND RDC.SCENARIOID     =RFS.SCENARIOID

          AND RDC.SCENARIOID     =MISC.SCENARIOID

          AND MISC.ORDERKEY      =RFS.ORDERKEY

          AND DT.YEAR_DATE       =RFS.PRODUCTIONDATE

          AND DT.PERIOD_DATE     > NEXT_DAY(TRUNC(SYSDATE-7),'MONDAY')

          GROUP BY RDC.CONSTRAINTID

          ) BACK_LOG_WEEK

        ON (MD_DATE.CONSTRAINTID=BACK_LOG_WEEK.CONSTRAINTID )

        LEFT OUTER JOIN

          (SELECT RDC.CONSTRAINTID,

            dt.period_Date RFC_REAL_WEEK,

            SUM(

            CASE

              WHEN upper(rdc.osltype) LIKE '%MIN%'

              THEN rfc.target

              ELSE rfc.capacity

            END) MN_CURR_CAP_SUM_WK

          FROM xyZ.REPORT_F_CAPACITY RFC,

            xyZ.rep_agdud_akaa RDC,

            (SELECT year_date,

              MIN(period_Date) over (partition BY year_number,week_number) period_Date

            FROM xyZ.***_****_***

            ) DT

          WHERE RFC.MODELID IN

            ( SELECT DISTINCT MODELID

            FROM xyZ.***_**_MAPPING

            WHERE PK_ID IN

              ( SELECT DISTINCT pk_id FROM ***_** WHERE active = 'true'

              )

            AND PK_LEVEL= 'WSL'

            )

          AND RFC.SCENARIOID    = 1

          AND RDC.CONSTRAINTID IN

            (SELECT DISTINCT(constraintid) FROM REPORT_D_CONSTRAINT

            )

          AND RDC.ROWKEY       =RFC.CONSTRAINT_KEY

          AND RFC.REALDATETIME = DT.YEAR_DATE

          AND RFC.REALDATETIME BETWEEN

            (SELECT TO_DATE(PARAM_VALUE,'YYYY-MM-DD')

            FROM random_par

            WHERE PARAM_KEY = 'Todays_Date'

            )

          AND (SELECT ADD_MONTHS(TO_DATE(PARAM_VALUE,'YYYY-MM-DD'),6)

            FROM random_par

            WHERE PARAM_KEY = 'Todays_Date')

          GROUP BY RDC.CONSTRAINTID,

            dt.period_Date

          ) CAP_WEEK

        ON (MD_DATE.CONSTRAINTID=CAP_WEEK.CONSTRAINTID

        AND MD_DATE.REF_WEEK    =CAP_WEEK.RFC_REAL_WEEK)

        LEFT OUTER JOIN

          (SELECT RDC.CONSTRAINTID,

            dt.period_Date RFS_PROD_WEEK,

            COUNT(DISTINCT RFS.ORDERKEY) MN_TOT_ORD_COUNT_WK

          FROM xyZ.rep_agdud_akaa RDC,

            xyZ.rep_gfd_xyzRFS,

            xyZ.jghsad_dksad_dsa  GFO,

            xyZ.rep_gdkah_djad_fdhlad  MISC,

            (SELECT year_date,

              MIN(period_Date) over (partition BY year_number,week_number) period_Date

            FROM xyZ.***_****_***

            ) DT

          WHERE RFS.MODELID IN

            ( SELECT DISTINCT MODELID

            FROM xyZ.***_**_MAPPING

            WHERE PK_ID IN

              ( SELECT DISTINCT pk_id FROM ***_** WHERE active = 'true'

              )

            AND PK_LEVEL= 'WSL'

            )

          AND RFS.SCENARIOID    = 1

          AND RDC.CONSTRAINTID IN

            (SELECT DISTINCT(constraintid) FROM REPORT_D_CONSTRAINT

            )

          AND RFS.PRODUCTIONDATE>=TRUNC(SYSDATE)

          AND RFS.FACTORYORDERID =GFO.FACTORYORDERID

          AND RDC.MODELID        =RFS.MODELID

          AND RDC.MODELID        =MISC.MODELID

          AND RDC.SCENARIOID     =RFS.SCENARIOID

          AND RDC.SCENARIOID     =MISC.SCENARIOID

          AND MISC.ORDERKEY      =RFS.ORDERKEY

          AND MISC.CONSTRAINTKEY =RDC.ROWKEY

          AND DT.YEAR_DATE       =RFS.PRODUCTIONDATE

          GROUP BY RDC.CONSTRAINTID,

            dt.period_Date

          ) TOT_WEEK

        ON (MD_DATE.CONSTRAINTID     =TOT_WEEK.CONSTRAINTID

        AND TOT_WEEK.RFS_PROD_WEEK   =MD_DATE.REF_WEEK)

        WHERE (MN_BACK_LOG_COUNT_WK IS NOT NULL

        OR MN_CURR_CAP_SUM_WK       IS NOT NULL

        OR MN_TOT_ORD_COUNT_WK      IS NOT NULL)

        ) INFO ON INFO.REF_WEEK      =MDI.REF_WEEK;

       

       

       

      Thanks in advance for your kind help !!