Stored Procedure into QV Script ?

[#1]

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

[manojkulkarni]

You can directly call Stored Procedure itself in QLikVIew Script.

TableName:

SQL EXEC sp_GetYourData;

[#3]

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

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

[manojkulkarni]

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.

[#5]

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 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 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 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_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 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 prm.SERVICE_TYPE=:gServType                   ----service type

                    AND prm.PRODUCT_ID IS NULL

                    AND prm.PRODUCT_FAMILY_ID IS NULL;           

            }

          }

     }