06-23-2022 10:08 AM
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' ).
06-23-2022 10:27 AM
06-23-2022 10:46 AM
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.
06-23-2022 11:27 AM
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!
06-23-2022 1:43 PM
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>
06-23-2022 1:46 PM
Hi
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
06-23-2022 1:52 PM
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 )
06-23-2022 2:27 PM
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' ).
06-23-2022 3:47 PM