topic Re: Left Out join..... in Application Development and Automation Discussions

Left Out join.....

Former Member — Thu, 03 May 2007 05:24:11 GMT

hi alll

i have written left out join coding

zaset_details has 50 records.

zasset_flag has 5 records -

Primary key assetno

SELECT A~ASSETNO INTO ASSETNO-ASSETNO FROM ZASSET_DETAILS AS A LEFT OUTER JOIN ZASSET_FLAG AS B

ON A_{ASSETNO EQ B}ASSETNO

WHERE A_{ASSETYPE = 'Laptop' and B}ASSETFLG eq '0'.

write 😕 assetno-assetno.

endselect.

while running that coding, following error occured.

"No fields from the right-hand table of a LEFT OUTER JOIN may appear in

the WHERE condition: "B~ASSETFLG"."

anyone help me

Re: Left Out join.....

Former Member — Thu, 03 May 2007 05:27:41 GMT

Hi,

Donot give b~assetflg condition in where clause instead select it into ITAB and then filter it.

Jogdand M B

Re: Left Out join.....

Former Member — Thu, 03 May 2007 05:28:50 GMT

Going by the error you cannot use B~ASSETFLG eq '0'. in the where condition

Try with inner join

Re: Left Out join.....

Former Member — Thu, 03 May 2007 05:29:59 GMT

Hi,

Just use Inner Join as below.

SELECT A~ASSETNO INTO ASSETNO-ASSETNO FROM ZASSET_DETAILS AS A <b>INNER JOIN</b> ZASSET_FLAG AS B

ON A_{ASSETNO EQ B}ASSETNO

WHERE A_{ASSETYPE = 'Laptop' and B}ASSETFLG eq '0'.

Regards,

Ram

Re: Left Out join.....

former_member632991 — Thu, 03 May 2007 05:30:00 GMT

Hi,

In the left outer join , u can not put condition on right hand table

SELECT A~ASSETNO INTO ASSETNO-ASSETNO FROM ZASSET_DETAILS AS A LEFT OUTER JOIN ZASSET_FLAG AS B

ON A_{ASSETNO EQ B}ASSETNO

WHERE A_{ASSETYPE = 'Laptop' and <b>B}ASSETFLG eq '0'.</b>.

Regards,

Soniks

Re: Left Out join.....

Former Member — Thu, 03 May 2007 05:32:47 GMT

Hi Muthu,

We cannot give the field for the right hand table in the condition. Because.

- left outer join will get everything which is on the left.

- where condition should be on left and table and on condition on right hand table.

- on left outer join... if you have no entries in the right hand table matching to the left hand table then it joins the table but gives a null entry correpsong to d missg value...

So, that is the reason you are getting that error... please read help on joins and will will get a pictorial solution to you problem.

Regards,

Jayant

Please award if helpful