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: 

CREATE VIEW in ABAP (Open SQL or Native SQL)

Former Member
0 Kudos

Hi all you experts!

I want to create a VIEW in ABAP. I have created Table Views using ABAP Dictionary (in transaction SE11), I don't have any problem with them.

But, what I need is to create a dynamic view, I mean, a view that can be created/replaced (or modified) at runtime. Is this possible with SAP Open SQL, I don't think so... that is why I tried to created using native SQL but it is not working.

Here is the code:


  EXEC SQL.
    CREATE VIEW [ZMXRFIV_GLPCA]
      AS SELECT
         T1.GL_SIRID,
         T1.POPER,
         T1.RBUKRS,
         T1.RPRCTR,
         T1.RACCT,
         T1.HSL
      FROM
         GLPCA T1
      INNER JOIN
         SKA1 T2
      ON
         T1.RACCT = T2.SAKNR
      WHERE
        T1.RVERS      =  '000'
        AND T1.RYEAR  =  '2008'
        AND T1.KOKRS  =  'PI01'
        AND T2.KTOPL  =  'PI00'
        AND T2.XBILK  <> 'X'.
  ENDEXEC.

I have tried using quotes (") for the view name, parenthesis and even using only the name but this make no difference.

Do any of you experts have any idea?

PS: After creating the view I need to do a SELECT INTO TABLE to that view and finally delete this view and continue working with the data on the internal table.

1 ACCEPTED SOLUTION

christine_evans
Active Contributor
0 Kudos

What error are you getting? You don't need a full stop at the end of a real SQL statement.

Why do you need to use a database view? What can you do with it that you can't do using ABAP internal tables?

Edited by: Christine Evans on Sep 3, 2008 12:27 PM

9 REPLIES 9

christine_evans
Active Contributor
0 Kudos

What error are you getting? You don't need a full stop at the end of a real SQL statement.

Why do you need to use a database view? What can you do with it that you can't do using ABAP internal tables?

Edited by: Christine Evans on Sep 3, 2008 12:27 PM

0 Kudos

Hi Christine!

I have tried using Table Views for that program and it worked pretty good! The problem is that since some values come from a "Constants table" (same field for more than one constant at the same time) I could not find a way to handle this with the standard tool for creating views (SE11), I mean there is no way to use AND/OR conditions for the constants that come from the contants table. Basically that is why I thought in building the viewn dynamically (at runtime) instead of creating a view for each combination of constant we have and refer to them statically (via the vista name).

0 Kudos

Hi,

Is there a reason you can't use a table join within ABAP?

SELECT Field List

INTO TABLE Internal Table

FROM GLPCA as T1

INNER JOIN SKA1 T2

ON

T1RACCT = T2SAKNR

WHERE

T1~RVERS = ...

AND T1~RYEAR = .....

AND T1~KOKRS = ....

AND T2~KTOPL = .....

AND T2~XBILK = 'X'.

If this processing is going to be used in several programs look at setting it up in a Function Module or Class Method to make it more re-usable.

0 Kudos

I still don't see why you need to store the selected data in the database. Why can't you use a join and select into an internal table as described by the post above?

What you're trying to do is going completely against the normal rules of ABAP programming by not using Open SQL and by working with data directly in the database rather than getting it into memory on the application server and working with it there. You would need to have a really good reason to do things this way. The only one that I can think of is that you want the data to stay around in the database after the program has finished - but this doesn't appear to be the case since you are dropping the view at the end of the program.

0 Kudos

Hi again Christine! First of all thank you so much for your interest in this post.

I have to improve the performance of a program that access GLPCA table (GLPCA table has around 18 million records only for 2008). As I mention before I created a Table View in the Dictionary (using SE11), this view has 4 fixed (hardcoded) values including the RYEAR (year). I noticed that the performance of the program was really improved since I created the view. But the problem is having those fixed values. Some of those values come from a constants table that depend on the values the user enter at the selection screen. That is basically why I think creating a view at runtime would be a good idea. Otherwise I will have to create a view for 2009, 2010 an so on...

Tell me something, is there any difference in execution time (I mean performance) between an ABAP Join and a Table View?

0 Kudos

A database view is not really a database object; rather it is a join defined by a SQL statement that has been created and stored in the database when you define the view. At runtime, this bit of SQL is appended to your SQL. This being the case, I shouldn't think that there would be much difference in performance between using a join in the SQL statement in your program or using a database view - though since the database view has been predefined and the database optimiser knows about it, I guess using one would be slightly quicker since there would be less time spent on parsing the statement.

By creating the view at runtime, I think you would lose even this advantage. Is there really such a great difference between using a database view and not? What does explain plan in ST05 say?

Oh, and did you try removing the final full stop from your real SQL statement?

0 Kudos

Hi ,

oh yes it is an object (well, how the database should handle it in any context if it wasn't)

i.e. for ORACLE you would have several thousands of them:

select count(*) from dba_objects where object_type ='VIEW'

If you avoid some kind of foreground processing (i.e. pull the data over the network) and handle the processing inside the database it can improve somehow performance a little (i.e. using the retieved rows of the view to stuff into a database table directly). But his may not always possible...

bye

yk

0 Kudos

When I said that a 'view is not really a database object' I meant that it is not an actual structure in the database that holds data; it is just a bit of SQL containing a join between database tables that is added to your own query at runtime, and the only difference between this and between writing the join yourself is that the view join is stored in the database. Sorry for any confusion. The point I was trying to make is that whether you use a view or use a join, in both cases you are really using a join, and I don't think there would much difference in performance in using either.

0 Kudos

definitley !

agree with you

btw:

could mention materialized views but this would be out of scope here

bye

yk