topic Re: error in select query using join in Application Development and Automation Discussions

error in select query using join

former_member130219 — Thu, 21 May 2009 06:56:51 GMT

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.

Re: error in select query using join

Former Member — Thu, 21 May 2009 07:00:41 GMT

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 ()

Re: error in select query using join

former_member130219 — Thu, 21 May 2009 07:07:32 GMT

Now it gives the following error.

Wrong expression "OUTER" in FROM clause . . .

pls. check

Re: error in select query using join

MarcinPciak — Thu, 21 May 2009 07:14:03 GMT

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

Re: error in select query using join

Former Member — Thu, 21 May 2009 07:19:28 GMT

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

Re: error in select query using join

ThomasZloch — Thu, 21 May 2009 07:24:50 GMT

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

Re: error in select query using join

MarcinPciak — Thu, 21 May 2009 07:26:03 GMT

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

Re: error in select query using join

Former Member — Thu, 21 May 2009 07:27:37 GMT

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 .

Re: error in select query using join

Former Member — Thu, 21 May 2009 12:23:29 GMT

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

Re: error in select query using join

Former Member — Thu, 21 May 2009 19:32:31 GMT

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

Re: error in select query using join

Former Member — Wed, 27 May 2009 06:26:38 GMT

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

Re: error in select query using join

Former Member — Thu, 28 May 2009 10:27:22 GMT

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.

Re: error in select query using join

Former Member — Sun, 31 May 2009 13:16:59 GMT

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

Re: error in select query using join

former_member130219 — Thu, 04 Jun 2009 12:07:20 GMT

Thank you.