Create a view from 10 tables

[Former Member]

Hi Experts,

I want to create a view from 10 pricing tables rather than write 10 different select query in my application. Most of the columns of the pricing tables are same. I want all the data from these tables into the respective columns. Further I want an indicator as to which table does this record belong to. How can I achieve this?

Ex:

Table 1 columns: kappl kschl vkorg vtweg vkgrp

Table 2 columns: kappl kschl vkorg vtweg matkl matnr

if each table has two records, I want 4 records in my view in the respective columns along with the indicator that this record belongs to a specific column.

Regards

[Former Member]

With a view, you usually do a JOIN. However, what you're describing is a UNION ALL.

[Former Member]

yes, I need a union all. Is it possible in SAP DDIC?

[MarcinPciak]

Hi,

Placing 10 tables in one view will drastically affect performance. This is highly unrecommended.

Anyhow if you insist on doing so you can create [database view|http://help.sap.com/saphelp_nw04/helpdata/en/36/74c0358373003ee10000009b38f839/frameset.htm] or use explicitly joins in your program.

Keep in mind that althought it is possible to use projection (only selection of choosen column) it is not logically appropriate to locate which table this column belongs to (as far as we are talking about key fields) as both have the same field as key field.

On the other hand when using projection for non-key columns it is you who define what is to be selected and how, therefore you know which column belongs to which table.

i.e

data: begin of st,
          lgart type t512w-lgart,
          molga type t512w-molga,
          lgtxt   type t512t-lgtxt,
        end of st.

select w~molga w~lgart t~lgtxt   "<- here using aliases you know which column is taken from which table
  into st
  from T512W as W
  inner join T512T as T on
     W~LGART = T~LGART.

  write: / st-lgart, st-molga, st-lgtxt.
 
endselect.

In this example lgart (key field) can be taken from either t512w or t512t, but fields mogla and lgtxt are taken from t512w and t512t respectively.

All in all this is correct as far as up to 3-4 tables are concerned. But 10 will be a disaster...

Hope this helps you

Marcin

[Former Member]

>

> Placing 10 tables in one view will drastically affect performance. This is highly unrecommended. 
> All in all this is correct as far as up to 3-4 tables are concerned. But 10 will be a disaster...

But then why does SAP have standard views with more than 10 tables?

Rob

[Former Member]

we are deviating from the question asked in the thread. I would like to have a union all of the 10 tables. Can anyone please suggest how do I go about it.

[Former Member]

The forum is about the exchange of ideas. Deviance is a good thing.

Rob

[Former Member]

No, I don't think it is possible to do anything but inner joins via a view. Your best option may be to load each table appending into an itab, and then search the itab.

[Former Member]

Why don't you just do something like this? Of course you'll want to adjust the name of your table each time.

I hope this gets us back on track after Rob's attempted deviation

data:
  g_t_tstc  type TABLE OF tstc.

select tcode into CORRESPONDING FIELDS OF TABLE g_t_tstc  from tstc.
select tcode APPENDING CORRESPONDING FIELDS OF TABLE g_t_tstc  from tstc.
select tcode APPENDING CORRESPONDING FIELDS OF TABLE g_t_tstc  from tstc.
select tcode APPENDING CORRESPONDING FIELDS OF TABLE g_t_tstc  from tstc.
select tcode APPENDING CORRESPONDING FIELDS OF TABLE g_t_tstc  from tstc.

[Former Member]

I am doing something similar. But deviating a bit, I have to know which condition table it is also. And I have it as a column.

So I loop at the table and then assign the table number(say 633) and finally use move corresponding to the main table and append. This affects performance.

[Former Member]

used native sql here.