Re: Same query, different results

On 19.04.2007 10:41, Thorsten Jens wrote:
> take these two PL/SQL snippets:
>
> declare
> pznr_tnsw VARCHAR2(200);
> pdoknr VARCHAR2(200) := 'D1.4655-00-00-00.00-00';
> begin
> SELECT zvz_tnswhdw_zchnr
> INTO pznr_tnsw
> FROM pp_info.zvz_tnswhdw
> WHERE zvz_tnswhdw_doknr = pdoknr AND
> zvz_tnswhdw_auf = '51398';
> end;
>
> vs.
>
> declare
> pznr_tnsw VARCHAR2(200);
> pdoknr VARCHAR2(200) := 'D1.4655-00-00-00.00-00';
> begin
> SELECT zvz_tnswhdw_zchnr
> INTO pznr_tnsw
> FROM pp_info.zvz_tnswhdw
> WHERE zvz_tnswhdw_doknr = 'D1.4655-00-00-00.00-00' AND
> zvz_tnswhdw_auf = '51398';
> end;
>
> The only difference is that in #1 the string to compare comes from a
> variable, while in #2 it is specified directly.
>
> Running #1 leads to ORA-01403, "no data found" -- while #2 leads to
> ORA-01422, "exact fetch returns more than requested number of rows".
> Why that? Am I doing something wrong here? There is more than one
> matching row, so I was expecting #1 to return ORA-01422 as well.
>
> Oracle 9.2.0.7.0 EE on Solaris 64bit.

Do you happen to have a column pdoknr in that table?

        robert Received on Thu Apr 19 2007 - 04:27:05 CDT