Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I want to convert SQL Stored procedure into qlikview script ?
You can directly call Stored Procedure itself in QLikVIew Script.
TableName:
SQL EXEC sp_GetYourData;
HI Manoj, In got the SQL Scripting in Stored procedure,
For that I want to Convert into Qlikview script...
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.
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;
}
}
}