Convert value during a join statement

[david_nguyen3]

Hi folks,

Is there a way to convert value of a table field during a select statement? I have a sql statement that doesn't work because a field has zero characters prefix.

SELECT EBAN_{BANFN EBAN}BNFPO EBAN_{STATU EBAN}BSART EBAN_{MENGE EBAN}LFDAT EKKO_{AEDAT EKKO}EBELN EKKO~LIFNR

INTO TABLE GT_RFQ_DATA

FROM EBAN JOIN EKKO ON EKKO_{SUBMI = EBAN}BANFN

EBAN_{BANFN = '0003006807' and EKKO}SUBMI = '3006807'

Is it possible to do a join with that condition? Will reward points.

Note: I know how to get those data without a join, so I will not give out reward points for those advices.

Thanks for all of your helps.

[Former Member]

Before passing values to SELECT, try using conversion routines. That will help to extract data correctly. (If i have correctly understood your problem).

[david_nguyen3]

Hi Ashish,

the values are unknown before SELECT and they are in 2 table EKKO, EBAN as you see in the SELECT. Therefore , there are no way to get value and convert it before SELECT.

Thanks.

[Former Member]

Try using the fucntion module CONVERSION_EXIT_ALPHA_INPUT to pad with leading Zeros

if you want to convert the value of

EKKO_{SUBMI = '3006807' to EKKO}SUBMI = '0003006807'

or if you want to remove leading zeros

use CONVERSION_EXIT_ALPHA_OUTPUT function module.

Data: v_submi like ekko-submi.

v_submi = '3006807'

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = v_submi

IMPORTING

OUTPUT = v_submi

EXCEPTIONS

OTHERS = 1.

SELECT EBAN_{BANFN EBAN}BNFPO EBAN_{STATU EBAN}BSART EBAN_{MENGE EBAN}LFDAT EKKO_{AEDAT EKKO}EBELN EKKO~LIFNR

INTO TABLE GT_RFQ_DATA

FROM EBAN JOIN EKKO ON EKKO_{SUBMI = EBAN}BANFN

EBAN_{BANFN = '0003006807' and EKKO}SUBMI = v_submi

[david_nguyen3]

Hi Sid,

Thanks for your help. But the thing is that i want to <b>join EBAN with EKKO on the condition

EKKO_{SUBMI = EBAN}BANFN.</b> The example values is to show the difference between those values. I want to get a result set that match the condition not just one record.

[Former Member]

In this case i think there is no way to fetch the data with join statment as the value in one table is padded with leading 0's and in other table it is padded with leading 0's.

[david_nguyen3]

I am thinking the same way to but I just want to investigate it. Thanks for your help.

[Former Member]

Sid is right. As the link between 2 tables is not clear. One table field is padded with zeros and second is not padded with zeroes.

Just to try - can you see with LIKE in WHERE condition if it helps.