I have SQL Query with me and referring this I need to build same query in Qlikview using Qlikview Script not same below SQL.
This is something new to me and I can't see Joins in the below SQL Query. I'm referring the below SQL query to build data model script in Qlikview. Here is my SQL Query:
SQL SELECT AER.CASE_ID, AER.AER_ID, AER.VERSION_NO, AER.AER_INIT_RECV_DATE IRD_ADJ, UPPER(AER_PRODUCT.TRADE_NAME) TRADE_NAME, AER_PRODUCT.SEQ_PRODUCT, AER_REACT.SEQ_REACT FROM DIM_CASE AER, CONFIG_COUNTRY SR_COUNTRY_REGION, ( SELECT CASE_ID, SEQ_PRODUCT, CASE_PRODUCT_ID, PRIMARY_INDICATION_PT_CODE,PRODUCT_FLAG, DIM_PRODUCT.TRADE_NAME, CONFIG_PRODUCT.PRODUCT_GROUP_NAME, FLAG_RMT FROM CONFIG_PRODUCT, DIM_PRODUCT WHERE UPPER(DIM_PRODUCT.TRADE_NAME)=UPPER(CONFIG_PRODUCT.TRADE_NAME ) AND CONFIG_PRODUCT.CONFIG_ID=0 AND FLAG_RMT = 'YES' AND NVL(DIM_PRODUCT.PRODUCT_RECORD_CATEGORY,'Other') IN ( 'Valid Product Record','Other' ) AND CONFIG_PRODUCT.DM_EFFECTIVE_DATE_END = '01-JAN-2099' ) AER_PRODUCT, ( SELECT * FROM CONFIG_INDICATION WHERE IS_ACTIVE = 'YES' ) CONFIG_INDICATION, DIM_EVENT AER_REACT, FACT_CASE_PRODUCT_EVENT, (Select report_parameter1_code product_group_name from sr_report_parameter where parameter_list_type = 'RMT_PRODUCT_OFFSET_FOR IBD') OFFSET WHERE ( AER.CASE_ID=FACT_CASE_PRODUCT_EVENT.CASE_ID ) AND FACT_CASE_PRODUCT_EVENT.CASE_PRODUCT_ID = AER_PRODUCT.CASE_PRODUCT_ID AND FACT_CASE_PRODUCT_EVENT.CASE_EVENT_ID = AER_REACT.CASE_EVENT_ID AND ( SR_COUNTRY_REGION.CONFIG_ID(+)=0 ) AND ( AER.AER_COUNTRY=SR_COUNTRY_REGION.COUNTRY_NAME(+) ) AND AER_PRODUCT.PRIMARY_INDICATION_PT_CODE = CONFIG_INDICATION.INDICATION_PT_CODE (+) AND AER_PRODUCT.PRODUCT_GROUP_NAME = CONFIG_INDICATION.PRODUCT_NAME (+) AND AER_PRODUCT.PRODUCT_GROUP_NAME = OFFSET.PRODUCT_GROUP_NAME (+) AND AER_PRODUCT.PRODUCT_FLAG = 'Suspect' AND ( AER.latest_aer_version_no = 'YES' and AER.approval_status = 'YES' AND AER_REACT.SEQ_AE IS NULL AND AER.aer_case_deleted = 'NO' and AER.aer_deleted = 'NO' );
Sir, reason for storing is just to test out all the possibilities related to memory load, load time, performance. But the main purpose it to understand how joins are working in SQL (maybe that is the Inner Join in the Query above). I'm trying to replicate the same SQL script in Qlikview and out of that want to generate same result with SQL query as well Qlikview Script.
The new updated query which you've shared, I need to test it out. I'll share the results with you.
Yes, sir there are 4-5 more queries similar to that but those are master tables queries so no issue in that.
You're absolutely right Sir.
What I'm trying to achieve here is:
1): First, I'm creating a Document.
2): Secondly, I'm finding how many tables are used in the SQL Query. Documenting all those table Names and used fields from in it.
3): Understanding the Data by extracting the tables individually.
4): Then, storing all the used tables individually in my system Folder using Qlikview Store command. Qvd's concept is ausm.
5): After this, I try to understand the joining keys, cardinality, relationships b/w tables. What can be done to increase the performance at the time of joining or any other way so that data will not duplicate, data discrepancy should not come, performance will not degrade. Where ever required I try to use Hash Keys, Auto number or creating composite keys.
I'm in a learning phase and you really helped me out. Really appreciate that.
I'm doing this for the better performance, speed & optimization.
