2 Replies Latest reply: Jul 13, 2011 5:30 AM by Sushil Kumar RSS

    help me to implement sql query in qlikview

    Sushil Kumar

      Hi All,

       

      I need to load data into qlikview file using a sql query. The query is written in such a way that it fetches the data from various tables and load that into a single table...as you can see below there are lot of joints and conditions given and as i am new to this tool please help me out to how we can implement this situation in qlikview to load data

       

      Thanks

       

      SELECT prim.UNIT_ID,

      prim.ORIGIN, prim.DESCRIPTION,

      prim.PRIMARY_INFO, defect.SECONDARY_INFO, other.OTHER_INFO, prim.UPD_USER DEFECT_USER, prim.UPD_DATE DEFECT_DATE, PRIM.PRD_GROUP PRODUCT,

      FROM

      (

      SELECT pd.prd_group,dl_ot_code OPERATION_CODE,dl_st_Station, dl_unit_id UNIT_ID, dl_id DEFECT_LOG_ID, de_id DEFECT_ID,

      di_id PRIMARY_ID, dt_origin ORIGIN, dt_description DESCRIPTION,  it_type IT_TYPE, di_info PRIMARY_INFO, de_upd_user UPD_USER,

      to_char(db_date_location_2_local_date(de_upd_date,1),'MON-DD-YYYY HH24.MI.SS') UPD_DATE, OT_TYPE OPERATION

       

       

      FROM defect_logs dl

       

       

      INNER JOIN OPERATION_TYPES ON DL_OT_CODE = OT_CODE

      INNER JOIN defects de ON dl_id = de_dl_id and de.delete_ods_dt is null

       

      LEFT JOIN defect_info di ON di_de_id = de_id  and di.delete_ods_dt is null

      LEFT JOIN info_types it ON di_it_code = it_code  and it.delete_ods_dt is null

       

      //TYPES

      left JOIN defect_types dt ON de_dt_code = dt_code

       

      //LINK TO PD

      LEFT JOIN units u ON dl_unit_id = u.unit_id

      LEFT JOIN product_designs pd ON NVL(u.start_prd_id,dl_prd_id) = pd.prd_id

       

      //LINK DEFECT LOGS

      LEFT JOIN assembly_logs ON AS_DL_ID = DL_ID

       

      WHERE (as_op_vernum = 1 OR as_op_vernum IS NULL)

      and dl.delete_ods_dt is null

      AND dl_client_id IN

      (

      SELECT client_id

      FROM ods_na_owner.station_client

      WHERE location_code = 1

      AND stage_code = 1

      )

       

       

      AND (it_order = 1 OR it_order IS NULL /*IF NO INFO TYPES*/)

      AND DI_IT_CODE NOT IN (5,328,394,395)

      AND de_upd_date BETWEEN local_date_location_2_db_date(to_date('MAY-01-2011 00:00:00','MON-DD-YYYY HH24.MI.SS'),1)

      AND local_date_location_2_db_date(to_date('JUL-05-2011 23:59:59','MON-DD-YYYY HH24.MI.SS'),1)

      AND prd_group IN ('R040 (768)','R040 Slider')

      AND dt_origin IN ('PROCESS','COMPONENT')

      ) prim

       

       

       

      LEFT JOIN

      (

      SELECT de_id DEFECT_ID, NVL(di_id,'') SECONDARY_ID, di_info SECONDARY_INFO

       

      FROM defect_info

      INNER JOIN info_types ON it_code = di_it_code and info_types.delete_ods_dt is null

      INNER JOIN defects ON de_id = di_de_id  and defects.delete_ods_dt is null

      WHERE di_it_code IN (328,394,395,585)

      and defect_info.delete_ods_dt is null

       

      ) defect ON prim.DEFECT_ID = defect.DEFECT_ID

       

       

      LEFT JOIN

      (

      SELECT de_id DEFECT_ID, NVL(di_id,'') OTHER_ID, di_info OTHER_INFO

      FROM defect_info

      INNER JOIN info_types ON it_code = di_it_code  and info_types.delete_ods_dt is null

      INNER JOIN defects ON de_id = di_de_id  and defects.delete_ods_dt is null

      WHERE di_it_code IN (5)

      and defect_info.delete_ods_dt is null

      ) other ON prim.DEFECT_ID = other.DEFECT_ID

        • Re: help me to implement sql query in qlikview
          Martin FAVIER

          Hi,

           

          Not sure, but have you tried :

           

          QVTable:
          SQL SELECT prim.UNIT_ID,
          prim.ORIGIN, prim.DESCRIPTION,
          prim.PRIMARY_INFO, defect.SECONDARY_INFO, other.OTHER_INFO, prim.UPD_USER DEFECT_USER, prim.UPD_DATE DEFECT_DATE, PRIM.PRD_GROUP PRODUCT,
          FROM
          (
          SELECT pd.prd_group,dl_ot_code OPERATION_CODE,dl_st_Station, dl_unit_id UNIT_ID, dl_id DEFECT_LOG_ID, de_id DEFECT_ID,
          di_id PRIMARY_ID, dt_origin ORIGIN, dt_description DESCRIPTION,  it_type IT_TYPE, di_info PRIMARY_INFO, de_upd_user UPD_USER,
          to_char(db_date_location_2_local_date(de_upd_date,1),'MON-DD-YYYY HH24.MI.SS') UPD_DATE, OT_TYPE OPERATION
          
          
          FROM defect_logs dl
          
          
          INNER JOIN OPERATION_TYPES ON DL_OT_CODE = OT_CODE
          INNER JOIN defects de ON dl_id = de_dl_id and de.delete_ods_dt is null
          
          LEFT JOIN defect_info di ON di_de_id = de_id  and di.delete_ods_dt is null
          LEFT JOIN info_types it ON di_it_code = it_code  and it.delete_ods_dt is null
          
          //TYPES
          left JOIN defect_types dt ON de_dt_code = dt_code
          
          //LINK TO PD
          LEFT JOIN units u ON dl_unit_id = u.unit_id
          LEFT JOIN product_designs pd ON NVL(u.start_prd_id,dl_prd_id) = pd.prd_id
          
          //LINK DEFECT LOGS
          LEFT JOIN assembly_logs ON AS_DL_ID = DL_ID
          
          WHERE (as_op_vernum = 1 OR as_op_vernum IS NULL)
          and dl.delete_ods_dt is null
          AND dl_client_id IN
          (
          SELECT client_id
          FROM ods_na_owner.station_client
          WHERE location_code = 1
          AND stage_code = 1
          )
          
          
          AND (it_order = 1 OR it_order IS NULL /*IF NO INFO TYPES*/)
          AND DI_IT_CODE NOT IN (5,328,394,395)
          AND de_upd_date BETWEEN local_date_location_2_db_date(to_date('MAY-01-2011 00:00:00','MON-DD-YYYY HH24.MI.SS'),1)
          AND local_date_location_2_db_date(to_date('JUL-05-2011 23:59:59','MON-DD-YYYY HH24.MI.SS'),1)
          AND prd_group IN ('R040 (768)','R040 Slider')
          AND dt_origin IN ('PROCESS','COMPONENT')
          ) prim
          
          
          
          LEFT JOIN
          (
          SELECT de_id DEFECT_ID, NVL(di_id,'') SECONDARY_ID, di_info SECONDARY_INFO
          
          FROM defect_info
          INNER JOIN info_types ON it_code = di_it_code and info_types.delete_ods_dt is null
          INNER JOIN defects ON de_id = di_de_id  and defects.delete_ods_dt is null
          WHERE di_it_code IN (328,394,395,585)
          and defect_info.delete_ods_dt is null
          
          ) defect ON prim.DEFECT_ID = defect.DEFECT_ID
          
          
          LEFT JOIN
          (
          SELECT de_id DEFECT_ID, NVL(di_id,'') OTHER_ID, di_info OTHER_INFO
          FROM defect_info
          INNER JOIN info_types ON it_code = di_it_code  and info_types.delete_ods_dt is null
          INNER JOIN defects ON de_id = di_de_id  and defects.delete_ods_dt is null
          WHERE di_it_code IN (5)
          and defect_info.delete_ods_dt is null
          ) other ON prim.DEFECT_ID = other.DEFECT_ID