How to use 'for all entries' and 'into corresponding' and 'up to 1 row' together in SQL

[former_member625844]

I have an internal table A with fields name and price and DB table B with fields name,price,date. Now itab A already has name, and I want to for each name in A, query latest price in B and filled into corresponding field of A.

So I wrote

select price from B for all entries in A  where name = A-name order by date descending into corresponding field of table A up to 1 rows.

But it not work, The error said date is not a valid name. But I'm sure B has this date field. So what's the correct way to write it?Thx.

[tom_wan]

Hello Luo

Seems order by can only be used in a special way with FOR ALL ENTRIES:

• If the addition FOR ALL ENTRIES is used in the WHERE condition,, ORDER BY can only be used with the addition PRIMARY KEY and all columns of the primary key (except the client column of client-specific tables) must be specified after SELECT list.

  

And if you use up to 1 rows, it will only get 1 record, are you sure this expected?

I think you can just get all entries from DB, then LOOP the internal table to do what you want.

[former_member625844]

Yes I only need one row. Because for each name in Table A, there are many prices in table B, and I only want the latest price.

[former_member1716]

loki_luo15,

As suggested above by tom.wan you wont be able to use the Order by Statement unless you include all the Primary key fields from the table. However you could follow below alternative approach as well.

Approach 1:

1) Fetch the entries from Data base Table B using FOR ALL ENTRIES alone, don't Use any Order By Keyword.

2) Now sort the result table using Name (Ascending) and Date (Descending).

3) Now apply DELETE ADJACENT DUPLICATES to the internal table by comparing NAME field.

You will now be ending with what you wanted.

Approach 2 (Tedious):

You may fetch the entries from Table B and the using Loop Condition you have to find the right value in every iteration of the Loop.

Regards!