I want to select all notifications with the relevant information and I also want the notifications that have no equipment. But when I use below join, I only get the ones where the equipment is not null. Shouldn't the left outer join make sure I get everything in table VIQMEL?
I do get the notifications that have no equipment if I delete the AND K~SPRAS EQ 'E'.
Any ideas on how to resolve this?
SELECT v~qmnum,
v~qmart,
t~istat,
t~txt30,
v~aufnr,
v~tplnr,
v~equnr,
v~btpln,
v~qmnam,
v~qmgrp,
v~qmcod,
ct~kurztext,
gt~kurztext,
v~beber,
k~eqktx,
v~qmtxt,
ax~pltxt,
fx~pltxt,
v~priok,
v~erdat,
s~tdid,
a~reltype,
z~aduser
FROM viqmel AS v
LEFT OUTER JOIN iflot AS f ON v~tplnr = f~tplnr
LEFT OUTER JOIN jest AS j ON j~objnr = v~objnr
LEFT OUTER JOIN tj02t AS t ON t~istat = j~stat
LEFT OUTER JOIN iflotx AS fx ON fx~tplnr = v~tplnr
LEFT OUTER JOIN iflotx AS ax ON ax~tplnr = v~btpln
LEFT OUTER JOIN qpct AS ct ON ct~code = v~qmcod
LEFT OUTER JOIN eqkt AS k ON v~equnr = k~equnr
LEFT OUTER JOIN qpgt AS gt ON gt~codegruppe = v~qmgrp
LEFT OUTER JOIN stxh AS s ON s~tdname = v~qmnum
LEFT OUTER JOIN srgbtbrel AS a ON v~qmnum = a~instid_a
LEFT OUTER JOIN zzid_map AS Z ON v~qmnam = z~sapuser
WHERE t~spras = @sy-langu
AND v~qmnum LIKE @p_qmnum
AND v~equnr LIKE @p_equnr
AND v~qmnam LIKE @p_qmnam
AND v~aufnr LIKE @p_aufnr
AND f~tplnr LIKE @p_tplnr
AND t~istat LIKE @p_istat
AND v~beber LIKE @p_beber
AND j~inact <> @abap_true
AND t~istat <> 'I0076'
AND t~spras = 'E'
AND fx~spras = 'E'
AND k~spras = 'E'
INTO TABLE @DATA(et_notifs).
Side note:EQKT is equipment short text (not equipment) and EQKT~SPRAS is language.
Problem: You wrote your condition to only select English text, which is why it ignores records that are joined with non English or ones, that aren't joined at all.
So if you have ( number represents a key ) your text table
1 E ....
2 X ....
3 N ....
4 E ....
After a join texts from table join like this
1 E ....
2 [initial]
3 [initial]
4 E ....
After filter you're left with
1 E ....
4 E ....
With restrictions of SAP Open SQL, excluding joins, as well as joins that including records based on absence of corresponding records from other tables is not possible. The workarounds for excluding joins are generally sub-queries.
You could add a subquery to check select languages based on your filter and ignore that filter in other cases (to include empty records). Try to replace and K~SPRAS EQ 'E' with the following (the idea here is to take the language if it exists and bypass the condition otherwise):
and ( K~SPRAS in (select SPRAS from EQKT where EQUNR=V~EQUNR and spras = 'E')
OR NOT EXISTS (select SPRAS from EQKT where spras = 'E')
)
The idea here is you have 2 subqueries. One of them uses a positive check to include all the languages you need. The other uses a negative check and includes records where that particular language does not exist.
After looking at your question with clear head, I noticed my solution might be too complicated for your needs (even though it will work).
A standard left join on key + condition will fulfill your requirement. Move your and K~SPRAS EQ 'E' into join condition and it will select exactly the way you want it to (A standard left join). Also, if I recall correctly outer keyword doesn't do anything on left/right joins.
LEFT JOIN EQKT AS K ON V~EQUNR EQ K~EQUNR AND K~SPRAS EQ 'E'
PS: Aliases and redundant joins in the question aren't helping with its readability.