Re: SQL Query convert to Qlikview Script - Qlik Community

erric3210 · ‎2020-10-22

Hi All,

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

Need your help guys.

Regards,

Eric

benvatvandata · ‎2020-10-23

Table1:
LOAD * ;
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' );

erric3210 · ‎2020-10-24

Hi benvatvandata,

Thanks for the reply & sharing this amazing code. I really appreciate your help. It's a new learning for me.

Sorry, I haven't explain it well. What I'm trying to achieve here & what exactly my requirement is:

1): Firstly, I stored individual tables from above SQL query with required fields in a qvd format.

2): Secondly, I tried to join other tables which are in the SQL query with where conditions.

Now, the problem is I don't know which join I need to use in Qlikview because referring SQL query I'm not able to find out that which join is used in it.

Sorry again for not explaining my requirement correctly.

Regards,

Eric

benvatvandata · ‎2020-10-26

No problem, could you share your script where you stored the individual tables? Or do you think you may need to revise that part too?

benvatvandata · ‎2020-10-26

Also... if you're able to query the database... could you let me know if the below query gives the same results as the query you initially posted?

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 
       join fact_case_product_event 
         ON AER.case_id = fact_case_product_event.case_id 
       join 
              (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 
         ON fact_case_product_event.case_product_id = 
            AER_PRODUCT.case_product_id 
       join dim_event AER_REACT 
         ON fact_case_product_event.case_event_id = AER_REACT.case_event_id 
       join config_country SR_COUNTRY_REGION 
         ON AER.aer_country = SR_COUNTRY_REGION.country_name(+) 
       join (SELECT * 
             FROM   config_indication 
             WHERE  is_active = 'YES') CONFIG_INDICATION 
         ON AER_PRODUCT.primary_indication_pt_code = 
                 CONFIG_INDICATION.indication_pt_code (+) 
            AND AER_PRODUCT.product_group_name = 
                CONFIG_INDICATION.product_name (+) 
       join (SELECT report_parameter1_code product_group_name 
             FROM   sr_report_parameter 
             WHERE  parameter_list_type = 'RMT_PRODUCT_OFFSET_FOR IBD') OFFSET 
         ON AER_PRODUCT.product_groupname = OFFSET.product_group_name (+) 
WHERE  sr_country_region.config_id(+) = 0 
       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'

erric3210 · ‎2020-10-29

Hi Sir,

First of all, Sir Thank you so much for devoting your time on my Post. I really appreciate that.

This query giving below error:

QL##f - SqlState: S1000, ErrorCode: 25156, ErrorMsg: [Oracle][ODBC][Ora]ORA-25156: old style outer join (+) cannot be used with ANSI joins

The approach I followed is that:

dim_case:

SELECT *
FROM dim_case;

STORE dim_case into my location\dim_case.qvd(qvd);

DROP TABLE dim_case;

dim_product:

SELECT *
FROM dim_product;

STORE dim_product into my location\dim_product.qvd(qvd);

DROP TABLE dim_product;

same I did for all the tables involved in SQL Query. Stored all of them in a qvd format and after getting all individual tables i'm trying to connect them using joins and finally creating data model out of it.

Sir, one question which is bothering me, How did you find out from the above SQL query that this is a join and not inner join, left join or any other type of join. In SQL query, there is nowhere joins are used. How query work and joins work in that case. I need to understand that part at the time of joining my new Model while referring the SQL query.

Regards,

Eric

erric3210 · ‎2020-10-30

Hi Sir,

This query is giving me below error:

QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: 08S01, ErrorCode: 3113, ErrorMsg: [Oracle][ODBC][Ora]ORA-03113: end-of-file on communication channel

This error i'm getting from your first reply in which you've customized SQl Query.

Regards,

Eric

benvatvandata · ‎2020-11-02

ahh my guess is those (+) signs are causing the issue... i've never really used those joins before (was just trying my best to use your code, but joining in different way)... what about if you just try:

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 
       join fact_case_product_event 
         ON AER.case_id = fact_case_product_event.case_id 
       join 
              (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 
         ON fact_case_product_event.case_product_id = 
            AER_PRODUCT.case_product_id 
       join dim_event AER_REACT 
         ON fact_case_product_event.case_event_id = AER_REACT.case_event_id 
       join config_country SR_COUNTRY_REGION 
         ON AER.aer_country = SR_COUNTRY_REGION.country_name
       join (SELECT * 
             FROM   config_indication 
             WHERE  is_active = 'YES') CONFIG_INDICATION 
         ON AER_PRODUCT.primary_indication_pt_code = 
                 CONFIG_INDICATION.indication_pt_code
            AND AER_PRODUCT.product_group_name = 
                CONFIG_INDICATION.product_name 
       join (SELECT report_parameter1_code product_group_name 
             FROM   sr_report_parameter 
             WHERE  parameter_list_type = 'RMT_PRODUCT_OFFSET_FOR IBD') OFFSET 
         ON AER_PRODUCT.product_groupname = OFFSET.product_group_name 
WHERE  sr_country_region.config_id = 0 
       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'

Sorry been a busy week, I'll try to look into joining the qvds when I have some free time this week. I wanted to know if your original query produced the same results as the new query to validate if they are inner joins/outer joins/etc. (since I haven't used the '(+)' types of joins before)

benvatvandata · ‎2020-11-03

Just curious... is there a reason why you need to store each table as a qvd?

Are you planning on building a lot more on top of this?

Could you share your full load script for storing the qvd's? I think I could create your load script without too much difficulty, but I need some clarification so that I don't write code and then have to scrap it all because one little bit of information ruins the whole plan.

Brett_Bleess · ‎2020-11-04

@erric3210 Just going to toss out the Help Doc on QlikView Join as well, it covers all the different types, hopefully that may help a little as well. You can find some further example related items in the Design Blog area too, so I will leave a link for that as well, but the partner seems to be taking great care of you one this one, I just wanted to toss in this other info as well for others in the future too.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

SQL Query convert to Qlikview Script

General Question