topic Re: joininig SQL statement in Application Development and Automation Discussions

joininig SQL statement

Former Member — Tue, 17 Jan 2006 03:01:45 GMT

Hi all,

I want to join 2 db tables (A, B), and select a minimum column (A~field1) from db table A, what should be the sql statement? Thanks.

Select (....)

into corresponding fields of itab

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

where (....).

append itab.

Endselect

Re: joininig SQL statement

suresh_datti — Tue, 17 Jan 2006 03:05:01 GMT

Hi Macy,

try this..

Select (....)

into corresponding fields of table itab

from ekko as A

inner join ekpo as B

on A_{ebeln = B}ebeln

where (....).

Regards,

Suresh Datti

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 03:05:20 GMT

Select (....)
into corresponding fields of itab
from ekko as A
join ekpo as B
on A~ebeln = B~ebeln
where A~field1 = ( SELECT min(field1) from A ).

append itab. 
Endselect

Hope it works...

Re: joininig SQL statement

former_member186741 — Tue, 17 Jan 2006 03:05:37 GMT

Your prototype select is pretty much ok, you need the MIN aggregate function:

Select MIN (....) AS ??? "??? = SAME NAME AS COLUMN IN ITAB

into corresponding fields of TABLE itab

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

where (....).

Message was edited by: Neil Woodruff

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 03:17:58 GMT

This works ...

TABLES : ekko.

DATA : begin of i_data occurs 0,
          ebeln LIKE ekko-ebeln,
       end of i_data.

select ekko~ebeln
  into table i_data
  from ekko join ekpo on ekko~ebeln = ekpo~ebeln
where ekko~field1 IN ( SELECT min( field1 ) from ekko ).

Is this what you want?

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 03:21:14 GMT

Sorry,

I should have said that I have 3 db tables (A, B, C) to join.

And I'd like to get the min fieldX value from C.

What should be the SQL statement?

Select A_{field1 B}field2

min( C~fieldX)

into corresponding fields of TABLE itab

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebelp

where (....).

append itab.

Endselect.

Re: joininig SQL statement

former_member186741 — Tue, 17 Jan 2006 03:23:54 GMT

Select A_{field1 B}field2

min( C~fieldX) as fieldX

into corresponding fields of TABLE itab

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebelp

where (....).

no append or endselect required

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 03:30:08 GMT

Select ekko~field1 ekpo~field2 
min( eket~field3 )
into corresponding fields of TABLE itab
from ekko
join ekpo
on ekko~ebeln = ekpo~ebeln
join eket
on ekpo~ebeln = eket~ebeln and ekpo~ebelp = eket~ebelp 
group by ekko~field1 ekpo~field2

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 03:38:26 GMT

Thanks.

I've followed the code.

But there is syntax error:

The field "B_{MEINS" from the SELECT list is missing in the GROUP BY clause. addition INTO wa or INTO (g1,...gn) is required. fields of type "" or "A}MANDT".

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 03:43:34 GMT


DATA : begin of i_data occurs 0,
          field1 LIKE ekko-field1,
          field2 LIKE ekpo-field2,
          menge LIKE eket-menge,
       end of i_data.

Select ekko~field1 ekpo~field2
min( eket~menge )
into  TABLE i_data
from ekko
join ekpo
on ekko~ebeln = ekpo~ebeln
join eket
on ekpo~ebeln = eket~ebeln and ekpo~ebelp = eket~ebelp
group by ekko~field1 ekpo~field2

Check whether it works and please let us know.

Re: joininig SQL statement

former_member186741 — Tue, 17 Jan 2006 03:48:29 GMT

try again, for example:

Select A_{ebeln B}ebelp c~etenr

min( C~eindt ) as eindt

into corresponding fields of table tt

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebel

group by a_{ebeln b}ebelp c~etenr

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 03:49:34 GMT

Then where could I put the "where clause" ?

Re: joininig SQL statement

former_member186741 — Tue, 17 Jan 2006 03:50:51 GMT

Select A_{ebeln B}ebelp c~etenr

min( C~eindt ) as eindt

into corresponding fields of table tt

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebel

where .......

group by a_{ebeln b}ebelp c~etenr

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 04:08:11 GMT

So many thanks to all of you.

It works. But the result in the itab is unexpected.

I want to retrieve the entry with minimum eindt into table itab, but all the entry (not only min.) are retrieved into itab.

Re: joininig SQL statement

former_member186741 — Tue, 17 Jan 2006 04:10:40 GMT

Hi Macy,

this sql gives you the minimun eindt for each ETENR not the MIN for the whole set. What is it that you wanted?

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 04:16:46 GMT

Hi Neil,

I want to get 1 entry with its minimum delivery date MIN( EINDT ) and other info for each PO item

Re: joininig SQL statement

Former Member — Tue, 17 Jan 2006 04:19:44 GMT

If you want the minimum value of menge for ebeln

Then use...

Select ekko~ebeln
min( eket~menge )
into corresponding fields of TABLE i_data
from ekko
join ekpo
on ekko~ebeln = ekpo~ebeln
join eket
on ekpo~ebeln = eket~ebeln and ekpo~ebelp = eket~ebelp
where ....
group by ekko~ebeln.

Re: joininig SQL statement

former_member186741 — Tue, 17 Jan 2006 04:24:33 GMT

then try,

Select A_{ebeln B}ebelp c~etenr

C~eindt as eindt

into corresponding fields of table tt

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebelp

where eindt IN ( SELECT min( eindt ) from eket )

But, this gives you the lowest EINDT in the whole EKET table which is not necessarily what you want. I think you want the MIN (EINDT) of the ones you have selected but I'm not sure how to achieve that.

Message was edited by: Neil Woodruff