topic Re: SELECT ... Join problems with LIFNR in Application Development and Automation Discussions

SELECT ... Join problems with LIFNR

luis_rod — Mon, 11 Jul 2016 20:57:55 GMT

Hi all,

(Warning! Newbie here, so please bear with me).

We have an extension to our VBAP table where we store a version of the LFA1-LIFNR field. Let's call it ZLIFNR.

If I check the VBAP table, I can see both, the “normal” value of ZLIFNR, say 123456 and the “Unconverted value”: '0000123456'.

On the other side, LFA1 has a record with the proper '123456' / '0000123456' value in LIFNR.

My problem begins when I try to do a:

SELECT ZLIFNR, NAME1

FROM VBAP LEFT JOIN LFA1

ON VBAP~ZLIFNR = LFA1~LIFNR

INTO CORRESPONDING FIELDS OF TABLE gt_mytable.

In this case ZLIFNR gets inserted into GT_MYTABLE without the leading zeroes AND the join seems to fail.

Any ideas? How could I retain the leading ZEROES when doing the SELECT?

TIA,

Luis.

Re: SELECT ... Join problems with LIFNR

Juwin — Tue, 12 Jul 2016 00:13:16 GMT

Leading zeros is the way data is stored in the database. When you do a select, the leading zeros are always retained by default.

If the SELECT failed, and you didn't get any values in the table, how did you know that ZLIFNR is being selected without leading zeros?


Luis Rodriguez wrote:
In this case ZLIFNR gets inserted into GT_MYTABLE  without the leading zeroes AND the join seems to fail.

Thanks, Juwin

Re: SELECT ... Join problems with LIFNR

Former Member — Tue, 12 Jul 2016 01:07:38 GMT

Dear Luis,

Do you use the data element (LIFNR) in your table VBAP-ZLIFNR ?

Because the domain of data element (LIFNR) has a conversion input (ALPHA) which will save with the leading zero in the database.

Regards,

Yance

Re: SELECT ... Join problems with LIFNR

luis_rod — Tue, 12 Jul 2016 01:39:00 GMT

Justin,

Thanks for your answer.

The SELECT gets the ZLIFNR field from VBAP and inserts it into gt_mytable without any problems. NAME1 should be available from LFA1 thru the LEFT JOIN . Is this last field that I am unable to get.

When I debug the program I can see that gt_mytable has loaded ZLIFNR (without leading zeros). NAME1 is blank.

Re: SELECT ... Join problems with LIFNR

luis_rod — Tue, 12 Jul 2016 01:50:00 GMT

Yance,

Thanks for your post.

The guys who designed the table's extension did it in base to LIFNR. In fact, if I check VBAP-LIFNR with SE16n, I can see the field with the leading zeroes. It is only when I try to JOIN it to LFA1 that I seem to have some problems.

Thanks again,

Luis

Re: SELECT ... Join problems with LIFNR

Former Member — Tue, 12 Jul 2016 02:24:10 GMT

Dear Luis,

"When I debug the program I can see that gt_mytable has loaded ZLIFNR (without leading zeros). NAME1 is blank."

I think that the data not saved with leading zeros. Please use CONVERSION_EXIT_ALPHA_INPUT when saved ZLIFNR in VBAP table.

Regards,

Yance

Re: SELECT ... Join problems with LIFNR

luis_rod — Tue, 12 Jul 2016 02:31:47 GMT

Yance,

I had thought about that. But, if that had been the case, Why does the value appear with leading zeroes when I check it with SE16n?

Regards,

Luis

Re: SELECT ... Join problems with LIFNR

Former Member — Tue, 12 Jul 2016 03:19:55 GMT

Dear Luis,

As long as the total of characters (ZLIFNR) including the leading zero is 10, It will be sure that will get the data of LFA1.

Regards,

Yance

Re: SELECT ... Join problems with LIFNR

luis_rod — Tue, 12 Jul 2016 19:20:49 GMT

Yance,

Wasn't able to see why the data is inserted without the leading zeroes, as the DB table looks ok using SE16n.

Nevertheless, as further evaluation of the table contents shows that this is a sparsely populated field, it was decided to use a LOOP AT construct which, along with the proper concatenation of zeroes helped us to solve, albeit a little bit inelegantly, our problem.

Thanks for your help,

Luis

Re: SELECT ... Join problems with LIFNR

Clemenss — Tue, 12 Jul 2016 22:56:49 GMT

Google VBFA. Use SAP Standard. Avoid self-made troubles.

Regards Clemens

Re: SELECT ... Join problems with LIFNR

Juwin — Tue, 12 Jul 2016 23:32:08 GMT

You mean VBPA?

Re: SELECT ... Join problems with LIFNR

Clemenss — Wed, 13 Jul 2016 04:56:19 GMT

Whats is VBFA Table? | SCN