How do I write a SQ02 select query?

[bmierzwi]

Hello,

I have a problem with my code and after days of googling, I am clueless. I am new with ABAP, so I am not sure if it's relevant, but I am writing it in SQ02 transaction. I need to select the invoice number by a reference number from VBFA. There are two restrictions:
1. The reference number has to be a WZ document, so it needs to begin with either '1' or '2' ('1' for documents and '2' corrections).
2. I need to always select the latest record because corrections might appear and I always need to have it included.

That is my code:

TYPES: BEGIN OF ty_vbf,
vbeln TYPE vbeln,
erdat TYPE erdat,
END OF ty_vbf.

DATA: wa_vbf TYPE ty_vbf.

SELECT vbeln erdat
FROM vbfa
INTO wa_vbf
WHERE vbelv = BKPF-XBLNR
AND vbelv LIKE '1%' OR vbelv LIKE '2%'
AND erdat = ( select MAX( erdat ) from vbfa where erdat LE sy-datum ).
ENDSELECT.

This code does not work. Every time I try to even touch any date field, the program just won't work, it will just keep processing endlessly. I believe that without adding any "and's", I am selecting multiple records and that is why if I don't add any restrictions, the column just contains 0 for every record. So without the "and's" it will start and return all zeros, but if I dare add any restrictions, I can kiss my program goodbye.

I also tried creating internal tables, sorting them, using ORDER BY, looping... nothing works.

Please, help.

[Jelena_Perfiljeva]

I'm a bit late to reply but seeing how there has been no response at all, maybe it'll still be helpful.

There is a lot to unpack here (which probably explains why no one replied 🙂 ).

SQ02 is Query Infoset transaction, so I'm guessing you have some infoset that includes BKPF table and you're trying to add invoice information from SD via additional code because simple JOIN is not possible?

This code won't work for multiple reasons.

1. This part: "select MAX( erdat ) from vbfa where erdat LE sy-datum" would select the date for any document from the whole table. It's not going to select latest date for the specific documents you're looking for. This is SQL, so it reads quite a well and is literally "give me a latest date that exists in this table". Which in any productive system will always be today or last working day.

2. I suspect the whole SELECT might also be incorrect. If by "invoices" you mean billing documents (displayed in VF03) then you don't need to read the VBFA table at all. Instead, you need VBRK table. It should also have XBLNR field and typically has a secondary index for it, so should work faster. To get the most recent document only, you can SELECT SINGLE MAX (erdat) vbeln WHERE... (you'll need to add GROUP BY and probably ORDER BY or something). You don't need the subquery (p. 1 above).

3. LIKE 1% should not be part of SELECT, instead use IF to check if this BKPF record is eligible. Don't start selecting anything if you already know it's not needed. Also, I recommend using document type instead of relying on number ranges. Number range is configurable and can change at any time.

4. Keep in mind, because it's an infoset, your SELECT will be called for every single record. Since you're not using primary key, this will be costly. Perhaps a custom report would be a better alternative in this case, especially if this query pulls up many records.