Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to create QVD from Pl/SQL ?

Need Qlikview Script instead of Pl/sql Script..

20 Replies
MVP
MVP

Re: How to create QVD from Pl/SQL ?

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

SunilChauhan
Esteemed Contributor

Re: How to create QVD from Pl/SQL ?

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

Not applicable

Re: How to create QVD from Pl/SQL ?

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;

              

            }

   

     }

SunilChauhan
Esteemed Contributor

Re: How to create QVD from Pl/SQL ?

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 ?

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

Not applicable

Re: How to create QVD from Pl/SQL ?

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 ?

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

Not applicable

Re: How to create QVD from Pl/SQL ?

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

Not applicable

Re: How to create QVD from Pl/SQL ?

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..