topic Re: Convert value during a join statement in Application Development and Automation Discussions

Convert value during a join statement

david_nguyen3 — Mon, 14 Aug 2006 20:54:34 GMT

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.

Re: Convert value during a join statement

Former Member — Mon, 14 Aug 2006 21:01:37 GMT

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

Re: Convert value during a join statement

Former Member — Mon, 14 Aug 2006 21:11:42 GMT

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

Re: Convert value during a join statement

david_nguyen3 — Mon, 14 Aug 2006 21:11:56 GMT

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.

Re: Convert value during a join statement

david_nguyen3 — Mon, 14 Aug 2006 21:20:51 GMT

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.

Re: Convert value during a join statement

Former Member — Mon, 14 Aug 2006 21:26:16 GMT

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.

Re: Convert value during a join statement

david_nguyen3 — Mon, 14 Aug 2006 21:31:10 GMT

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

Re: Convert value during a join statement

Former Member — Mon, 14 Aug 2006 21:37:29 GMT

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.