Page 1 of 2

Strugling with SELECT

PostPosted: Tue Jan 17, 2017 4:39 am
by rpinxt
As the subject already says, I'm struggling with the select statement.

I am trying to get the Handeling Unit from VBFA table via the Delivery number from LIKP/LIPS with :
Code: Select all
CLEAR RP_HU.
SELECT SINGLE VBELN FROM VBFA
  INTO RP_HU
  WHERE VBELV = LIKP-VBELN AND VBTYP_N = 'X'.


But the output is only 1 handeling unit whereas 1 delivery could have multiple hu's.
I understand it probably is realted to select SINGLE because I only get the 1st HU from the 4 lines (3 lines with the same Hu so 2 different hu's).

However with a SELECT (and ENDSELECT) I only get the last 1 from the list for ever entry....

If I manual go to VBFA table and pass the delivery and set type to "X" then I get 4 lines. 3 with the same HU (and different item numbers) and 1 line which has no item number (blank).
And that is ok.

So what am I missing??

ps: This is only the first part of my problem....Some deliveries handeling unit combi's can not be found in VBFA but need to come from VTFA... But this problem first.

Re: Strugling with SELECT

PostPosted: Tue Jan 17, 2017 5:23 am
by Gothmog
If you use SELECT SINGLE, you get only one line (at most).

With SELECT ..... ENDSELECT, the program will loop at the four entries, and for each execute the code that is between the SELECT and the ENDSELECT instruction. That's why ou're left with the last one after the loop.

You could also use SELECT ... INTO TABLE itab to get the 4 entries into an internal table, and loop at this table to do whatever you want to do with these entries.

Re: Strugling with SELECT

PostPosted: Tue Jan 17, 2017 5:37 am
by rpinxt
Ok clear.
But could you maybe help me a bit on my way?

How would I setup that table itab and then get those 4 lines into the output.

I am already trying for 2 days several scenario's but keep running into walls.
Seemed so simple.
Pass a delivery number into VBFA and get the (in this case) 4 output lines which have type 'X'.

Then my second hurdle would be when there is no output for VBFA repeat the same trick but then for VTFA.

You may already have noticed I have no experience with internal tables :oops: so any help would be very welcome.

Re: Strugling with SELECT

PostPosted: Tue Jan 17, 2017 5:56 am
by Gothmog
OK, here's are a fex ways you could do this.

With SELECT...ENDSELECT :
Code: Select all
CLEAR RP_HU.
SELECT VBELN FROM VBFA
  INTO RP_HU
  WHERE VBELV = LIKP-VBELN AND VBTYP_N = 'X'.
  "do whatever you want here with this document number
ENDSELECT.
IF sy-subrc <> 0. "no document found
  "try selecting in VTFA
ENDIF.


With internal table :
Code: Select all
DATA: lt_hus TYPE STANDARD TABLE OF VBFA-VBELN.

CLEAR lt_hus.
SELECT VBELN FROM VBFA
  INTO TABLE lt_hus
  WHERE VBELV = LIKP-VBELN AND VBTYP_N = 'X'.
IF sy-subrc <> 0. "no document found
  "try selecting in VTFA
ENDIF.

LOOP AT lt_hus INTO RP_HU.
  "do whatever you want here with this document number
ENDLOOP.

Re: Strugling with SELECT

PostPosted: Tue Jan 17, 2017 6:22 am
by rpinxt
Thanks very much Gothmog!

I started immediately with you first suggestion.
And where it said do anything you want with the document number I tried to WRITE it to RP_HU.

However the output is still only 1 line then (and leaves me with the last selected line)
I take it, it overwrites the line 3 times and therefore I still am left with 1 line output.

Figure in your second option the same will happen if I use WRITE to RP_HU.

So if you also could teach me how abap will not overwrite the same line constantly but for every line he finds in the select make a new output line I would be very grateful!

Re: Strugling with SELECT

PostPosted: Tue Jan 17, 2017 6:50 am
by rpinxt
something strange to add....

When I arrow back from the output screen (which only showed 1 line) I got to an extra screen which I normally not see before.
And it had the 4 document numbers I would expect in the output of the report listed horizontally.

I also tried WRITE: / RP_HU but this also gave only 1 output line.
But when you then go back you still get the 4 documents expected in the output of the report but then listed vertically....

Re: Strugling with SELECT

PostPosted: Wed Jan 18, 2017 4:13 am
by rpinxt
write, move, read into.....nothing is working.... :|

This code works fine :
Code: Select all
DATA: it_hus TYPE STANDARD TABLE OF VBFA.
CLEAR it_hus.
SELECT VBELN FROM VBFA
  INTO TABLE it_hus
  WHERE VBELV = LIKP-VBELN AND VBTYP_N = 'X'.
IF sy-subrc <> 0. "no document found
  CLEAR it_hus.
  SELECT VBELN FROM VTFA
    INTO TABLE it_hus
    WHERE VBELV = LIKP-VBELN AND VBTYP_N = 'X'.
ENDIF.
LOOP AT it_hus INTO RP_HU.
  "????????????"
ENDLOOP.


So anybody an idea what code needs to go on the "??????" in order to show the data from interal table it_hus in SQ02 query field called RP_HU?

Thanks!

Re: Strugling with SELECT

PostPosted: Wed Jan 18, 2017 5:08 am
by Gothmog
At this point, you'd better stop torturing Queries and start writing custom programs.
Here, you'll have to mess with field symbols, internal table...

But you could try this:
- in the DATA code section, declare these :
Code: Select all
FIELD-SYMBOLS:
  <lt_data> TYPE STANDARD TABLE,
  <ls_data> TYPE ANY,
  <l_field> TYPE ANY.
DATA: it_hus TYPE STANDARD TABLE OF VBFA.

- in the coding for the field RP_HU (make sure it's executed last of all fields coding):
Code: Select all
CLEAR it_hus.
SELECT VBELN FROM VBFA
  INTO TABLE it_hus
  WHERE VBELV = LIKP-VBELN AND VBTYP_N = 'X'.
IF sy-subrc <> 0. "no document found
  CLEAR it_hus.
  SELECT VBELN FROM VTFA
    INTO TABLE it_hus
    WHERE VBELV = LIKP-VBELN AND VBTYP_N = 'X'.
ENDIF.

IF <lt_data> IS NOT ASSIGNED.
  ASSIGN  ('%dtab[]') TO <lt_data>.
ENDIF.

IF <ls_data> IS NOT ASSIGNED.
  ASSIGN  ('%dtab') TO <lt_data>.
ENDIF.

IF <l_field> IS NOT ASSIGNED.
  ASSIGN COMPONENT 'RP_HU' OF STRUCTURE <ls_data> TO <l_field>.
ENDIF.

LOOP AT it_hus INTO RP_HU.
  AT LAST.
    EXIT.
  ENDAT.

  <l_field> = RP_HU.
  APPEND <ls_data> TO <lt_data>.
ENDLOOP.


It could work.

Re: Strugling with SELECT

PostPosted: Wed Jan 18, 2017 5:25 am
by rpinxt
Thanks Gothmog, you are probably right.
But in our system the only possibility I have to develop my own queries is SQ01/SQ02..... :(
Multinational with no BI environment....they still exist...

But thanks for you code addition.
However the second part does give me an error message about compatibility :
"When you use the "AT" statement, the line type of the corresponding
table must be compatible with the work area. However, the line type of table "IT_HUS" is n"
(last word is not I guess but it broke off).

Must I change Type Any to something else?

Re: Strugling with SELECT

PostPosted: Wed Jan 18, 2017 6:42 am
by rpinxt
Ok tried which changing types but that did not the trick...

Also blocked this piece of code as the error is caused by this piece :
AT LAST.
EXIT.
ENDAT.

But although it says no syntax error, when running the Query it crashes.

So either this part is vital or the code does not work :cry:

Re: Strugling with SELECT

PostPosted: Wed Jan 18, 2017 8:01 am
by Gothmog
Oh, it_hus declaration should be
Code: Select all
DATA: it_hus TYPE STANDARD TABLE OF VBFA-VBELN.
(I copy-pasted your code on this section).

Re: Strugling with SELECT

PostPosted: Wed Jan 18, 2017 8:57 am
by rpinxt
Thanks Gothmog,

Now it takes the code.
Unfortunately the Query now crashes.

Runtime error 'ASSIGN_TYPE_CONFLICT'

Crashes on this line :
Code: Select all
IF <ls_data> IS NOT ASSIGNED.
    ASSIGN  ('%dtab') TO <lt_data>.
  ENDIF.


And then line about ASSIGN.

Re: Strugling with SELECT

PostPosted: Wed Jan 18, 2017 10:15 am
by rpinxt
Ok think now that it might have been a typo?
Seems like you forgot [] behind %dtab?

I now added that and the code continious.
However then it crashes a few lines later at :
Code: Select all
<l_field> = RP_HU.


It is called a GETWA_NOT_ASSIGNED error....

Re: Strugling with SELECT

PostPosted: Wed Jan 18, 2017 11:14 am
by Gothmog
The typo was :
Code: Select all
IF <ls_data> IS NOT ASSIGNED.
  ASSIGN  ('%dtab') TO <ls_data>. "not <lt_data>
ENDIF.

Re: Strugling with SELECT

PostPosted: Thu Jan 19, 2017 3:39 am
by rpinxt
Thanks for clearing this Gothmog!

Query now runs without problems.
The output however is not looking like it should and rather strange :

let me try an image :
Image

4 lines is correct however the first 3 lines need also to be filled.
They would have the same values as the last list, I think this is where it goes wrong? (a 1 to n connection is not possible?)

Also the last HU shows as asterix (*****), but should also be 11414698.

Can this be fixed by a change in the code or did we hit a dead end now.... :?