Re: Select not returning value some times
Date: Tue, 19 Feb 2008 09:25:02 +0100
<joshianant_at_gmail.com> schreef in bericht
> We have a stored procedure in which we replace numeric internal
> currency code to ISO currency code using a lookup table. The procedure
> handles about 5K records and for few of them the currency remains
> numeric when inserted to target table. This causes exceptions in
> downstream system as it expects ISO codes.
> Here's section of the code:
> /* Get the ISO Currency Code if Currency code came in the file is
> number */
> v_n_currcd := vsd_currency; /* This is the numeric currency code */
> SELECT ISO_CCY_CD into vsd_currency FROM CD_MAP_TBL WHERE CURRENCY_CD
> = v_n_currcd;
> WHEN NO_DATA_FOUND THEN
> vsd_currency := v_n_currcd;
> We were unable to duplicate this issue in test environment even while
> processing 100K records.
> Table CD_MAP_TBL is defined as:
> SQL> desc CD_MAP_TBL;
> Name Null? Type
> ----------------------------------------- --------
> CURRENCY_CD NOT NULL VARCHAR2(3)
> EFFDT NOT NULL DATE
> EFF_STATUS NOT NULL VARCHAR2(1)
> ISO_CCY_CD NOT NULL VARCHAR2(3)
> LOAD_DTTM DATE
> LASTUPDDTTM DATE
> ERROR_FLAG NOT NULL VARCHAR2(1)
> Variables are declared as:
> v_n_currcd VARCHAR2(3);
> vsd_currency ST_DET.CURRENCY%Type; (This is VARCHAR2(30))
> Any ideas ?
> Thanks in advance
Could you pleas explain this piece of code? First you assign v_n_currcd := vsd_currency; /* This is the numeric currency code */
So both values are now the same, and appearantly either vsd_currrency was holding the numeric value, or the numeric value is assigned an ISO code?
Then you try to find a record with the isocode equal to vsd_currency, which
leads to the conclusion that vsd_currency WAS holding the iso-code, and you
HOPE to find a corresponding numeric value. If you don't find it (so no
values have changed in the select) you assign
vsd_currency := v_n_currcd;
but both values were already the same...... furthermore, you assign a varchar2(30) to a varchar2(3)..... but fortunately it will never be longer than 3, because of the previous assignment.
I don't get it.
Shakespeare Received on Tue Feb 19 2008 - 02:25:02 CST