4 Replies Latest reply: Mar 21, 2014 2:42 AM by Helen Betty RSS

    Stored Procedure into QV Script ?

    Helen Betty

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

        • Re: Stored Procedure into QV Script ?
          Manoj Kulkarni

          You can directly call Stored Procedure itself in QLikVIew Script.

           

          TableName:

          SQL EXEC sp_GetYourData;

            • Re: Stored Procedure into QV Script ?
              Helen Betty

              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 ?
                  Manoj Kulkarni

                  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 ?
                      Helen Betty

                      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;           

                                  }

                                }

                           }