Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Problems with Left Outer Join

Former Member
0 Kudos
981

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 aidcust = bidcust

left outer join table3 as c !!!!!

on aidcust = cidcust

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!!!

15 REPLIES 15

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos
236

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

0 Kudos
236

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
0 Kudos
236

Hi,

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

Example:

data lang type sy-langu.

lang = 'E'.

select aSAKNR bTXT50 into corresponding fields of table test

from SKA1 as a left join SKAT as b

on aSAKNR = bSAKNR and <b>b~SPRAS = lang</b>

where a~KTOPL = 'BULG'.

Svetlin

0 Kudos
236

Good call, never knew that you could do that.

Regards,

Rich Heilman

0 Kudos
236

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

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

... on aidcust = cidcust (vardynamic) ?

0 Kudos
236

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

Regards,

Rich Heilman

0 Kudos
236

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

0 Kudos
236

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

Is this right?

Regards Marcus

0 Kudos
236

Hi,

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

select aSAKNR bTXT50 into corresponding fields of table test

from SKA1 as a left outer join SKAT as b

on aSAKNR = bSAKNR 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

0 Kudos
236

Hi,

Try this.

select aSAKNR bTXT50 into corresponding fields of table test

from SKA1 as a left outer join SKAT as b

on aSAKNR = bSAKNR

where a~KTOPL = 'BULG'

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

0 Kudos
236

Hi,

look at my first posting!

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

Regards,

Marcus

0 Kudos
236

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 aSAKNR bTXT50 b~spras into corresponding fields of table test

from ( SKA1 as a left outer join SKAT as b

on aSAKNR = bSAKNR )

where a~KTOPL = 'BULG'.

Loop at test into wa.

if wa-spras ne lang.

delete itab index sy-tabix.

endif.

endloop.

Former Member
0 Kudos
236

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

0 Kudos
236

Sorry,

didn't know what you mean.

Former Member
0 Kudos
236

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