Re: Select not returning value some times
Date: Tue, 19 Feb 2008 22:07:42 +0100
Message-ID: <47bb4514$0$14357$e4fe514c@news.xs4all.nl>
<joshianant_at_gmail.com> schreef in bericht
news:c43d9b1c-89cc-4a08-b8db-57ff2d5092bd_at_n75g2000hsh.googlegroups.com...
On Feb 19, 2:52 pm, "shakespeare" <what..._at_xs4all.nl> wrote:
> <joshian..._at_gmail.com> schreef in
> berichtnews:e1841b2d-acf9-4a91-8d7e-0e14da18379a_at_d4g2000prg.googlegroups.com...
> On Feb 19, 9:50 am, "shakespeare" <what..._at_xs4all.nl> wrote:
>
>
>
>
>
> > <joshian..._at_gmail.com> schreef in
> > berichtnews:0082eddb-c207-48d8-b940-8c8f82156fa6_at_e6g2000prf.googlegroups.com...
> > On Feb 19, 3:25 am, "shakespeare" <what..._at_xs4all.nl> wrote:
>
> > > <joshian..._at_gmail.com> schreef in
> > > berichtnews:413d4cbe-4ea0-42da-9d1e-665db1d0e666_at_s8g2000prg.googlegroups.com...
>
> > > > Hi,
>
> > > > 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 */
> > > > BEGIN
> > > > SELECT ISO_CCY_CD into vsd_currency FROM CD_MAP_TBL WHERE
> > > > CURRENCY_CD
> > > > = v_n_currcd;
> > > > EXCEPTION
> > > > WHEN NO_DATA_FOUND THEN
> > > > vsd_currency := v_n_currcd;
> > > > END;
> > > > ==========================================================
>
> > > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > vsd_currency holds numeric currency code obtained from the source
> > table.
>
> > With the select statement, it is expected that ISO currency code
> > (column ISO_CCY_CD) gets overwritten
> > on previous numeric value of vsd_currency.
>
> > "WHERE CURRENCY_CD = v_n_currcd;" will fetch a row that has numeric
> > CURRENCY_CD equal to value of "v_n_currcd"
>
> > The numeric code is never more than 3 characters even though the
> > variable is varchar(30).
>
> > Thanks for taking time.
>
> > --------------------
> > Ok, but I never see you UPDATE any table... and still the assignment at
> > the
> > end seems useless. Yor proc wil continue with the numeric code if no
> > data
> > is
> > found.
>
> > Shakespeare- Hide quoted text -
>
> > - Show quoted text -
>
> I have not shown entire code. I don't think it is required. The ISO
> code is used later to insert record in another table.
> I agree that the assignment in case of NO_DATA_FOUND is not really
> required but it was added to prevent blank value.
>
> We do not want numeric code as downstream application cannot handle
> it.
>
> And this is an issue to only a handful of records (if at all) in about
> 5K records processed every day
>
> Regards
>
> ------------------------------
> If you don't want to end up with numeric values, don't assign a numeric
> value in the exception, but handle the exception.... you could start with
> logging the values that are not found, so you can correct your data set.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -
Sorry. I should have been more clear. Too many plates in the air.
The exception occurs for values that are already there. E.g. 099 numeric code translates to USD. It will work for hundreds of records but not for a couple of them. There is no pattern in occurrance of this exception. At this time I have nothing to blame except difference in length of variables as pointed out here and elsewhere.
We will do this change next time one other change is scheduled to go live. As I mentioned before, we were not able to reproduce this behavior in test environment.
Thanks again.
Ok, I understand. In the meantime, you could check your production environment for codes containing trailing spaces or other abnormailities. This could be a cause of your errors.
Shakespeare Received on Tue Feb 19 2008 - 15:07:42 CST