Database View where I join VBAK to itself?

[Former Member]

All,

At my company we have a concept of master and lower level contracts which are both stored in the VBAK table. I'd like to create a database view which would do the equivalant of the following valid select statement

select master_{vbeln child}vbeln into table git_data

from vbak as master inner join vbak as child on master_{vbeln = child}vbeln_grp

where master~auart = 'ZMTR'.

The above syntax will give me a list of all our master contracts with all it's associated lower level contracts (1 to many relationship).

When I list VBAK twice on the table/join coditions tab of the view definition in SE11 I get an error stating "join fields vbak-vbeln and vbak-vbeln_grp come from the same table". Essentially SAP thinks I'm trying to join the same table rather than 2 seperate occurrences of the same table. I've tried various flavors of incorporating what would essentially be equivalant to the "as master/as child" syntax to no avail.

I wasn't sure if anyone here knew of a way to make this work.

[Former Member]

All,

At my company we have a concept of master and lower level contracts which are both stored in the VBAK table. I'd like to create a database view which would do the equivalant of the following valid select statement

select master_{vbeln child}vbeln into table git_data

from vbak as master inner join vbak as child on master_{vbeln = child}vbeln_grp

where master~auart = 'ZMTR'.

The above syntax will give me a list of all our master contracts with all it's associated lower level contracts (1 to many relationship).

When I list VBAK twice on the table/join coditions tab of the view definition in SE11 I get an error stating "join fields vbak-vbeln and vbak-vbeln_grp come from the same table". Essentially SAP thinks I'm trying to join the same table rather than 2 seperate occurrences of the same table. I've tried various flavors of incorporating what would essentially be equivalant to the "as master/as child" syntax to no avail.

I wasn't sure if anyone here knew of a way to make this work.

[Former Member]

Hello,

Try creating two views and using one of them in the view with join.

For example an view for the clause where and other to the inner join of the table and the created view.

Regards.

[Former Member]

>

> Hello,
> 
> Try creating two views and using one of them in the view with join.
> 
> For example an view for the clause where and other to the inner join of the table and the created view.

David,

Thanks for your response. I think I tried what you suggested by doing the following.

I created 1 view which basically consisted of my VBAK table with the where condition being just the master contracts. So this should have created a view of the VBAK table that just consisted of master contracts.

I then tried to create a second view. In this view I was going to join my first view with the VBAK table. The intention here being that I would join my master contracts view with the VBAK table where the master contract view vbeln = vbak-vbeln_grp. Unfortunately when I try this I get the error message that the tables listed in the tables section of the view definition have to in fact be transparent tables (can't be views).

Thanks for the idea though. It was worth a shot.

[Former Member]

Maybe creating the view on VBAP and VBAK would work.

Rob

[Former Member]

>

> Maybe creating the view on VBAP and VBAK would work.
> 
> Rob

Rob,

Not sure you understand the query as I don't know how linking to VBAP would help. Appreciate you taking the time to try and help though.

[Former Member]

I meant that since you cannot join VBAK to either itself or a view and since you don't need anything more than VBELN which is present in bothe VBAP and VBAK, this might work.

Rob

[Former Member]

>

> I meant that since you cannot join VBAK to either itself or a view and since you don't need anything more than VBELN which is present in bothe VBAP and VBAK, this might work.
> 
> Rob

Hi Rob,

...and thanks again for your input. I think your missing the point to the relationship I'm building in the join condition however. It's not just vbeln I'm after it's the master contract vbeln WITH it's associated lower level contract vbeln. This is why I have VBAK linking to VBAK where the vbeln of the first occurrence of table VBAK equals the vbeln_grp field of the second occurrence of table VBAK.

[Former Member]

Well, I think I have a pretty good idea of what you are trying to do, but maybe I didn't explain it well enough.

From your experience and the posts here, it seems unlikely that you can do what you want the way you want to do it (a view joining VBAK to itself). So you need some sort of a workaround that accomplishes the same thing.

I'm suggesting that you use VBAP to get the sales docuement and the join to VBAK should get the lower levels. Coding for a JOIN rather than a VIEW would be something like:

TABLES: vbak, vbap.

DATA: BEGIN OF git_data OCCURS 0,
        vbeln1 TYPE vbak-vbeln,
        vbeln2 TYPE vbak-vbeln,
      END   OF git_data.

SELECT master~vbeln child~vbeln
  INTO TABLE git_data
  FROM vbap AS master INNER JOIN vbak AS child ON
    master~vbeln = child~vbeln_grp
  WHERE child~auart = 'ZMTR'.

Rob

[ThomasZloch]

Sounds like a brilliant idea (if it works, can't test it here...), I would just replace VBAP with VBUK to avoid the duplicates due to the multiple VBAP entries per VBAK.

There might also be away to solve this problem using Native SQL in an ABAP program.

Shaun, please tell us once you made it work.

Greetings

Thomas

[Former Member]

Thomas - both VBUK and VBAP have a many to one relationship to VBAK. Native SQL might work, but that's not the problem. He can already JOIN VBAK to itself in ABAP. He wants to create a view.

Hmmm...

After thinking about this, saying that there is a many to one relationship between VBAK and VBUK is obviously incorrect - they have the same keys; however, in our system, there are entries in VBUK that are not in VBAK. So I would still try VBAP.

Rob

Edited by: Rob Burbank on Jun 6, 2008 9:39 AM

[ThomasZloch]

Reg. VBUK key, I was about to mention the same...and it is also possible to save a contract header without any lines, giving you a VBAK without any VBAPs...whether that makes sense or not is a different story. Nitpicker, am I not? I just hope Shaun tells us how he solved it in the end.

Cheers

Thomas

P.S. glad to see you back in action around here

[Former Member]

Are the Lower Level/Child Documents created "With Reference" to the Master Documents? If that is the case, you can use VBFA, Document Flow, to get your Master/Child Relationships.

[Former Member]

>

> Are the Lower Level/Child Documents created "With Reference" to the Master Documents?  If that is the case, you can use VBFA, Document Flow, to get your Master/Child Relationships.

Hi Mike,

Technically I could use VBFA but it doesn't help all that much practically in this situation. I was hoping there was a way to use the view builder to use a table more than once. If you look at the CREATE Statement in se11 when you're defining a view (found under menu path EXTRAS->CREATE Statement) you can see that behind the scenes SAP is actually aliasing the tables (T1, T2, T3, etc...). I was hoping there was a way I could manually define the aliasing which would allow me to link a table to itself.

[Former Member]

Hi,

Use two different queries.

use the records obtained from first as input to second query.

Regards,

Amit R.

[Former Member]

All,

Thanks for all your input. With the way SAP interprets what you build in the view builder to the actual view definition ("Create View" SQL) at the database level there was just no way for me to do what I was trying to do. SAP auto-defines the aliases and it doesn't recognize that it needs to create 2 separate aliases when the table occurs twice in the "from/join" clause.