Problems with Left Outer Join

[Former Member]

Hi guys,

i have a big problem with left outer join and the where condition:

3 tables

table1: IDCUST NAME TEL

table2: IDCUST CURRENCY

table3: ID3 IDCUST PRODUCTNAME

So one Customer can have more than one Product (1 - n)

So my select looks like this:

Select

a~name

a~tel

b~currency

c~productname

from

table1 As a

inner join table2 as b

on a_{idcust = b}idcust

left outer join table3 as c !!!!!

on a_{idcust = c}idcust

Where (condition-string).

Now the Error:

my condition-string is dynamic.

so if i want to have productname="prod1" an all customers with this product i get an error.

but if i want to have all customers where tel number = "123"

i get a result with all customers tel=123 AND their products! (that's correct).

So how can i get a result with using a where condition including table 3 without having an error????

Please help me!!!

[RichHeilman]

Are you trying to put a where condition on your LEFT OUTER JOINed table. If so, this is not allowed. You will have to filter it doing a loop after the select.

Select .....into itab

Loop at itab.
if itab-productname <> "prod1".
delete itab.
continue.
endif.

endloop.

Regards,

Rich Heilman

[Former Member]

Thanks for this answer,

so it isnt possible doing where condition on left outer join? i tested in on access (very stupid i know) but in this case it worked.

So i thought that it may work in OpenSQL also.

Regards,

Marcus

[Former Member]

Hi,

You can put your filter after <b>ON</b>:

Example:

data lang type sy-langu.

lang = 'E'.

select a_{SAKNR b}TXT50 into corresponding fields of table test

from SKA1 as a left join SKAT as b

on a_{SAKNR = b}SAKNR and <b>b~SPRAS = lang</b>

where a~KTOPL = 'BULG'.

Svetlin

[RichHeilman]

Good call, never knew that you could do that.

Regards,

Rich Heilman

[Former Member]

But how can i do a dynamic "on condition" ?

vardynamic = 'AND c~prod = ''test'' '.

... on a_{idcust = c}idcust (vardynamic) ?

[RichHeilman]

I think the only way to do that is to make the entire statement dynamic.

Regards,

Rich Heilman

[RichHeilman]

Here is some sample code to show you how to generate a subroutine at runtime with dynamic code.

report zrich_0001
       no standard page heading.

types: t_source(72).

data: routine(32) value 'TEMP_ROUTINE',
      program(8),
      message(128),
      line type i.

data: isource type table of t_source,
            xsource type t_source.

data: it001 type table of t001.
data: xt001 type t001.

start-of-selection.

* Here is the source code for the temporary subroutine
* with you select statement
  xsource = 'REPORT ZTEMP_REPORT.'.
  insert xsource  into isource index 1.
  xsource = 'FORM & tables itab.'.
  replace '&' with routine into xsource.
  insert xsource  into isource index 2.

  xsource = 'select * into corresponding fields of table itab'.
  append xsource to isource.
  xsource = 'from t001'.
  append xsource to isource.
  xsource = 'where bukrs <> space.'.
  append xsource to isource.

  xsource = 'ENDFORM.'.
  append xsource to isource.

* Generate the subroutine
  generate subroutine pool isource name program
                           message message
                           line line.
* Call it and get your internal back with data.
  if sy-subrc = 0.
    perform (routine) in program (program) tables it001.
    loop at it001 into xt001.
      write:/ xt001-bukrs, xt001-BUTXT.
    endloop.
  else.
    write:/ message.
  endif.

Regards,

Rich Heilman

[Former Member]

I did not know that i could do subroutine in an BSP event handler!

Is this right?

Regards Marcus

[Former Member]

Hi,

i tested ur solution but it didnt work as i imagine:

select a_{SAKNR b}TXT50 into corresponding fields of table test

from SKA1 as a left outer join SKAT as b

on a_{SAKNR = b}SAKNR and b~SPRAS = lang

where a~KTOPL = 'BULG'.

I got only results where a~KTOPL = 'BULG' !

The condition of b~SPRAS = lang did not work.

Regards,

Marcus

[jayanthi_jayaraman]

Hi,

Try this.

select a_{SAKNR b}TXT50 into corresponding fields of table test

from SKA1 as a left outer join SKAT as b

on a_{SAKNR = b}SAKNR

where a~KTOPL = 'BULG'

<b>and b~SPRAS = lang</b>.

[Former Member]

Hi,

look at my first posting!

This is the problem, that the where condition used within left outer join is not allowed!

Regards,

Marcus

[jayanthi_jayaraman]

Hi,

types : begin of ty,

saknr type ska1-saknr,

txt50 type skat-txt50,

spras type skat-spras,

end of ty.

data : test type standard table of ty,

wa type ty.

select a_{SAKNR b}TXT50 b~spras into corresponding fields of table test

from ( SKA1 as a left outer join SKAT as b

on a_{SAKNR = b}SAKNR )

where a~KTOPL = 'BULG'.

Loop at test into wa.

if wa-spras ne lang.

delete itab index sy-tabix.

endif.

endloop.

[Former Member]

Did you try the 'Having' clause of the select statement ?

[Former Member]

Sorry,

didn't know what you mean.

[Former Member]

Marcus, hi

You are not going to be able to achieve your requirement the way you want to. Left outer joined tables cannot have clause(s) in the WHERE condition and dynamic clauses are only allowed in the WHERE condition. The two are mutually exclusive.

The following links to the official help back this up,

from the first link http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm scroll down to 'Specifying Two or More Database tables as a Left Outer Join' and it states 'The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.' (Where <dbtab> is the left outer joined table.)

From the second link http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm scroll down to 'Dynamic Conditions' and it states 'You may only use dynamic conditions in the WHERE clause of the SELECT statement.'

I realise this hasn't helped resolve your problem but it does clearly indicate you need to use an alternative technique like dynamically generated subroutine pools, which Rich Heilman outlined above, or further processing the data in a loop once you've retrieved it, as outlined by Jayanthi Jayarman.

Although you can use dynamic clauses in the HAVING addition, you face the same restriction as with the WHERE clause in that you cannot reference the left outer joined table.

I hope this helps and allows you to proceed to come at the problem from a different perspective.

Kind regards

Mark