topic Re: error in join in Application Development and Automation Discussions

error in join

Former Member — Wed, 03 Jun 2009 07:21:21 GMT

SELECT A_{aufnr B}gstri A_{autyp A}loekz A_{kokrs A}abkrs

FROM AUFK AS A

into table it_aufk

INNER JOIN AFKO AS B

ON A_{erdat = B}gstri

WHERE B~gstri in s_date. "s_date is date from select options

the error is "wrong table name or table alias name "B". the field "B~gstri". Please help.

Re: error in join

ThomasZloch — Wed, 03 Jun 2009 07:23:16 GMT

I think the "into table" is in the wrong place, try moving it before the "from aufk" line.

Thomas

Re: error in join

Former Member — Wed, 03 Jun 2009 07:25:49 GMT

try this

SELECT A~aufnr 
             A~autyp 
             A~loekz 
             A~kokrs 
             A~abkrs
             B~gstri 
into table it_aufk
  FROM AUFK AS A
INNER JOIN AFKO AS B ON A~erdat = B~gstri
WHERE B~gstri in s_date.

Regards

Smitha

Re: error in join

Former Member — Wed, 03 Jun 2009 07:27:08 GMT

Hi,

Chk this example coding,

SELECT a~vbeln
a~fkart
a~fkdat
a~inco1
a~inco2
a~land1
a~netwr
a~kunag
a~waerk
a~vkorg
a~vtweg
a~kunrg
b~kunnr
b~parvw
b~lifnr
b~adrnr
c~vgbel
c~aubel
A~KNUMV
d~addrnumber
d~city1
d~POST_CODE1
d~NAME1
e~kunnr
e~name1
INTO CORRESPONDING FIELDS OF TABLE It_data
FROM vbrk as a
inner join vbrp as c on a~vbeln = c~vbeln
inner join vbpa as b on a~vbeln = b~vbeln and parvw = 'WE'
inner join adrc as d on d~ADDRNUMBER = b~adrnr
inner join kna1 as e on e~kunnr = b~kunnr
WHERE a~vbeln in s_vbeln
 AND a~fkart in s_fkart
 AND a~fkdat in s_fkdat
 AND a~vkorg in s_vkorg
 AND a~vtweg in s_vtweg
 AND a~kunag in s_kunag.

I dont think you have selected the table from where you selecting field grsti..that i think comes from a different table so you have mentioned it as b.

Regards,

Nikhil.

Edited by: Nikhil Kanegaonkar on Jun 3, 2009 9:27 AM

Re: error in join

Former Member — Wed, 03 Jun 2009 07:30:57 GMT

SELECT a~aufnr

a~autyp

a~loekz

a~kokrs

a~abkrs

b~gstri

INTO TABLE IT_AUFK

FROM AUFK AS a INNER JOIN AFKO AS b

ON a_{erdat = b}gstri

WHERE b~gstri in s_date.

Re: error in join

Former Member — Wed, 03 Jun 2009 07:56:00 GMT

Hi,

SELECT A_{aufnr A}autyp A_{loekz A}kokrs A_{abkrs B}gstri

into table it_aufk

FROM AUFK AS A

INNER JOIN AFKO AS B

ON A_{erdat = B}gstri

WHERE B~gstri in s_date. "s_date is date from select options

Try this.

Regards,

Vijay

Re: error in join

Former Member — Wed, 03 Jun 2009 08:08:51 GMT

Hi,

Before using join please keep in mind that performance would be effected from it.

And eventhough you need to use it,

PLEASE SELECT THE DATA FRONM TABLE AUFK & AFKO IN THE ORDER GIVEN IN THE DB TABLE AND TRY TO FETCH ALL THE PRIMARY KEYS OF BOTH THE TABLE USED IN JOINS( In ur case,primary keys from table AUFK & AFKO).

CLEAR IT_AUFK.

SELECT a~aufnr

a~autyp

a~loekz

a~kokrs

a~abkrs

b~gstri

INTO TABLE IT_AUFK

FROM AUFK AS a INNER JOIN AFKO AS b

ON a_{erdat = b}gstri

WHERE b~gstri in s_date.

Please close the thread as answered!!!

Warm Regards,

Abhishek

Re: error in join

Former Member — Wed, 03 Jun 2009 08:21:57 GMT

hi,

use this code::-------

select a_{aufnr b}gstri a_{autyp a}loekz a_{kokrs a}abkrs into corresponding fields of

table it_aufk

from

aufk as a

inner join

afko as b

a_{erdat = b}gstri

where

b~gstri in s_date

group by a_{aufnr b}gstri a_{autyp a}loekz a_{kokrs a}abkrs.

Regards,

Vaneet Thakur