Dump on single-row subquery

[Former Member]

Hello,

I'm using the following select statement, which causes a dump "DBIF_RSQL_SQL_ERROR" (ORA-01427: single-row subquery returns more than one row) and I don't know why:

DATA: atbez LIKE cabnt-atbez.

SELECT SINGLE atbez FROM cabnt INTO atbez

WHERE atinn = '0000002134'

AND spras = 'D'

AND adzhl =

( SELECT MAX( adzhl ) FROM cabnt WHERE atinn = cabnt~atinn

AND spras = 'D'

GROUP BY atinn spras ).

Why does the subquery return more than one row, if ATINN, SPRAS and ADZHL are the only key fields of table CABNT?

Can anybody help me?

Thanks

Daniel

[Former Member]

Hello Daniel,

I guess, writing the code in below fashion can solve the dump,

SELECT SINGLE atbez FROM cabnt INTO atbez

WHERE atinn = cabnt~atinn

AND spras = 'D'

AND adzhl =

( SELECT MAX( adzhl ) FROM cabnt WHERE atinn = '0000002134'

AND spras = 'D'

GROUP BY atinn spras ).

Let me know if that works!

Thanks,

Abhishek

[Former Member]

Hello Abhishek,

it really works. Does that mean, the subquery is executed before the outer select?

Thanks

Daniel

Edited by: Daniel Brack on Nov 17, 2010 1:18 PM

I don't get the dump with your version, but I don't think it works correct, because when I replace MAX by MIN it selects a totally different record (with other value for ATINN for example).

[Former Member]

Hi Daniel,

Instead of writing a sub query, write a separate query to get max of adzhl

*--To get adzhal possible values for each atinn and spras

select adzhl

from cabnt

into t_cabnt

where atinn = p_atinn and

spras = p_spras.

sort t_cabnt by atinn ascending spras ascending adzhl descending.

delete adjacent duplicates from t_cabnt by comparing adzhl.

Above code will return you the max value for adzhl for each atinn and spras. Now you can write the main query

SELECT SINGLE atbez FROM cabnt INTO atbez

for all entries in t_cabnt

WHERE atinn = '0000002134'

AND spras = 'D'

AND adzhl = t_cabnt-adzhl.

I hope your problem is solved now.

Regards,

Pranjali

[Former Member]

Hi

My proposal of get max is(there is no need to write subquery):

 TABLES: cabnt.

 SELECT * FROM cabnt
   UP TO 1 ROWS
   WHERE atinn =  '00000000XX'  AND spras = 'D'
   ORDER BY adzhl DESCENDING.
 ENDSELECT.

Edited by: Daniel Duras on Nov 17, 2010 12:43 PM

[Former Member]

Hello Daniel,

your solution would have been my alternative, if I didn't get it work with the subquery :-). How about performance? Subquery vs. up to n rows?

I can't believe, but I got another solution:

SELECT SINGLE atbez FROM cabnt as c INTO atbez

WHERE atinn = '0000002134'

AND spras = 'D'

AND adzhl =

( SELECT MAX( adzhl ) FROM cabnt WHERE atinn = c~atinn

AND spras = 'D'

GROUP BY atinn spras ).

I just defined an alias ("as c" ) for the outer select and changed the where clause of the subquery to that alias with the result, that the dump doesn't occur anymore.

[Former Member]

Hi Daniel,

I think, that table cabnt won't have many rows on your production server. Check it out.

Furthemore you can make some combinations of this code f.e. remove DB sorting and use ABAP SORT BY statement.

It should be faster I think.

Moreover

SELECT var

is faster than

SELECT *

But like I said before this table is quite small ...

Best regards, Daniel.