20 Replies Latest reply: Mar 24, 2014 9:21 AM by Helen Betty RSS

    How to create QVD from Pl/SQL ?

    Helen Betty

      Need Qlikview Script instead of Pl/sql Script..

        • Re: How to create QVD from Pl/SQL ?
          Massimo Grossi

          if you post your oracle pl/sql script or you can explain us the logic perhaps the community can helps you

            • Re: How to create QVD from Pl/SQL ?
              Sunil Chauhan

              Go to Edit script( Control+e)

               

              data Tab( bottom left)

               

              select OLEdb-. select provider

               

              Click next provide Credentials i.e server ip addess or Server name

               

              provide password if any.

              then finish

               

              Click on select which is below connect In data Tab( bottom left)

               

              select table and finish

               

              Now you will have code like below

               

              OLEDVB connect to..........................................

              tab1 :// Give name of your choice

              Select *

              Load

              a,

              b

              C

              from dbo.table name;

               

              Now store it using below code

               

              Store tab1 into Yourpath\tab1.qvd

               

               

              hope this helps

              • Re: How to create QVD from Pl/SQL ?
                Helen Betty

                I have written here Else part...try to convert into qvscript..

                Else

                {

                       SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER              

                                FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

                                WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

                                AND UPPER(prm.PRODUCT_ATTR_SEG)= :g_cProductAttrSeg

                                AND prm.PRODUCT_ID= :g_iProd_id -- product_id

                                AND NVL(prm.CHARGE_TYPE,'P')= DECODE(:g_iCharge_type, 1, 'I', 2,'P','P') ---- G_ICHARGE_TYPE 1 OR 2 CAN BE FOUND IN BILL PRODUCT CHARGE

                                AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag;

                          IF(NO_DATA_FOUND)

                           {  SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

                                    INTO :g_iGsiRev_Id,:lProductCode,:g_cProfCen,:lDunning_area_desc, :g_cCost_Centre 

                                    FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

                                    WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

                                    AND UPPER(prm.PRODUCT_ATTR_SEG)=:g_cProductAttrSeg      

                               AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag 

                                    AND prm.SERVICE_TYPE=:gServType                   ----service type

                                    AND prm.PRODUCT_ID IS NULL

                                    AND prm.PRODUCT_FAMILY_ID IS NULL;

                 

                 

                 

                 

                              

                            }

                 

                 

                   

                     }

                  • Re: How to create QVD from Pl/SQL ?
                    Sunil Chauhan

                    Create Qvd Seperately using below

                     

                    Tab1:

                    SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER             

                                    FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

                                    WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

                                    AND UPPER(prm.PRODUCT_ATTR_SEG)= :g_cProductAttrSeg

                                    AND prm.PRODUCT_ID= :g_iProd_id -- product_id

                                    AND NVL(prm.CHARGE_TYPE,'P')= DECODE(:g_iCharge_type, 1, 'I', 2,'P','P') ---- G_ICHARGE_TYPE 1 OR 2 CAN BE FOUND IN BILL PRODUCT CHARGE

                                    AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag;

                              IF(NO_DATA_FOUND)

                               {  SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

                                        INTO :g_iGsiRev_Id,:lProductCode,:g_cProfCen,:lDunning_area_desc, :g_cCost_Centre

                                        FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

                                        WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

                                        AND UPPER(prm.PRODUCT_ATTR_SEG)=:g_cProductAttrSeg     

                                   AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag

                                        AND prm.SERVICE_TYPE=:gServType                   ----service type

                                        AND prm.PRODUCT_ID IS NULL

                                        AND prm.PRODUCT_FAMILY_ID IS NULL;

                     

                    store Tab1 into yourpath\Tab1.qvd;

                     

                    drop table yourpath\Tab1.qvd;

                     

                    and use it in your code

                    Else

                    {

                     

                    Load *

                    from yourpath\Tab1.qvd(qvd);

                    }

                     

                     

                    hope this helps

                    • Re: How to create QVD from Pl/SQL ?
                      Anand Chouhan

                      Hi,

                       

                      After this load script

                       

                      Else

                      {

                             SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER             

                                      FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

                                      WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

                                      AND UPPER(prm.PRODUCT_ATTR_SEG)= :g_cProductAttrSeg

                                      AND prm.PRODUCT_ID= :g_iProd_id -- product_id

                                      AND NVL(prm.CHARGE_TYPE,'P')= DECODE(:g_iCharge_type, 1, 'I', 2,'P','P') ---- G_ICHARGE_TYPE 1 OR 2 CAN BE FOUND IN BILL PRODUCT CHARGE

                                      AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag;

                                IF(NO_DATA_FOUND)

                                 {  SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER

                                          INTO :g_iGsiRev_Id,:lProductCode,:g_cProfCen,:lDunning_area_desc, :g_cCost_Centre

                                          FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

                                          WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

                                          AND UPPER(prm.PRODUCT_ATTR_SEG)=:g_cProductAttrSeg     

                                     AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag

                                          AND prm.SERVICE_TYPE=:gServType                   ----service type

                                          AND prm.PRODUCT_ID IS NULL

                                          AND prm.PRODUCT_FAMILY_ID IS NULL;

                                       

                                  }

                                }

                       

                      STORE Tablename into Tablename.qvd;

                      or

                      STORE Tablename into $(vYourlocationvariable)\Tablename.qvd;

                       

                      Put Store command for storing the QVD table like

                        • Re: How to create QVD from Pl/SQL ?
                          Helen Betty

                          I am not fetching the data from from SQL server,

                          I am already having the tables in QVD format..

                          By Using that qvds i have to implement that...

                            • Re: How to create QVD from Pl/SQL ?
                              Anand Chouhan

                              Hi,

                               

                              Ok simply load from qvd like

                               

                              Load *

                              from Location;

                              or

                              Load

                              fieldname,

                              fieldname,

                              fieldnames......

                              from Location;

                               

                              if possible provide me your qvd load script.

                              Hope this helps

                              Thanks & Regards

                              • Re: How to create QVD from Pl/SQL ?
                                Angad Singh

                                I believe you have QVD's available for individual tables

                                (GSI_PRODUCT_REVENUE_MAP.qvd, GSI_REVENUE_DET.qvd)

                                 

                                To transform your query into a QV Script, would require

                                >> Knowledge of the whole data model of the application.

                                >> Understand what exactly the query is doing.

                                 

                                You should break the query into smaller parts and start creating QV Script. or if you are in a hurry to optimize your application, then create a QVD of the result set of this query(as discussed by fellow community members) and use that in your application.

                                 

                                Try transforming it, and share with us, what all problems you face.

                                 

                                Thanks,

                                Angad

                                  • Re: How to create QVD from Pl/SQL ?
                                    Helen Betty

                                    All are saying that copy that select Query Code & Paste it..

                                    I have to load the QVD with the Joins, Then I have to mention the conditions...

                                    How its possible..

                                      • Re: How to create QVD from Pl/SQL ?
                                        Angad Singh

                                        ok, to start with , use the below:

                                        Since GSI_REVENUE_CODE_ID is common in both, the left join() will join both the table on the common field name.

                                         

                                        Now this will be similar to

                                        DB Query:

                                        SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA, rd.COST_CENTER            

                                                        FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd

                                                        WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

                                         

                                        QVScript:

                                         

                                        GSI_PRODUCT_REVENUE_MAP_tbl:

                                        LOAD

                                        GSI_REVENUE_CODE_ID,

                                        PRODUCT_ATTR_SEG,

                                        PRODUCT_ID,

                                        CHARGE_TYPE,

                                        CUSTOMER_TYPE_ID

                                        ..\QVDLocation\GSI_PRODUCT_REVENUE_MAP.qvd

                                         

                                         

                                        GSI_REVENUE_DET_tbl:

                                        left join(GSI_PRODUCT_REVENUE_MAP_tbl)

                                        LOAD

                                        GSI_REVENUE_CODE_ID,

                                        PRODUCT_CODE,

                                        PROFIT_CENTER,

                                        DUNNING_AREA,

                                        COST_CENTER

                                        ..\QVDLocation\GSI_REVENUE_DET.qvd

                                         

                                        You can gradually expand the query step by step.

                                         

                                        Hope this was helpful.

                                         

                                        Thanks,

                                        Angad

                            • Re: How to create QVD from Pl/SQL ?
                              Nizam HM

                              Hi,

                               

                              Execute your pl/sql procedure or function from your qlikview script.

                               

                              Your pl/sql procedure should return some result set. In case of function it will return some result set. In case of procedure to return values use ref cursor.

                               

                              like

                               

                              connection_string_to_database

                               

                              Table1:(Qlikview_Sample_Table)

                              sql

                              exec sample_procedure;

                              store Table1 into Table1.qvd;

                              • Re: How to create QVD from Pl/SQL ?
                                Ashraf Muhammad Arsalan Ashraf

                                Please upload the sample data file and also share the query that u want to translate in qv script and what is the desired result u want from them also share it.