SELECT SINGLE IS POSSIBLY NOT UNIQUE

[martinlydia]

Hiii,

I want to join two tables VBKD and QMEL using vbeln.

SELECT SINGLE BSTKD

FROM VBKD AS v

INNERJOIN QMEL AS Q

ON V~VBELN = Q~VBELN

INTO wa_xxxx

WHERE QMNUM = wa_xxxx-qmnum

While doing this, I am getting the ATC check message as SELECT SINGLE IS POSSIBLY NOT UNIQUE.

Please provide any suggestions.

Thanks in advance.

Regards,

Lydia Martin.

[vijay_hariharan]

Hello,

VBKD has POSNR as well.. well generally the BSTKD Customer reference has the same value at header and item(Order data) level but it can also be different.. probably why you get the above Message during check.. if you are sure, they would the same or you are looking for the header BSTKD, use POSNR = '000000'..

Regards,
Vijay

[martinlydia]

Hii,

Tried that.. But didn't work...Still got the same error.

Thanks for the suggestion.

[vijay_hariharan]

Are you running a specific ATC check i.e. ATC with....?? since i do not get this message for the above Inner Join statement.

[martinlydia]

Hii,

I am not running ATC with...Just the ATC.

[FredericGirod]

SELECT BSTKD
       UP TO 1 ROWS
       INTO wa_xxxx
       FROM VBKD AS v
            INNER JOIN QMEL AS Q
            ON V~VBELN = Q~VBELN
       WHERE QMNUM EQ wa_xxxx-qmnum.
  
ENDSELECT.

You ATC will certainly allow this

[martinlydia]

Hii,

While trying this, the check message says empty SELECT/ENDSELECT at line xxx....

Thanks for the suggestion

[FredericGirod]

SELECT BSTKD
       UP TO 1 ROWS
       INTO @data(lv_bstkd)
       FROM VBKD AS v
            INNER JOIN QMEL AS Q
            ON @V~VBELN = @Q~VBELN
       WHERE QMNUM EQ @wa_xxxx-qmnum.
  wa_xxxx-bstkd = lv_bstkd.
ENDSELECT.

just do something inside the SELECT / ENDSELECT

[martinlydia]

Thank you soo much...It worked...

[sourabh_maybhate]

Thank you.. it is working fine but after implementing getting error Empty select/Endselect.

[FredericGirod]

Maybe the system expect to have a statement between the SELECT // ENDSELECT.

I am not agree with this check/rule. As you have an UP TO 1 ROWS you expect only one result, so you do not need to proceed inside the SELECT / ENDSELECT

[matt]

frdric.girod I don't agree with UP TO 1 ROWS instead of SINGLE, just to avoid an ATC warning. 😀 See my answer below.

[former_member808116]

If you select a single field in the database table, its return value is a variable, not a Workarea. So if you want it to return a WA, you have to remove Single or just get 1 value out of the total number of values you query.

TYPES: BEGIN OF LTY,
         BSTKD TYPE BSTKD,
         QMNUM TYPE QMNUM,
       END OF LTY.
DATA: WA_XXXX TYPE LTY.
SELECT BSTKD
 INTO @WA_XXXX
 FROM VBKD AS V
 INNER JOIN QMEL AS Q ON V~VBELN = Q~VBELN
 WHERE QMNUM EQ @WA_XXXX-QMNUM.
ENDSELECT.
BREAK QUYNHBC .

[matt]

This conversation has been had before ATC was invented. It is just a warning. It isn't necessarily an error. ATC doesn't know if it's actually ok or not. If you don't use the full primary key you get this message, but often it is spurious.

If you know the warning isn't applicable, some suggest SELECT UP TO 1 ROWS, but this is to my mind the formal check tail wagging the programming dog. It's the ATC warning that's usually not correct. It's adding extra code to simply remove the warning, and decreases readability so goes against clean code principles. UP TO 1 ROWS detracts from the readability of the code.

I would simply use the warn_ok pragma to suppress the message and indicate to anyone reading the code that the lack of a fully specified key is deliberate.

SELECT SINGLE ... ##warn_ok.

As a note, both SELECT SINGLE and UP TO 1 ROWS resolve to the same SQL at the database level (they're both handled as SELECT SINGLE). You can verify this with an SQL trace (ST05) and EXPLAIN, which shows you what the actual SQL issued is.