How to create QVD from Pl/SQL ?

[#1]

Need Qlikview Script instead of Pl/sql Script..

[maxgro]

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

[SunilChauhan]

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

[#4]

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]

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

[its_anandrjs]

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

[#7]

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

[its_anandrjs]

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

[#9]

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

[#10]

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