1 Reply Latest reply: Jul 14, 2011 1:50 AM by Shumail Hussain RSS

    Joining Tables

    Sushil Kumar

      Hi All,

       

      I have 8 tables to get the data.. the sql written for that is given below...i need to implement it on qlikview so that data fetched from various table get joined and loaded in the single table....Please help me out

       

      Thanks..

       

       

      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 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')