Home » SQL & PL/SQL » SQL & PL/SQL » tuning package (Oracle 11g)
tuning package [message #582449] Thu, 18 April 2013 07:44 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have a package in a package I am calling two procedures.
If I execute a procedure it's taking 15 mins to complete execution.
It's deleting 2000000 records.
Total number of records in the table ORDER_TREND_SCORE is 210016314.
One index is created on the this table.

Please help me to improve the performance of this package.

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT."INTERNAL_SCORING_NANDU" IS
 PROCEDURE TrendScoring_nandu(pBUID       IN STAGING_ORDER_DATA.BUID%TYPE,
                         OrderNum    IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
                         ReturnValue OUT VARCHAR2);
PROCEDURE InsertTrend_nandu(pBUID        IN ORDER_TREND_SCORE.BUID%TYPE,
                        OrderNum     IN ORDER_TREND_SCORE.ORDER_NUM%TYPE,
                        Variable     IN ORDER_TREND_SCORE.TREND_VARIABLE%TYPE,
                        DeductPoints IN ORDER_TREND_SCORE.DEDUCTION_POINTS%TYPE,
                        FraudPct     IN ORDER_TREND_SCORE.FRAUD_PERCENTAGE%TYPE,
                        FraudWkdPct  IN ORDER_TREND_SCORE.FRAUD_WORKED_PERCENTAGE%TYPE);
END;
/

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT."INTERNAL_SCORING_NANDU" IS
PROCEDURE TrendScoring_nandu(pBUID       IN STAGING_ORDER_DATA.BUID%TYPE,
                         OrderNum    IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
                         ReturnValue OUT VARCHAR2) IS

    tLocalChannel   STAGING_ORDER_DATA.LOCAL_CHANNEL%TYPE;
    tZipCode        STAGING_ORDER_DATA.ZIP_CODE%TYPE;
    tCountryCode    STAGING_ORDER_DATA.COUNTRY_CODE%TYPE;
    tFreeEmail      STAGING_ONLINE_DATA.FREE_EMAIL_FLAG%TYPE;
    tPayCode        ORDERS.PAY_CODE%TYPE;
    tOrderAmt       ORDERS.ORDER_AMOUNT%TYPE;
    tProdDesc       ORDERS.PRODUCT_DESC%TYPE;
    tShipCode       ORDERS.SHIP_CODE%TYPE;
    tSrcSys         ORDERS.SOURCE_SYSTEM%TYPE;
    tSTDiff         ORDERS.ST_ADDRESS_DIFF_FLAG%TYPE;
    tCCDiff         ORDERS.CC_ADDRESS_DIFF_FLAG%TYPE;
    tBTDateDiff     NUMBER := 0;
    tAVS            VARCHAR2(50);
    tEmailDomain    STAGING_ONLINE_DATA.EMAIL_ADDRESS%TYPE;
    tCID            VARCHAR2(50);
    tIPContinent    STAGING_ONLINE_DATA.IP_CONTINENT%TYPE;
    tIPCountry      STAGING_ONLINE_DATA.IP_COUNTRY%TYPE;
    tEmailAddress   STAGING_ONLINE_DATA.EMAIL_ADDRESS%TYPE;
    tDeviceTimezone IOV_DEVICE_TRAN_DETAIL.DEVICE_TIMEZONE%TYPE;
    tDeviceBrowserLanguage IOV_DEVICE_TRAN_DETAIL.DEVICE_BROWSER_LANG%TYPE; 
    tDeviceOS IOV_DEVICE_TRAN_DETAIL.DEVICE_OS%TYPE; 
    tIPRoutingType  STAGING_ONLINE_DATA.IP_ROUTING_TYPE%TYPE;
    tScore          SCORE.VENDOR_COMBINED_SCORE%TYPE;
    tAmtBucketRange VARCHAR2(50);
    tTrendAmtBucket MASTER_BUCKET.BUCKET_TYPE%TYPE := 'Trend Order Amount Bucket';
    tZipDigits      CONFIG_PARAMS.VARIABLE_NAME%TYPE := 'TREND_ZIP_DIGITS';
    tCleanProdDesc  ORDERS.PRODUCT_DESC%TYPE;
    tBIN            TEMP_INTSCORING_CC.BIN_NUM%TYPE;
    tScoringID      STAGING_ORDER_DATA.SCORING_ID%TYPE;
    tMinOrderNum    STAGING_ORDER_DATA.ORDER_NUM%TYPE;
    tSKUNum         STAGING_ORDER_DATA.SKU_NUM%TYPE;
    tExists         NUMBER := 0;
    tCount          NUMBER := 0;
    tIncludeZipDigits NUMBER := 3;
    tDeductionPoints  TREND.DEDUCTION_POINTS%TYPE := 0;
    tFrdPct           TREND.FRAUD_TOTAL_PERCENTAGE%TYPE := 0;
    tFrdWkdPct        TREND.FRAUD_WORKED_PERCENTAGE%TYPE := 0;

    tTrendScore NUMBER := 5000;

  BEGIN

    --get order header------------------------------------------------------------
    ReturnValue := 'get order header';

    SELECT stgOrder.LOCAL_CHANNEL,
           TRUNC(SYSDATE - ADDRESS_CREATE_DATE) "Dy"
      INTO tLocalChannel, tBTDateDiff
      FROM STAGING_ORDER_DATA stgOrder
     WHERE stgOrder.BUID = pBUID AND stgOrder.ORDER_NUM = OrderNum AND
           stgOrder.ADDRESS_TYPE = 'B';

    SELECT stgOrder.ZIP_CODE,
           stgOrder.COUNTRY_CODE,
           NVL(stgOnline.FREE_EMAIL_FLAG, ''),
           stgOnline.IP_CONTINENT,
           stgOnline.IP_COUNTRY,
           stgOnline.EMAIL_ADDRESS,
           stgOnline.IP_ROUTING_TYPE
      INTO tZipCode,
           tCountryCode,
           tFreeEmail,
           tIPContinent,
           tIPCountry,
           tEmailAddress,
           tIPRoutingType
      FROM STAGING_ORDER_DATA stgOrder LEFT OUTER JOIN STAGING_ONLINE_DATA stgOnline ON stgOrder.BUID = stgOnline.BUID AND stgOrder.ORDER_NUM = stgOnline.ORDER_NUM
     WHERE stgOrder.ADDRESS_TYPE = 'S' AND stgOrder.BUID = pBUID AND
           stgOrder.ORDER_NUM = OrderNum;
           
       SELECT iov.DEVICE_TIMEZONE,
           iov.DEVICE_BROWSER_LANG,
           iov.DEVICE_OS
      INTO tDeviceTimezone,
           tDeviceBrowserLanguage,
           tDeviceOS
      FROM STAGING_ORDER_DATA stgOrder LEFT OUTER JOIN Iov_Device_Tran_Detail iov ON stgOrder.BUID = iov.BUID AND stgOrder.ORDER_NUM = iov.ORDER_NUM
     WHERE stgOrder.ADDRESS_TYPE = 'S' AND stgOrder.BUID = pBUID AND
           stgOrder.ORDER_NUM = OrderNum;


    SELECT (ord.ORDER_AMOUNT * exchg.EXCHANGE_RATE),
           ord.PAY_CODE,
           ord.PRODUCT_DESC,
           ord.SHIP_CODE,
           ord.SOURCE_SYSTEM,
           ord.ST_ADDRESS_DIFF_FLAG,
           ord.CC_ADDRESS_DIFF_FLAG,
           NVL(ord.SKU_NUM,''),
           NVL(cc.AVS_CC_CODE, ''),
           NVL(cc.CID_CODE, ''),
           NVL(s.VENDOR_COMBINED_SCORE, -1)
      INTO tOrderAmt,
           tPayCode,
           tProdDesc,
           tShipCode,
           tSrcSys,
           tSTDiff,
           tCCDiff,
           tSKUNum,
           tAVS,
           tCID,
           tScore
      FROM MASTER_CURRENCY exchg,
           ORDERS          ord LEFT OUTER JOIN (SELECT BUID,
                                                       ORDER_NUM,
                                                       MIN(AVS_CC_CODE) AS AVS_CC_CODE,
                                                       MIN(CID_CODE) AS CID_CODE
                                                  FROM CREDIT_CARD
                                                 WHERE BUID = pBUID AND
                                                       ORDER_NUM = OrderNum
                                                 GROUP BY BUID, ORDER_NUM)          cc ON ord.BUID = cc.BUID AND ord.ORDER_NUM = cc.ORDER_NUM LEFT OUTER JOIN SCORE s ON ord.BUID = s.BUID AND ord.ORDER_NUM = s.ORDER_NUM
     WHERE ord.CURRENCY_CODE = exchg.CURRENCY_CODE AND ord.BUID = pBUID AND
           ord.ORDER_NUM = OrderNum;

    --new code...added 7/10/2008...use primary order amount and product desc
    SELECT NVL(SCORING_ID, 0)
      INTO tScoringID
      FROM STAGING_ORDER_DATA
     WHERE BUID = pBUID AND ORDER_NUM = OrderNum AND ADDRESS_TYPE = 'S';

    IF tScoringID <> 0 THEN
      SELECT MIN(ORDER_NUM)
        INTO tMinOrderNum
        FROM STAGING_ORDER_DATA
       WHERE SCORING_ID = tScoringID AND ADDRESS_TYPE = 'S';

      SELECT (ord.ORDER_AMOUNT * exchg.EXCHANGE_RATE), ord.PRODUCT_DESC
        INTO tOrderAmt, tProdDesc
        FROM ORDERS ord, MASTER_CURRENCY exchg
       WHERE ord.CURRENCY_CODE = exchg.CURRENCY_CODE AND ord.BUID = pBUID AND
             ORDER_NUM = tMinOrderNum;
    END IF;
    ------------------------------------------------------------------------------
    --SKU Num---------------------------------------------------------
    ReturnValue := 'sku Num';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'sku num' AND
           UPPER(VARIABLE_VALUE) = tSKUNum AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'sku num' AND
             UPPER(VARIABLE_VALUE) = tSKUNum AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'sku num: ' || tSKUNum,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;

    --product degredation---------------------------------------------------------
    ReturnValue := 'product degredation';

    IF tCount = 0 THEN

     tCleanProdDesc := fn_cleanproddesc(UPPER(tProdDesc));
     SELECT COUNT(BUID)
       INTO tCount
       FROM TREND
      WHERE VARIABLE_NAME = 'product' AND
           UPPER(VARIABLE_VALUE) = tCleanProdDesc AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

     IF tCount > 0 THEN
       SELECT DEDUCTION_POINTS,
              FRAUD_TOTAL_PERCENTAGE,
              FRAUD_WORKED_PERCENTAGE
         INTO tDeductionPoints, tFrdPct, tFrdWkdPct
         FROM TREND
        WHERE VARIABLE_NAME = 'product' AND
             UPPER(VARIABLE_VALUE) = tCleanProdDesc AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

       tTrendScore := tTrendScore - tDeductionPoints;

       InsertTrend_nandu(pBUID,
                  OrderNum,
                  'product: ' || tCleanProdDesc,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
     END IF;
    END IF;
    
    tCount := 0;

    ------------------------------------------------------------------------------

    --avs code--------------------------------------------------------------------
    ReturnValue := 'avs code';

    SELECT 'pc: ' || tPayCode || ', avs: ' ||
           DECODE(tAVS, NULL, ' ', '', ' ', tAVS)
      INTO tAVS
      FROM DUAL;

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'avs code' AND VARIABLE_VALUE = tAVS AND
           BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel AND
           DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'avs code' AND VARIABLE_VALUE = tAVS AND
             BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'avs code: ' || tAVS,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --cid code--------------------------------------------------------------------
    ReturnValue := 'cid code';

    SELECT 'pc: ' || tPayCode || ', cid: ' ||
           DECODE(tCID, NULL, ' ', '', ' ', tCID)
      INTO tCID
      FROM DUAL;

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'cid code' AND VARIABLE_VALUE = tCID AND
           BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel AND
           DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'cid code' AND VARIABLE_VALUE = tCID AND
             BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'cid code: ' || tCID,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --pay code--------------------------------------------------------------------
    ReturnValue := 'pay code';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'payment code' AND VARIABLE_VALUE = tPayCode AND
           BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel AND
           DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'payment code' AND VARIABLE_VALUE = tPayCode AND
             BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'pay code: ' || tPayCode,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --ship code--------------------------------------------------------------------
    ReturnValue := 'ship code';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'ship code' AND VARIABLE_VALUE = tShipCode AND
           BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel AND
           DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'ship code' AND VARIABLE_VALUE = tShipCode AND
             BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'ship code: ' || tShipCode,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --source system flag----------------------------------------------------------
    ReturnValue := 'source system flag';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'source system flag' AND
           VARIABLE_VALUE = tSrcSys AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'source system flag' AND
             VARIABLE_VALUE = tSrcSys AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'source system flag: ' || tSrcSys,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --st address diff flag--------------------------------------------------------
    ReturnValue := 'st address diff flag';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'st address diff flag' AND
           VARIABLE_VALUE = tSTDiff AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'st address diff flag' AND
             VARIABLE_VALUE = tSTDiff AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'st address diff flag: ' || tSTDiff,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --cc address diff flag--------------------------------------------------------
    ReturnValue := 'cc address diff flag';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'cc address diff flag' AND
           VARIABLE_VALUE = tCCDiff AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'cc address diff flag' AND
             VARIABLE_VALUE = tCCDiff AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'cc address diff flag: ' || tCCDiff,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --order amt bucket------------------------------------------------------------
    ReturnValue := 'order amt bucket';

    SELECT COUNT(BUID)
      INTO tCount
      FROM MASTER_BUCKET
     WHERE BUID = pBUID AND BUCKET_TYPE = tTrendAmtBucket AND
           tOrderAmt >= TO_NUMBER(RANGE_START) AND
           tOrderAmt <= TO_NUMBER(RANGE_END);

    IF tCount > 0 THEN
      SELECT RANGE_START || '-' || RANGE_END
        INTO tAmtBucketRange
        FROM MASTER_BUCKET
       WHERE BUID = pBUID AND BUCKET_TYPE = tTrendAmtBucket AND
             tOrderAmt >= TO_NUMBER(RANGE_START) AND
             tOrderAmt <= TO_NUMBER(RANGE_END);

      SELECT COUNT(BUID)
        INTO tCount
        FROM TREND
       WHERE VARIABLE_NAME = 'order amt bkt' AND
             VARIABLE_VALUE = tAmtBucketRange AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

      IF tCount > 0 THEN
        SELECT DEDUCTION_POINTS,
               FRAUD_TOTAL_PERCENTAGE,
               FRAUD_WORKED_PERCENTAGE
          INTO tDeductionPoints, tFrdPct, tFrdWkdPct
          FROM TREND
         WHERE VARIABLE_NAME = 'order amt bkt' AND
               VARIABLE_VALUE = tAmtBucketRange AND BUID = pBUID AND
               LOCAL_CHANNEL = tLocalChannel;

        tTrendScore := tTrendScore - tDeductionPoints;

        InsertTrend_nandu(pBUID,
                    OrderNum,
                    'order amt bkt: ' || tAmtBucketRange,
                    tDeductionPoints,
                    tFrdPct,
                    tFrdWkdPct);
      END IF;
    END IF;
    ------------------------------------------------------------------------------

    --metro area------------------------------------------------------------------
    ReturnValue := 'metro area';

    GetConfigurationItemValue(pBUID, tZipDigits, tIncludeZipDigits);

    SELECT SUBSTR(REPLACE(tZipCode, ' ', ''), 1, tIncludeZipDigits)
      INTO tZipCode
      FROM DUAL;

    tCount := 0;

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'metro area' AND
           UPPER(VARIABLE_VALUE) = UPPER(tZipCode) AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'metro area' AND
             UPPER(VARIABLE_VALUE) = UPPER(tZipCode) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'metro area: ' || UPPER(tZipCode),
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --country code----------------------------------------------------------------
    ReturnValue := 'country code';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'country code' AND
           UPPER(VARIABLE_VALUE) = UPPER(tCountryCode) AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'country code' AND
             UPPER(VARIABLE_VALUE) = UPPER(tCountryCode) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'country code: ' || UPPER(tCountryCode),
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------


    --cc bin----------------------------------------------------------------------
    ReturnValue := 'cc bin';

    SELECT COUNT(trd.BUID)
      INTO tCount
      FROM TREND trd, TEMP_INTSCORING_CC tmpCC
     WHERE trd.VARIABLE_NAME = 'cc bin'
           --trend table should have credit card bins in clear text
           AND trd.VARIABLE_VALUE = tmpCC.BIN_NUM AND trd.BUID = tmpCC.BUID AND
           tmpCC.BUID = pBUID AND tmpCC.ORDER_NUM = OrderNum AND
           trd.LOCAL_CHANNEL = tLocalChannel AND
           trd.DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT points, frdpct, frdwkdpct, bin
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct, tBIN
        FROM (SELECT trd.DEDUCTION_POINTS        AS points,
                     trd.FRAUD_TOTAL_PERCENTAGE  AS frdpct,
                     trd.FRAUD_WORKED_PERCENTAGE AS frdwkdpct,
                     tmpCC.BIN_NUM               AS bin
                FROM TREND trd, TEMP_INTSCORING_CC tmpCC
               WHERE trd.VARIABLE_NAME = 'cc bin' AND
                     trd.VARIABLE_VALUE = tmpCC.BIN_NUM AND
                     trd.BUID = tmpCC.BUID AND tmpCC.BUID = pBUID AND
                     tmpCC.ORDER_NUM = OrderNum AND
                     trd.LOCAL_CHANNEL = tLocalChannel
               ORDER BY trd.DEDUCTION_POINTS DESC)
       WHERE ROWNUM = 1;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'cc bin: ' || tBIN,
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ------------------------------------------------------------------------------

    --reason codes-----------------------------Iovation changes to perform trend scoring on reason code based on trend scoring enabled flag-----------------------------------
    ReturnValue := 'reason codes';

    SELECT COUNT(trd.BUID)
      INTO tCount
      FROM TREND trd, MASTER_REASON mstr, ORDER_SCORE_REASON ordScore
     WHERE trd.VARIABLE_NAME = 'reason codes' AND
           trd.VARIABLE_VALUE = mstr.ADDRESS_TYPE || mstr.REASON_CODE AND
           mstr.REASON_ID = ordScore.REASON_ID AND
           ordScore.ORDER_NUM = OrderNum AND ordScore.BUID = pBUID AND
           trd.BUID = pBUID AND trd.LOCAL_CHANNEL = tLocalChannel AND
           trd.DEDUCTION_POINTS IS NOT NULL AND mstr.trend_scoring_enabled='Y';

    IF tCount > 0 THEN
      SELECT SUM(trd.DEDUCTION_POINTS)
        INTO tDeductionPoints
        FROM TREND trd, MASTER_REASON mstr, ORDER_SCORE_REASON ordScore
       WHERE trd.VARIABLE_NAME = 'reason codes' AND
             trd.VARIABLE_VALUE = mstr.ADDRESS_TYPE || mstr.REASON_CODE AND
             mstr.REASON_ID = ordScore.REASON_ID AND
             ordScore.ORDER_NUM = OrderNum AND ordScore.BUID = pBUID AND
             trd.BUID = pBUID AND trd.LOCAL_CHANNEL = tLocalChannel AND mstr.trend_scoring_enabled='Y';

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      --todo: insert into order_trend_score table here
      --do it manually here as we might have multiple rows and cannot use the InsertTrend_nandu SP
      SELECT COUNT(*) INTO tExists FROM ORDER_TREND_SCORE WHERE BUID = pBUID AND ORDER_NUM = OrderNum AND TREND_VARIABLE LIKE 'reason codes%';
      
      IF tExists > 0 THEN
         DELETE FROM ORDER_TREND_SCORE WHERE BUID = pBUID AND ORDER_NUM = OrderNum AND TREND_VARIABLE LIKE 'reason codes%';
      END IF;    
      
      INSERT INTO ORDER_TREND_SCORE
        (BUID,
         ORDER_NUM,
         TREND_VARIABLE,
         DEDUCTION_POINTS,
         FRAUD_PERCENTAGE,
         FRAUD_WORKED_PERCENTAGE)
        (SELECT pBUID,
                OrderNum,
                trd.VARIABLE_NAME || ': ' || trd.VARIABLE_VALUE,
                trd.DEDUCTION_POINTS,
                trd.FRAUD_TOTAL_PERCENTAGE,
                trd.FRAUD_WORKED_PERCENTAGE
           FROM TREND trd, MASTER_REASON mstr, ORDER_SCORE_REASON ordScore
          WHERE trd.VARIABLE_NAME = 'reason codes' AND
                trd.VARIABLE_VALUE = mstr.ADDRESS_TYPE || mstr.REASON_CODE AND
                mstr.REASON_ID = ordScore.REASON_ID AND
                ordScore.ORDER_NUM = OrderNum AND ordScore.BUID = pBUID AND
                trd.BUID = pBUID AND trd.LOCAL_CHANNEL = tLocalChannel);

    END IF;
    ------------------------------------------------------------------------------

    --r2--------------------------------------------------------------------------
    --ip country--------------------------------------------------------------
    ReturnValue := 'ip country';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'ip country' AND
           UPPER(VARIABLE_VALUE) = UPPER(tIPCountry) AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'ip country' AND
             UPPER(VARIABLE_VALUE) = UPPER(tIPCountry) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      --do not reset tcount here. IP continent is dependant on this.

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'ip country: ' || UPPER(tIPCountry),
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ---------------------------------------------------------------------------
    
    --ip continent-------------------------------------------------------------
    IF tCount = 0 THEN
      ReturnValue := 'ip continent';

      SELECT COUNT(BUID)
        INTO tCount
        FROM TREND
       WHERE VARIABLE_NAME = 'ip continent' AND
             UPPER(VARIABLE_VALUE) = UPPER(tIPContinent) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

      IF tCount > 0 THEN
        SELECT DEDUCTION_POINTS,
               FRAUD_TOTAL_PERCENTAGE,
               FRAUD_WORKED_PERCENTAGE
          INTO tDeductionPoints, tFrdPct, tFrdWkdPct
          FROM TREND
         WHERE VARIABLE_NAME = 'ip continent' AND
               UPPER(VARIABLE_VALUE) = UPPER(tIPContinent) AND BUID = pBUID AND
               LOCAL_CHANNEL = tLocalChannel;

        tTrendScore := tTrendScore - tDeductionPoints;

        InsertTrend_nandu(pBUID,
                    OrderNum,
                    'ip continent: ' || UPPER(tIPContinent),
                    tDeductionPoints,
                    tFrdPct,
                    tFrdWkdPct);
      END IF;
    END IF;

    tCount := 0;
    ---------------------------------------------------------------------------
    
    --- EMAIL DOMAIN -- EMAIL ADDRESS -- IOVATION CHANGES -----
    
    tEmailDomain := substr(LOWER(tEmailAddress), instr(LOWER(tEmailAddress), '@', 1,1)+1, length(LOWER(tEmailAddress)));
    ReturnValue := 'email domain';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'email domain' AND
           UPPER(VARIABLE_VALUE) = UPPER(tEmailDomain) AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'email domain' AND
             UPPER(VARIABLE_VALUE) = UPPER(tEmailDomain) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      --do not reset tcount here. free email flag is dependant on this.

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'email domain: ' || UPPER(tEmailDomain),
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    
    --free email flag-------------------------------------------------------------
    IF tCount = 0 THEN
        ReturnValue := 'free email flag';
    
        SELECT COUNT(BUID)
          INTO tCount
          FROM TREND
         WHERE VARIABLE_NAME = 'free email flag' AND
               VARIABLE_VALUE = tFreeEmail AND BUID = pBUID AND
               LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;
    
        IF tCount > 0 THEN
          SELECT DEDUCTION_POINTS,
                 FRAUD_TOTAL_PERCENTAGE,
                 FRAUD_WORKED_PERCENTAGE
            INTO tDeductionPoints, tFrdPct, tFrdWkdPct
            FROM TREND
           WHERE VARIABLE_NAME = 'free email flag' AND
                 VARIABLE_VALUE = tFreeEmail AND BUID = pBUID AND
                 LOCAL_CHANNEL = tLocalChannel;
    
          tTrendScore := tTrendScore - tDeductionPoints;
        
          InsertTrend_nandu(pBUID,
                      OrderNum,
                      'free email flag: ' || tFreeEmail,
                      tDeductionPoints,
                      tFrdPct,
                      tFrdWkdPct);
        END IF;
    END IF;

    tCount := 0;

    ---------------------------------------------------------------------------
    
    --- DEVICE TIMEZONE--IOVATION CHANGES-----
    
    ReturnValue := 'device timezone';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'device timezone' AND
           UPPER(VARIABLE_VALUE) = UPPER(tDeviceTimezone) AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'device timezone' AND
             UPPER(VARIABLE_VALUE) = UPPER(tDeviceTimezone) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'device timezone: ' || UPPER(tDeviceTimezone),
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ---------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------
    
    --- DEVICE browser language--IOVATION CHANGES-----
    
    ReturnValue := 'device browser language';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'device browser language' AND
           UPPER(VARIABLE_VALUE) = UPPER(tDeviceBrowserLanguage) AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'device browser language' AND
             UPPER(VARIABLE_VALUE) = UPPER(tDeviceBrowserLanguage) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'device browser language: ' || UPPER(tDeviceBrowserLanguage),
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ---------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------
    
    --- DEVICE OS--IOVATION CHANGES-----
    
    ReturnValue := 'device OS';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'device OS' AND
           UPPER(VARIABLE_VALUE) = UPPER(tDeviceOS) AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'device OS' AND
             UPPER(VARIABLE_VALUE) = UPPER(tDeviceOS) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'device OS: ' || UPPER(tDeviceOS),
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ---------------------------------------------------------------------------
    


    




    --ip routing type----------------------------------------------------------
    ReturnValue := 'ip routing type';

    SELECT COUNT(BUID)
      INTO tCount
      FROM TREND
     WHERE VARIABLE_NAME = 'ip routing type' AND
           UPPER(VARIABLE_VALUE) = UPPER(tIPRoutingType) AND BUID = pBUID AND
           LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

    IF tCount > 0 THEN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'ip routing type' AND
             UPPER(VARIABLE_VALUE) = UPPER(tIPRoutingType) AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel;

      tTrendScore := tTrendScore - tDeductionPoints;

      tCount := 0;

      InsertTrend_nandu(pBUID,
                  OrderNum,
                  'ip routing type: ' || UPPER(tIPRoutingType),
                  tDeductionPoints,
                  tFrdPct,
                  tFrdWkdPct);
    END IF;
    ---------------------------------------------------------------------------

    --age of account-----------------------------------------------------------
    ReturnValue     := 'age of account';
    tTrendAmtBucket := 'Trend Account Age Bucket';

    SELECT COUNT(BUID)
      INTO tCount
      FROM MASTER_BUCKET
     WHERE BUID = pBUID AND BUCKET_TYPE = tTrendAmtBucket AND
           tBTDateDiff >= TO_NUMBER(RANGE_START) AND
           tBTDateDiff <= TO_NUMBER(RANGE_END);

    IF tCount > 0 THEN
      SELECT RANGE_START || '-' || RANGE_END
        INTO tAmtBucketRange
        FROM MASTER_BUCKET
       WHERE BUID = pBUID AND BUCKET_TYPE = tTrendAmtBucket AND
             tBTDateDiff >= TO_NUMBER(RANGE_START) AND
             tBTDateDiff <= TO_NUMBER(RANGE_END);

      SELECT COUNT(BUID)
        INTO tCount
        FROM TREND
       WHERE VARIABLE_NAME = 'age of account' AND
             VARIABLE_VALUE = tAmtBucketRange AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel AND DEDUCTION_POINTS IS NOT NULL;

      IF tCount > 0 THEN
        SELECT DEDUCTION_POINTS,
               FRAUD_TOTAL_PERCENTAGE,
               FRAUD_WORKED_PERCENTAGE
          INTO tDeductionPoints, tFrdPct, tFrdWkdPct
          FROM TREND
         WHERE VARIABLE_NAME = 'age of account' AND
               VARIABLE_VALUE = tAmtBucketRange AND BUID = pBUID AND
               LOCAL_CHANNEL = tLocalChannel;

        tTrendScore := tTrendScore - tDeductionPoints;

        InsertTrend_nandu(pBUID,
                    OrderNum,
                    'age of account: ' || tAmtBucketRange,
                    tDeductionPoints,
                    tFrdPct,
                    tFrdWkdPct);
      END IF;
    END IF;
    ------------------------------------------------------------------------------
    --end r2-----------------------------------------------------------------------

    --conclude trend scoring---------------------------------------------------
    ReturnValue := 'concluding trend scoring';

    IF tTrendScore < 1 THEN
      tTrendScore := 1;
    END IF;

    SetScores_ram(pBUID, OrderNum, 'T', tTrendScore);

    --if no errors clear this
    ReturnValue := '';
    ------------------------------------------------------------------------------

  EXCEPTION
    WHEN OTHERS THEN

      /*    ROLLBACK;

      DELETE
      FROM TEMP_INTSCORING_CC tmpCC
      WHERE tmpCC.BUID = pBUID AND tmpCC.ORDER_NUM = OrderNum;

      COMMIT;*/

      ReturnValue := SQLERRM || '|' || ReturnValue;

  END TrendScoring_nandu;
  PROCEDURE InsertTrend_nandu(pBUID        IN ORDER_TREND_SCORE.BUID%TYPE,
                        OrderNum     IN ORDER_TREND_SCORE.ORDER_NUM%TYPE,
                        Variable     IN ORDER_TREND_SCORE.TREND_VARIABLE%TYPE,
                        DeductPoints IN ORDER_TREND_SCORE.DEDUCTION_POINTS%TYPE,
                        FraudPct     IN ORDER_TREND_SCORE.FRAUD_PERCENTAGE%TYPE,
                        FraudWkdPct  IN ORDER_TREND_SCORE.FRAUD_WORKED_PERCENTAGE%TYPE) IS
                       
  tExists NUMBER:=0;
  BEGIN

    SELECT COUNT(*) INTO tExists FROM ORDER_TREND_SCORE WHERE BUID = pBUID AND ORDER_NUM = OrderNum AND TREND_VARIABLE = Variable;

    IF tExists > 0 THEN
       DELETE FROM ORDER_TREND_SCORE WHERE BUID = pBUID AND ORDER_NUM = OrderNum AND TREND_VARIABLE = Variable;       
    END IF;
        
    INSERT INTO ORDER_TREND_SCORE
      (BUID,
       ORDER_NUM,
       TREND_VARIABLE,
       DEDUCTION_POINTS,
       FRAUD_PERCENTAGE,
       FRAUD_WORKED_PERCENTAGE)
    VALUES
      (pBUID, OrderNum, Variable, DeductPoints, FraudPct, FraudWkdPct);

  END InsertTrend_nandu;
END;
/

Please help me.
Thanks in advance.
Re: tuning package [message #582453 is a reply to message #582449] Thu, 18 April 2013 08:00 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
You appear to have a lot of code of the form:

SELECT COUNT(*)
FROM 
WHERE <conditions>

IF count > 0 THEN

  SELECT columns
  FROM
  WHERE <same conditions>
  
  do something
  
END IF;


This should be changed in all cases to:
BEGIN

  SELECT columns
  FROM
  WHERE <same conditions>
    
  do something
  
EXCEPTION WHEN no_data_found THEN
  NULL;
END;


That'll seriously reduce the amount of code you've got and speed up the process.

You also have some code like:
SELECT 'pc: ' || tPayCode || ', cid: ' ||
           DECODE(tCID, NULL, ' ', '', ' ', tCID)
      INTO tCID
      FROM DUAL;

That should just be:
tCID := 'pc: ' || tPayCode || ', cid: ' ||
           DECODE(tCID, NULL, ' ', '', ' ', tCID);

Never select from dual in pl/sql if you don't need to.
Re: tuning package [message #582454 is a reply to message #582453] Thu, 18 April 2013 08:04 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Thu, 18 April 2013 09:00

tCID := 'pc: ' || tPayCode || ', cid: ' ||
           DECODE(tCID, NULL, ' ', '', ' ', tCID);



I believe you will not be able to use DECODE in an assignment like that. Maybe 11g is different, but up through 10g it cannot be done.
Re: tuning package [message #582455 is a reply to message #582454] Thu, 18 April 2013 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can replace DECODE by NVL in this case: NVL(tCID, ' ') (otherwise you can replace it by CASE).


regards
Michel
Re: tuning package [message #582457 is a reply to message #582454] Thu, 18 April 2013 08:34 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
joy_division wrote on Thu, 18 April 2013 14:04
cookiemonster wrote on Thu, 18 April 2013 09:00

tCID := 'pc: ' || tPayCode || ', cid: ' ||
           DECODE(tCID, NULL, ' ', '', ' ', tCID);



I believe you will not be able to use DECODE in an assignment like that. Maybe 11g is different, but up through 10g it cannot be done.


D'oh.

But as Michel points out this is just a convoluted way of writing nvl.
Re: tuning package [message #582479 is a reply to message #582457] Thu, 18 April 2013 12:33 Go to previous message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Thu, 18 April 2013 09:34
joy_division wrote on Thu, 18 April 2013 14:04
cookiemonster wrote on Thu, 18 April 2013 09:00

tCID := 'pc: ' || tPayCode || ', cid: ' ||
           DECODE(tCID, NULL, ' ', '', ' ', tCID);



I believe you will not be able to use DECODE in an assignment like that. Maybe 11g is different, but up through 10g it cannot be done.


D'oh.

But as Michel points out this is just a convoluted way of writing nvl.


D'oh to me too. I didn't even bother looking at what the DECODE was doing. Shame on me.
Previous Topic: Open cursor statement getting stuck (V.interesting issue)
Next Topic: Problem with SUM function query
Goto Forum:
  


Current Time: Fri Aug 22 17:36:42 CDT 2014

Total time taken to generate the page: 0.41169 seconds