Stored Procedure into QV Script ?

Fri, 21 Mar 2014 06:20:48 GMT

Hi, I want to convert SQL Stored procedure into qlikview script ?

Re: Stored Procedure into QV Script ?

manojkulkarni — Fri, 21 Mar 2014 06:25:54 GMT

You can directly call Stored Procedure itself in QLikVIew Script.

TableName:

SQL EXEC sp_GetYourData;

Re: Stored Procedure into QV Script ?

Fri, 21 Mar 2014 06:31:35 GMT

HI Manoj, In got the SQL Scripting in Stored procedure,

For that I want to Convert into Qlikview script...

Re: Stored Procedure into QV Script ?

manojkulkarni — Fri, 21 Mar 2014 06:34:46 GMT

If you don't have any problem, you can post the Stored Procedure. Will try to convert.

Qlikview Script will be same as SQL. Only thing you need to consider is joins. Behaviour of joins are same but handling is different.

Re: Stored Procedure into QV Script ?

Fri, 21 Mar 2014 06:42:53 GMT

if(service_type==CPE)

{

SELECT COUNT(1)

-- INTO :lSaleCount

FROM geneva_admin.PVCUSTPRODUCTTARIFFDETAILS CPTD,geneva_admin.PVTARIFF TAR

WHERE CPTD.CUSTOMER_REF=:g_cCustomer_ref---customer_ref

AND CPTD.PRODUCT_SEQ =:g_iProdSeq ---product_seq

AND CPTD.TARIFF_ID = TAR.TARIFF_ID

AND UPPER(TAR.TARIFF_NAME) LIKE '%SALE%';

if (lSaleCount > 0)

{

EXEC SQL SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA

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

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd, geneva_admin.CUSTPRODUCTATTRDETAILS cpad, geneva_admin.PRODUCTATTRIBUTE pa

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

AND UPPER(prm.PRODUCT_ATTR_SEG)= :g_cProductAttrSeg ---FROM CUSTOMERATTRIBUTE TABLE GET THIS ATTRIBUTE

AND prm.PRODUCT_FAMILY_ID IN (SELECT product_family_id FROM geneva_admin.pvproductfamily

WHERE product_family_name = 'CPE Family')

AND prm.SERVICE_TYPE=cpad.ATTRIBUTE_VALUE

AND prm.SERVICE_TYPE_IDENTIFIER='Z'

AND prm.ATTRIBUTE_NAME=pa.ATTRIBUTE_BILL_NAME

AND cpad.PRODUCT_ID=pa.PRODUCT_ID

AND cpad.PRODUCT_ATTRIBUTE_SUBID=pa.PRODUCT_ATTRIBUTE_SUBID

AND cpad.CUSTOMER_REF=:g_cCustomer_ref ---customer_ref

AND cpad.PRODUCT_SEQ=:g_iProdSeq ---product seq

AND prm.product_family_id = :lProdfamId ---product family id

AND NVL(prm.CHARGE_TYPE,'P')= DECODE(:g_iCharge_type, 1, 'I', 2,'P','P')

AND NVL(prm.CUSTOMER_TYPE_ID,:g_cCustflag) = :g_cCustflag ---- FROM CUSTOMER TABLE FIND CUSTOMER TYPE _ID AND THEN JOIN WITH GSI_ADMIN.GSI_ADMIN.GSI_CUSTOMER_TYPE_MAP WHERE YOU WILL FIND CUSTOMER_TYPE_FLAG COLUMN WHICH IS JOINED AS CUSTOMER_TYPE_ID IN GSI_PRODUCT_REVENUE_MAP

AND (cpad.START_DAT <= to_date(:g_cBill_dtm_bs,'dd.mm.yyyy') ------ CHARGE START DATE FROM BILLPRODUCT CAHRGE TABLE

AND (cpad.END_DAT >= to_date(:g_cBill_dtm_bs,'dd.mm.yyyy') or cpad.END_DAT is NULL ));

if(NO_DATA)

{

EXEC SQL SELECT rd.GSI_REVENUE_CODE_ID, rd.PRODUCT_CODE, rd.PROFIT_CENTER, rd.DUNNING_AREA

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

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 ---FROM CUSTOMERATTRIBUTE TABLE GET THIS ATTRIBUTE

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

AND prm.PRODUCT_ID IS NULL

AND prm.PRODUCT_FAMILY_ID IS NULL;

}

else

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

--INTO :g_iGsiRev_Id,:lProductCode,:g_cProfCen,:lDunning_area_desc

FROM GSI_PRODUCT_REVENUE_MAP prm, GSI_REVENUE_DET rd, geneva_admin.CUSTPRODUCTATTRDETAILS cpad, geneva_admin.PRODUCTATTRIBUTE pa

WHERE prm.GSI_REVENUE_CODE_ID=rd.GSI_REVENUE_CODE_ID

AND UPPER(prm.PRODUCT_ATTR_SEG)= :g_cProductAttrSeg

AND prm.PRODUCT_FAMILY_ID IN (SELECT product_family_id FROM geneva_admin.pvproductfamily

WHERE product_family_name = 'CPE Family')

AND prm.SERVICE_TYPE=cpad.ATTRIBUTE_VALUE

AND prm.SERVICE_TYPE_IDENTIFIER='R'

AND prm.ATTRIBUTE_NAME=pa.ATTRIBUTE_BILL_NAME

AND cpad.PRODUCT_ID=pa.PRODUCT_ID

AND cpad.PRODUCT_ATTRIBUTE_SUBID=pa.PRODUCT_ATTRIBUTE_SUBID

AND cpad.CUSTOMER_REF=:g_cCustomer_ref ---- CUSTOMER_REF

AND cpad.PRODUCT_SEQ=:g_iProdSeq ---- PRODUCT_SEQ

AND prm.product_family_id = iprodfm_id ----prdocudt family 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 (cpad.START_DAT <= to_date(:g_cBill_dtm_bs,'dd.mm.yyyy')------ CHARGE START DATE FROM BILLPRODUCT CAHRGE TABLE

AND (cpad.END_DAT >= to_date(:g_cBill_dtm_bs,'dd.mm.yyyy') or cpad.END_DAT is NULL )

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 ---FROM CUSTOMERATTRIBUTE TABLE GET THIS ATTRIBUTE

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

AND prm.PRODUCT_ID IS NULL

AND prm.PRODUCT_FAMILY_ID IS NULL;

}

topic Stored Procedure into QV Script ? in QlikView

Stored Procedure into QV Script ?

Re: Stored Procedure into QV Script ?

Re: Stored Procedure into QV Script ?

Re: Stored Procedure into QV Script ?

Re: Stored Procedure into QV Script ?