Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sushil353
Honored Contributor II

help me to implement sql query in qlikview

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

2 Replies
martin59
Valued Contributor II

Re: help me to implement sql query in qlikview

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
Honored Contributor II

help me to implement sql query in qlikview

Hi Martin,

It is not working

Community Browser