help me to implement sql query in qlikview - Qlik Community

sushil353 · ‎2011-07-13

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

martin59 · ‎2011-07-13

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

sushil353 · ‎2011-07-13

Hi Martin,

It is not working