1

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).
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Matthijs Mennens
  • 1,125
  • 9
  • 33
  • 2
    Just a small comment: if you don't use alias, or use significative alias, you just will need to write a bit more of letters, BUT the SQL will be much more easier to read – VXLozano May 29 '18 at 10:14
  • 2
    BTW, you are retrieving the status ISTAT from the texts' table, and not from the statuses' ... I'm pretty sure you'll have your statuses translated for al your languages, but I dislike that approach (just a personal thought) And please, move the first condition a bit lower... use the first WHERE positions for the main table: you'll never know how the Workbench and/or the DBMS will translate that WHERE, make things easier for them :) – VXLozano May 29 '18 at 12:53
  • Thanks for your feedback, VXLozano. – Matthijs Mennens May 31 '18 at 07:01

1 Answers1

3

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    ....

Solutions

Unnecessarily complicated solution, using exclusion subquery

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.

Update: Minimalistic solution (left join on key + condition)

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.

Zero
  • 1,562
  • 1
  • 13
  • 29