If it won't be exactly 8 characters every time you need to change it to VARCHAR(8)
That will allow up to 8 characters but doesn't have to be exactly 8.
Dan
>
> Hello all,
>
> I am having a problem with prepared sth that uses
> fetchrow_array with two parameters passed in.
>
> The problem is as follows:
> in one of my tables I have a column STAT_DEF_ID ( CHAR(8) )
> which is also one of the four unique constraints for that
> table. I am able to select my rows into an array only if
> value passed to STAT_DEF_ID is exactly 8 char. if its less
> then 8 char I don't get any results back. Now I tried to
> leave trailing spaces and pass a value with blanks at the end
> of a string to constitute for 8 chars. No effect. However if
> I do execute that sql from SQLPLUS I get the desired results.
> Also I created another statement that selects everything from
> that table with no reference to STAT_DEF_ID but the other
> unique key. In that case I get all the data I want. Here are
> some perl code.
>
> # the following code works just fine since I don't have
> STAT_DEF_ID=? in a WHERE close my $sth_test =
> $dbh->prepare(q{ SELECT STAT_ID, STAT_DEF_ID, nvl(INST_MNEM,'N.A.'),
> nvl(FINSRL_TYP,'N.A.'), DENOM_CURR_CDE, ORG_ID, SUBDIV_ID,
> STAT_VAL_DTE, STAT_VAL_TMS, STAT_VAL_AMT,
> STAT_DEC_VAL_AMT,
> STAT_INT_VAL_NUM, STAT_CHAR_VAL_TXT
> FROM FT_T_ISST WHERE INSTR_ID=?
> ORDER BY STAT_DEF_ID,LAST_CHG_TMS DESC}) or die $dbh->errstr;
>
> $sth_test->execute($instr_id);
> while (my @row=$sth_test->fetchrow_array) {
> print join(',',@row)."\n";
> }
>
> ###############################
> # However this code don't return those records that have
> STAT_DEF_ID < than 8 chars long my $sth_test =
> $dbh->prepare(q{ SELECT STAT_ID, STAT_DEF_ID, nvl(INST_MNEM,'N.A.'),
> nvl(FINSRL_TYP,'N.A.'), DENOM_CURR_CDE, ORG_ID, SUBDIV_ID,
> STAT_VAL_DTE, STAT_VAL_TMS, STAT_VAL_AMT,
> STAT_DEC_VAL_AMT,
> STAT_INT_VAL_NUM, STAT_CHAR_VAL_TXT
> FROM FT_T_ISST WHERE INSTR_ID=? AND STAT_DEF_ID=?
> ORDER BY STAT_DEF_ID,LAST_CHG_TMS DESC}) or die $dbh->errstr;
>
> # $instr_id and $stat_def_id are obtained from a file
> $sth_test->execute($instr_id, $stat_def_id);
> while (my @row=$sth_test->fetchrow_array) {
> print join(',',@row)."\n";
> }
>
> NOTE: there are two prepared statements in a trace.txt I am
> executing the second one. As you can see the fetchrow_array
> works for 'FD12BFEE' and doesnt for 'FDPHONE'
>
>
> Thanx much for your help
>
> <<trace.txt>>
>
> Daniel Rozengurtel
> Analyst II - Data Mining/WHSE
> IT Clearing-Settlements
> tell: (646) 733-4242
> [EMAIL PROTECTED]
>
>
>
> _____________________________________________________________________
> IMPORTANT NOTICES:
> This message is intended only for the addressee.
> Please notify the sender by e-mail if you are not the
> intended recipient. If you are not the intended recipient,
> you may not copy, disclose, or distribute this message or its
> contents to any other person and any such actions may be unlawful.
>
> Banc of America Securities LLC("BAS") does not
> accept time sensitive, action-oriented messages or
> transaction orders, including orders to purchase or sell
> securities, via e-mail.
>
> BAS reserves the right to monitor and review the
> content of all messages sent to or from this e-mail address.
> Messages sent to or from this e-mail address may be stored on
> the BAS e-mail system.
>
>
>
