topic Re: SQL use Subquery in Application Development and Automation Discussions

SQL use Subquery

tobias96 — Thu, 23 Jun 2022 09:08:38 GMT

Hello,

I try to write a SQL in my ABAP Report but it does not work with subqueries.

I do not know how to change the SQL to fix my problem.

SELECT DISTINCT m~CONTR_NUM,
                m~CONTR_NUM_P,
                s~SUBSCRIPTION_ID,
                h~CONTRACT_TYPE,
                h~CUSTOMER,
                m~PRODUCT_OFFERING,
                p1~MAKTX,
                k~PRODUCT_OFFERING,
                p2~MAKTX
  FROM ( SELECT v~PRODINST_ID, v~CONTR_NUM, p~CONTR_NUM_P, p~PRODUCT_OFFERING
          FROM YGK_CONT_P as p
          JOIN YGK_CONT_RELA_V as v
            ON p~PRODINST_ID = v~PRODINST_ID
           AND p~contr_num = v~contr_num
         WHERE v~RELATION_NAME = 'has_Zweitanbindung'
           AND p~ITEM_STATUS = 'A' ) as m
 inner join (SELECT v~PRODINST_ID,
                    v~CONTR_NUM,
                    p~CONTR_NUM_P,
                    p~PRODUCT_OFFERING
               FROM YGK_CONT_P as p
               JOIN YGK_CONT_RELA_V as v
                 ON p~PRODINST_ID = v~PRODINST_ID
                AND p~contr_num = v~contr_num
              WHERE v~RELATION_NAME = 'has_Erstanbindung'
                AND p~ITEM_STATUS = 'A') as k
    ON m~contr_num = k~contr_num
 Inner join MAKT as p1
    ON ( p1~MATNR = m~PRODUCT_OFFERING AND p1~SPRAS = 'D' )
 Inner join MAKT as p2
    ON ( p2~MATNR = k~PRODUCT_OFFERING AND p2~SPRAS = 'D' )
 INNER JOIN YGK_CONT_H as h
    ON m~contr_num = h~contr_num
 INNER JOIN YGK_CONT_P as s
    ON ( m~contr_num = s~contr_num AND s~PARENT_ID = ' ' AND
       s~item_status = 'A' )
 WHERE ( m~CONTR_NUM, m~CONTR_NUM_P ) NOT IN
       ( SELECT e~CONTR_NUM, e~CONTR_NUM_P
          FROM YGK_CONT_RELA_V as e
         WHERE e~RELATION_NAME = 'has_ProaktivesServicemanagement' ).

Re: SQL use Subquery

venkateswaran_k — Thu, 23 Jun 2022 09:27:45 GMT

What is the error you are getting?

Re: SQL use Subquery

Sandra_Rossi — Thu, 23 Jun 2022 09:46:37 GMT

Please use the COMMENT button for comments, questions, adding details, replying to a comment or a proposed solution or to the OP question, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area.

Re: SQL use Subquery

Sandra_Rossi — Thu, 23 Jun 2022 10:27:58 GMT

It's probably possible with EXISTS (it's kind of join), but it would be too much time-consuming to answer.

Otherwise, if it works as native SQL in your database, just execute it as native SQL.

Good luck!

Re: SQL use Subquery

ThorstenHoefer — Thu, 23 Jun 2022 12:43:22 GMT

Please try this:

with +test1 as 
( SELECT v~PRODINST_ID, v~CONTR_NUM, p~CONTR_NUM_P, p~PRODUCT_OFFERING
          FROM YGK_CONT_P as p
          JOIN YGK_CONT_RELA_V as v
            ON p~PRODINST_ID = v~PRODINST_ID
           AND p~contr_num = v~contr_num
         WHERE v~RELATION_NAME = 'has_Zweitanbindung'
           AND p~ITEM_STATUS = 'A' )
),

test2 as
(SELECT v~PRODINST_ID,
                    v~CONTR_NUM,
                    p~CONTR_NUM_P,
                    p~PRODUCT_OFFERING
               FROM YGK_CONT_P as p
               JOIN YGK_CONT_RELA_V as v
                 ON p~PRODINST_ID = v~PRODINST_ID
                AND p~contr_num = v~contr_num
              WHERE v~RELATION_NAME = 'has_Erstanbindung'
                AND p~ITEM_STATUS = 'A'

)
SELECT DISTINCT m~CONTR_NUM,
                m~CONTR_NUM_P,
                s~SUBSCRIPTION_ID,
                h~CONTRACT_TYPE,
                h~CUSTOMER,
                m~PRODUCT_OFFERING,
                p1~MAKTX,
                k~PRODUCT_OFFERING,
                p2~MAKTX
  FROM +test as m
 inner join +test2 as k
    ON m~contr_num = k~contr_num
 Inner join MAKT as p1
    ON ( p1~MATNR = m~PRODUCT_OFFERING AND p1~SPRAS = 'D' )
 Inner join MAKT as p2
    ON ( p2~MATNR = k~PRODUCT_OFFERING AND p2~SPRAS = 'D' )
 INNER JOIN YGK_CONT_H as h
    ON m~contr_num = h~contr_num
 INNER JOIN YGK_CONT_P as s
    ON ( m~contr_num = s~contr_num AND s~PARENT_ID = ' ' AND
       s~item_status = 'A' )
 WHERE ( m~CONTR_NUM, m~CONTR_NUM_P ) NOT IN
       ( SELECT e~CONTR_NUM, e~CONTR_NUM_P
          FROM YGK_CONT_RELA_V as e
         WHERE e~RELATION_NAME = 'has_ProaktivesServicemanagement' ).<br>

Re: SQL use Subquery

venkateswaran_k — Thu, 23 Jun 2022 12:46:18 GMT

I was also about to say this soln. But it is giving syntax error as "Comma without preceding colon (after WITH?).

How to resolve this

Re: SQL use Subquery

ThorstenHoefer — Thu, 23 Jun 2022 12:52:46 GMT

Please check that you use tte new sql syntax. You should select the result "into table @lt_data"

Which Abap Version do you use?

WITH - ABAP-Schlüsselwortdokumentation (sap.com)

 WHERE not exists 
       ( SELECT  @abap_true 
          FROM YGK_CONT_RELA_V as e
         WHERE e~RELATION_NAME = 'has_ProaktivesServicemanagement' and
               e~CONTR_NUM = m~CONTR_NUM and
               e~CONTR_NUM_P = m~CONTR_NUM_P )

Re: SQL use Subquery

tobias96 — Thu, 23 Jun 2022 13:27:34 GMT

Hi thanks for your answer.

Unfortunately it will not work for me I do not know exactly why not.

I get an error in SE80 at the first line

 "" is grammatically not allowed here.

The part with where not in is not the problem.

I use ABAP Version 750

WHERE ( m~CONTR_NUM, m~CONTR_NUM_P ) NOT IN
       ( SELECT e~CONTR_NUM, e~CONTR_NUM_P
          FROM YGK_CONT_RELA_V as e
         WHERE e~RELATION_NAME = 'has_ProaktivesServicemanagement' ).

Re: SQL use Subquery

Sandra_Rossi — Thu, 23 Jun 2022 14:47:22 GMT

CTE (WITH) is only available from 7.51.