topic Re: Problem in select statement in Application Development and Automation Discussions

Problem in select statement

Former Member — Thu, 14 Jun 2007 06:29:30 GMT

Hi,

i am getting error in the following select st

select partner2

vkont

gpart

dfkkop~opbel

xblnr

budat

betrw

sctax

betrh

bukrs

pymet

opupk

waers

mwskz

c~erdat

spzah

opupw

opupz

from dfkkop

inner join but050

on dfkkop_{gpart = but050}partner1

left outer join zonebillfailure as c on

dfkkop_{opbel = c}opbel

into table iDfkkop

where partner2 in rPayers

and blart <> 'ZZ'

and reltyp = 'Z1BILL'

and xblnr in rConsInv

and augst = space

and hvorg in rhvorg

and c~erdat eq ' '.

How to resolve this probelm.

Thanks

venkat

Re: Problem in select statement

seshatalpasai_madala — Thu, 14 Jun 2007 06:32:29 GMT

Hi,

What is the error that you are getting?

Regards,

Sesh

Re: Problem in select statement

Former Member — Thu, 14 Jun 2007 06:33:25 GMT

Hi,

When you select the fields using join you have to refer which table with ~..

Ex..

SELECT MARA_{MATNR MARC}WERKS

INTO TABLE T_MATERIAL

FROM MARA INNER JOIN MARC

ON MARA_{MATNR = MARC}MATNR

WHERE MATNR IN SO_MATNR

Thanks

Naren

Re: Problem in select statement

Former Member — Thu, 14 Jun 2007 06:41:55 GMT

I am getting the below error

"No fields from the right hand table of a LEFT_OUTER JOIN may appear in where condition ."ERDAT"

actually after the select statement he follng statemt is coming

'delete idfkkop where not erdat is initial. '

To avoid this delete statement I have added erdat field in where condition

Thanks

Venkat

Re: Problem in select statement

Former Member — Thu, 14 Jun 2007 06:43:05 GMT

hi,

we should write inner join select statement like this,

ex:

select kna1~kunnr

kna1~name1

kna1~ort01

ekk0~ebeln

into table itab

from kna1 inner join ekko

on kna1_{kunnr = ekko}kunnr

where kunnr in s_kunnr.

try in such a manner then u cant get any syntax errors.

regards,

seshu.

Re: Problem in select statement

Former Member — Thu, 14 Jun 2007 06:43:54 GMT

Hi,

Yes..if you are using LEFT OUTER JOIN then you cannot have that table field in the WHERE clause..

After the select..as you have mentioned...use the DELETE statement to remove the records

Thanks

Naren

Re: Problem in select statement

seshatalpasai_madala — Thu, 14 Jun 2007 06:47:15 GMT

Hi,

When ever you are doing an OUTER JOIN you cannot use fields from the right hand side table in the WHERE CLAUSE.

What you can do is include this field in the JOIN ON statement and then use the left handle table's field in the WHERE CLAUSE. But I am not sure if you have the same field in the left hand table. if you have it then add that to JOIN ON and add the to the WHERE CALUSE the left hand table's entry as lef~erdat <> ' '.

ELSE if the field does not exist in the left hand side table.

What you can do is remove this from SELECT statement.

And add the DELETE statement.

Regards,

Sesh

Re: Problem in select statement

Former Member — Thu, 14 Jun 2007 06:47:29 GMT

yes . we know that. it is giving performance problem.

How to fine tune this select statement to improve the performance?

thanks

venkat

Re: Problem in select statement

Former Member — Thu, 14 Jun 2007 07:40:09 GMT

Hi,

first solution dont give c~fldname in where clause. bcoz it is from left outer joined table.

secodly to improve performmacne.. divide above select stmt into 2 select stmt and in first dont use left outer join and instead select into tab and use FOR ALL ENTRIES...

Jogdand M B