error in select query using join

[former_member130219]

hi all

please help.

The follwing code givs this error....

select skb1-bukrs ska1-ktoks skb1-saknr skb1-waers skb1-xsalh skb1-xopvw skb1-xkres

into corresponding fields of table it_skab1

from ( skb1

OUTER JOIN ska1 on ska1-saknr=skb1-saknr ) .

*Error while executing....*

Program ZABHI_FIREPORT

"(" has no closing ")".

Please guide me for the correct syntax.

Thank u.

[Former Member]

select skb1-bukrs ska1-ktoks skb1-saknr skb1-waers skb1-xsalh skb1-xopvw skb1-xkres

into corresponding fields of table it_skab1

from skb1

OUTER JOIN ska1

on ska1-saknr=skb1-saknr .

write select query without ()

[former_member130219]

Now it gives the following error.

Wrong expression "OUTER" in FROM clause . . .

pls. check

[MarcinPciak]

Use LEFT OUTER JOIN. Also SBK1 table should be on the left hand side in JOIN condition. And not hypens '-' but tilde '~' in selector.

select skb1~bukrs ska1~ktoks skb1~saknr skb1~waers skb1~xsalh skb1~xopvw skb1~xkres
into corresponding fields of table it_skab1
from skb1
LEFT OUTER JOIN ska1
on sbk1~saknr = ska1~saknr .   "sbk1 on the left; here use tilde too; also a separator after '='

Regards

Marcin

[Former Member]

Hi Abhinandan,

Try this code

SELECT s~bukrs p~ktoks p~saknr p~saknr p~waers p~xsalh p~xopvw p~xkres
       INTO CORRESPONDING FIELDS OF TABLE it_skab1 
       FROM skb1 AS s 
       LEFT OUTER JOIN ska1 AS p ON s~saknr   =  p~saknr.

Hope you got your answer.

Regards,

Nimish

[MarcinPciak]

Nimish is right, I forgot about aliases. These are necessary as long as you are addressing same components from different table. In your example field saknr is ambigous, so fields need to be addressed with tilde, not hypen anymore. The solution suggested above by Nimish is correct one.

Regards

Marcin

[ThomasZloch]

Once the syntax errors are fixed, you still have an incomplete join. You must include KTOPL in the link to SKA1, the required connection between BUKRS and KTOPL is inside table T001.

Thomas

[Former Member]

select skb1-bukrs ska1-ktoks skb1-saknr skb1-waers skb1-xsalh skb1-xopvw skb1-xkres

into corresponding fields of table it_skab1

from skb1

left OUTER JOIN ska1 on skb1-saknr=ska1-saknr .

[Former Member]

Hi,

Refer the code if it helps:

data: begin of imatnr occurs 0,

matnr type mara-matnr,

mtart type mara-mtart,

werks type marc-werks,

dispo type marc-dispo,

end of imatnr.

select mara_{matnr mara}mtart marc_{werks marc}dispo

into table imatnr

from mara

left outer join marc

on mara_{matnr = marc}matnr

up to 10 rows.

Regards

Sumana

[Former Member]

Not requiered "(" and ")".

select skb1-bukrs ska1-ktoks skb1-saknr skb1-waers skb1-xsalh skb1-xopvw skb1-xkres

into corresponding fields of table it_skab1

from skb1 OUTER JOIN ska1

on ska1-saknr=skb1-saknr.

For your information Don't use the correcponding fld. it will give the perform issue.

just make sure the order of the fld should be same in the Qury and in the internale table.

Ravi

[Former Member]

hi,

use this code n check

tables: ska1,skb1.

data: begin of it_skab1 occurs 0,

bukrs like skb1-bukrs,

ktoks like ska1-ktoks,

saknr like skb1-saknr,

waers like skb1-waers,

xsalh like skb1-xsalh,

xopvw like skb1-xopvw,

xkres like skb1-xkres,

end of it_skab1.

select skb1~bukrs

ska1~ktoks

skb1~saknr

skb1~waers

skb1~xsalh

skb1~xopvw

skb1~xkres

from skb1 left outer join ska1 on skb1_{saknr = ska1}saknr

into corresponding fields of table it_skab1 up to 10 rows.

loop at it_skab1.

write: / it_skab1-bukrs,it_skab1-ktoks,it_skab1-saknr,it_skab1-waers,it_skab1-xsalh,

it_skab1-xopvw,it_skab1-xkres.

endloop.

n let me knw is ur doubt cleared

rgds

shivraj

Edited by: ShivrajSinha on May 27, 2009 8:27 AM

[Former Member]

Hi Abhinandan,

Please try this code:

tables: ska1, skb1.

data: it_skab1 type standard table of skb1.

select a_{bukrs a}saknr a_{waers a}xsalh a_{xopvw a}xkres

into corresponding fields of table it_skab1

from skb1 as a

Left OUTER JOIN ska1 as b

on b_{saknr = a}saknr.

[Former Member]

Hi Abhinandan,

You are getting the error Program ZABHI_FIREPORT "(" has no closing ")".

because theres no space between ur brackets and the content.

After u open the brackets "(" please put a space, write the fields that u want, then again put a space and then close the bracket ")"

check if u have kept any previous brackets open or without space between the contents and the brackets or u have forgot to put a fullstop after the brackets.

Regards,

RadhikaS

[former_member130219]

Thank you.