union in a select

[Former Member]

Hi,

can anybody tell me whether the following sql stardard is possible in abap and how I can emulate the same?

	select * from sapsr3.mast@st1_desarrollos
	WHERE mandt = 300
	AND werks = '1100'
	AND stlan = '1'
	AND stlal = '01'
	union
	select * from sapsr3.mast@st1_desarrollos
	WHERE mandt = 300
	AND werks = '1100'
	AND stlan = '1'
	AND stlal = '02'
	AND trim(matnr) not in
	(select distinct trim(matnr) from sapsr3.mast@st1_desarrollos
	where mandt = 300
	AND werks = '1100'
	AND stlan = '1'
	AND stlal = '01')

Thanks in advance.

Regards.

[ThomasZloch]

As union select is not available in open SQL (as much as I know), I'd try with two selects, the first one with INTO TABLE and second one with APPENDING TABLE option. The subquery for the second select is also possible in open SQL (but not the trim function)

Thomas

[former_member196098]

Hi David

There is no union in abap select

what you can do is to split the query

1- you can do 2 queries

e.g using for all entries .... statement

Then Loop for all entries...

and append it to the internal table

Please search you will find lots of examples...

[Former Member]

Could you, please, give any link where would explained this issue?

Thanks.

[former_member184569]

You can join two select statements with a UNION clause in ABAP.

Check this documentation.

http://help.sap.com/saphelp_nw04/helpdata/en/13/aaca1b3ac34e3b944f94730947c7f4/content.htm

http://careerabap.blogspot.ae/2011/08/sql-union-query.html

[ThomasZloch]

Neither of these links is related to ABAP Open SQL, the first one is for Java environments, the second says "ABAP" but the actual code shown there can only work as Native SQL surrounded by EXEC SQL / ENDEXEC.

Thomas

[Former Member]

It is not necessary to work with Native SQL, what I want is to simulate the previous sql expression in abap if it is possible, of course.

If it is impossible so I could use the sql surrounded by EXEC SQL and ENDEXEC...

[VXLozano]

Thomas' answer is the right one for your subject question: if you must use an UNION to retrieve data and add it to an internal table, you should proceed with TWO select's using INTO TABLE and APPENDING TABLE clauses.

If you want to know if your whole SQL is possible in ABAP, I fear it's not. The use of some functions in the SELECT or WHERE clauses are restricted. You shall find a way to simulate them with some sort of post-processing (I mean: retrieve the full data with the INTO/APPENDING and then to use a LOOP to modify your data (trim = CONDENSE) and/or delete it.

[Former Member]

Check This

SELECT empid AS employee_id, sal AS salary FROM employees

UNION

SELECT empid AS employee_id, sal AS salary FROM more_employees

ORDER BY employee_id

[Former Member]

I haven't ckecked that but I think the union reserved word does not exist in abap,

I'm sorry if I'm wrong...

[former_member184569]

In the code below,   s_matnr-sign = 'I'.

(Not able to edit it)

[former_member184569]

If you want to get the output using multiple queries, you can apply the following logic.

Tables sapsr3.

data s_matnr type range of matnr WITH HEADER LINE.
data : begin of i_matnr OCCURS 0,
       matnr type matnr,
     end of i_matnr.
data i_tab type table of sapsr3.

select DISTINCT matnr  into CORRESPONDING FIELDS OF TABLE i_matnr  from sapsr3

CLIENT SPECIFIED where mandt = '300' AND werks = '1100' AND stlan = '1' AND stlal = '01'.

  loop at i_matnr.
    s_matnr-sign = 'E'.
    s_matnr-option = 'EQ'.
    s_matnr-low = i_matnr-matnr.
    append s_matnr.
endloop.

select * from sapsr3 CLIENT SPECIFIED into table i_tab

            WHERE mandt = '300' AND werks = '1100'  AND stlan = '1' AND stlal = '02'

delete i_tab where matnr in s_matnr.

select * from sapsr3 CLIENT SPECIFIED appending table i_tab

          where mandt = '300' AND werks = '1100' AND stlan = '1' AND stlal = '01'.

I do not think trim function would be required as matnr will always be in the internal format.

Let me know if its working fine.

[Former Member]

I have used the following code and, it seems that it works fine, at least with my data:

  select matnr andat aedat

    into (formu_aux-material, formu_aux-feccrea, formu_aux-fecmod)

    from mast client specified

    where mandt = sy-mandt

    and   werks in pcentro

    and   ( andat in pfbusca

    or    aedat in pfbusca )

    and   stlan eq '1'

    and   stlal eq '02'

    and   matnr not in

     ( select matnr

        from mast client specified

          where mandt = sy-mandt

          AND werks eq '1100'

          AND stlan eq '1'

          AND stlal eq '01'

          ).

    append formu_aux.

    clear formu_aux-material.

    clear formu_aux-feccrea.

    clear formu_aux-fecmod.

  endselect.

  select matnr andat aedat

    into (formu_aux-material, formu_aux-feccrea, formu_aux-fecmod)

      from mast client specified

        where mandt = sy-mandt

        AND werks eq '1100'

        AND stlan eq '1'

        AND stlal eq '01'.

    append formu_aux.

    clear formu_aux-material.

    clear formu_aux-feccrea.

    clear formu_aux-fecmod.

  endselect.

What is your opinion?

Thanks.

[former_member184569]

Its fine except that select end select query performance is low .

http://scn.sap.com/community/abap/testing-and-troubleshooting/blog/2012/09/21/selectinto-table-faste...

Hence select into table itab (select appending table itab in the second case) is always preferable than select end select.

[former_member196098]

Hi David

Thry to simlify your query as much as possible

One thing

Avoid:

select...

append..

endselect.

As they are slow in performance..

try using ranges which makes qucker performance

İ write you an example:

   types: BEGIN OF ty_formu_aux,
material type mast-matnr,
feccrea type mast-andat,
formu_aux type mast-aedat,
end of ty_formu_aux
.

DATA: gt_main_formu_aux type STANDARD TABLE OF ty_formu_aux,
      gt_formu_aux_2 type STANDARD TABLE OF ty_formu_aux
      .
RANGES: s_exclude for mast-matnr.


break sdogan.

*Now build the matnr to exclude
select matnr as low
  into TABLE s_exclude
  from mast
  UP TO 500 ROWS
  where werks eq '1100'
          AND stlan eq '1'
          AND stlal eq '01'
  .

  s_exclude(3) = 'IEQ'.

  modify s_exclude transporting sign option
  where sign eq ''.


select matnr andat aedat
  into CORRESPONDING FIELDS OF TABLE gt_main_formu_aux
  from mast
  where
   werks in pcentro
   and   ( andat in pfbusca
   or    aedat in pfbusca ) and
   stlan eq '1'
   and   stlal eq '02'
   and matnr not in s_exclude
  .



select matnr andat aedat
  into CORRESPONDING FIELDS OF TABLE gt_formu_aux_2
  from mast
  where
   werks eq '1100'
   AND stlan eq '1'
   AND stlal eq '01'.

*Now add the other table into the main one
APPEND LINES OF gt_formu_aux_2 to gt_main_formu_aux.

[VXLozano]

David, you can get the same result in less time and with less code lines using Thomas' answer:

1 - forget the SELECT loops

2- First select using INTO TABLE formu_aux

3- Second select using APPENDING TABLE formu_aux

About your code:

1- you can drop the CLIENT SPECIFIED if you will use your system's client (sy-mandt)

2- you don't need to clear the workarea after the APPEND, as it will be reassigned in the next SELECT loop

3- if you want to be sure about the "clearing", you can use just "CLEAR formu_aux." and it will clear the three fields.

4- from your code, it can be though you are using WITH HEADER LINE clause. It's a bad idea, because it makes the code harder to read. I should advice you to use w_form_aux and t_form_aux different variables

5- (not important) EQ is a bit slower (maybe nanoseconds) than =

[former_member196098]

totally agree Lozano

tables with headers are obselete in abap

e.g cant be used in classes or local methods

for step 4- you can use field symbols as well

So follow this David

[arindam_m]

Hi,

You can try CL_SQL* classes, its a way to do your SQL according to DB specific syntax and not bother on SAP specific translation.

Cheers,

Arindam

[former_member196098]

Hi

it will be good to know

You wouldnt need to write db specific query unless there is no other option

İ never written one specific.

Cause the code needs to change as db changes or maybe gets upgraded

Arindam do you have any examples of cl_sql* classes we can try

[arindam_m]

Hi,

Sorry for late reply yes there are limitations but as said earlier UNION is not allowed. So You have to go the DB specific way or do what the man with the invisible ink is suggesting its good

As for examples try doing CL_SQL* in se24 then do a where used list and you will get to your demo program. Also check below:

http://help.sap.com/abapdocu_702/en/abenadbc.htm

Cheers,

Arindam

[ThomasZloch]

Seems that I'm writing with (partially) invisible ink, but still here my suggestion again in code:

SELECT * FROM mast

        INTO TABLE it_mast

        WHERE werks = '1100'

          AND stlan = '1'

          AND stlal = '01'.

SELECT * FROM mast

        APPENDING TABLE it_mast

        WHERE werks = '1100'

          AND stlan = '1'

          AND stlal = '02'

          AND matnr NOT IN

              ( SELECT DISTINCT matnr FROM mast

                       WHERE werks = '1100'

                         AND stlan = '1'

                         AND stlal = '01' ).

Since this is not index supported, there can be runtime issues (depending on size of MAST), but you'll have these anyway no matter how complicated you choose to solve this.

Thomas

[Former Member]

Thanks a lot for your suggestions.

I'm not a programmer and I need to have a solution as soon as possible so, at the moment, I'll keep the abap code as I wrote in my previous reply. It seems to perform relatively fine.

In the future I'll check all your solutions and I hope to improve my code with all your replies.

Thanks again.

Regards.

[VXLozano]

The fast solution usually is not the right one.

Thomas' approach is the right solution, and you just need to invest (not waste) few minutes implementing it.

Your code works now, but when your tables will growth, your program's performance will decrease exponentially and you will need to re-code it, wasting (not investing) your time.

Some programmers thank Crom because some consultants go ABAP (it means work for them in the future). I blame for it.

-----------------

Anyways, Thomas gave you the solution. Twice. At least you can mark his answer as the correct one, and you will mark this thread as "Solved" and we will not need to come here again (and people in the future should find it easier).

[Former Member]

You can be sure that when I had the opportunity I will change the code using the Thomas' approach,

Thanks for your advice.

Regards.

[VXLozano]

It will be a good thing.

BTW, go to the first Thomas answer and mark it as "correct answer". It will help us to track the thread as solved and "forget" it

------------------------------

Serà una bona cosa.

Ja que hi som, ves al primer post del Thomas i marca'l com a "resposta correcta". Així podrem donar per tancat el tema.

------------------------------

Será una buena cosa.

Ya puestos, ve al primer post de Thomas y márcalo como "respuesta correcta". Así podremos dar por zanjado el asunto.

[ThomasZloch]

If you keep mentioning me, they might think that I hired you for this purpose

Looking at our avatars, there might well be some sort of connection...

Thomas

[VXLozano]

Don't worry, I will not eat you (I think this is not the place for a heavier joke).

BTW, you hired me for this purpose, didn't you?

[Former Member]

FYI..

Thomas had coined the term point bot which is a part of SCN Urban Dictionary.

[ThomasZloch]

Wow, your memory is better than the NSA's...cool that the dictionary is still being updated from time to time, a funny read for any regular visitor

Thomas

[custodio_deoliveira]

Nice job thomas. I would simplify (or complicate?) even further:

SELECT * FROM mast

         INTO TABLE it_mast

         WHERE werks = '1100'

           AND stlan = '1'

           AND ( stlal = '01' OR

                 stlal = '02'

             AND matnr NOT IN

            ( SELECT DISTINCT matnr FROM mast

                        WHERE werks = '1100'

                          AND stlan = '1'

                          AND stlal = '01' ) ).

Here your "UNION" clause