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 = ****'

[#1]

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 !!

[#2]

Note: I forgot one important point ... I am working on Qlik Sense