This website is not affiliated with, sponsored by, or approved by SAP AG.

Strugling with SELECT

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Strugling with SELECT

Postby rpinxt » Tue Jan 17, 2017 4:39 am

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.
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby Gothmog » Tue Jan 17, 2017 5:23 am

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.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67
Gothmog
 
Posts: 1941
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Strugling with SELECT

Postby rpinxt » Tue Jan 17, 2017 5:37 am

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.
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby Gothmog » Tue Jan 17, 2017 5:56 am

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.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67
Gothmog
 
Posts: 1941
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Strugling with SELECT

Postby rpinxt » Tue Jan 17, 2017 6:22 am

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!
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby rpinxt » Tue Jan 17, 2017 6:50 am

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....
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby rpinxt » Wed Jan 18, 2017 4:13 am

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!
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby Gothmog » Wed Jan 18, 2017 5:08 am

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.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67
Gothmog
 
Posts: 1941
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Strugling with SELECT

Postby rpinxt » Wed Jan 18, 2017 5:25 am

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?
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby rpinxt » Wed Jan 18, 2017 6:42 am

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:
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby Gothmog » Wed Jan 18, 2017 8:01 am

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).
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67
Gothmog
 
Posts: 1941
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Strugling with SELECT

Postby rpinxt » Wed Jan 18, 2017 8:57 am

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.
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby rpinxt » Wed Jan 18, 2017 10:15 am

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....
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Strugling with SELECT

Postby Gothmog » Wed Jan 18, 2017 11:14 am

The typo was :
Code: Select all
IF <ls_data> IS NOT ASSIGNED.
  ASSIGN  ('%dtab') TO <ls_data>. "not <lt_data>
ENDIF.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67
Gothmog
 
Posts: 1941
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Strugling with SELECT

Postby rpinxt » Thu Jan 19, 2017 3:39 am

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.... :?
rpinxt
 
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Next

Return to ABAP

Who is online

Users browsing this forum: No registered users and 10 guests





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.