Need help on how to code this SQL statement! (one key has leading zeros)

[Former Member]

Good day, everyone!

First of all, I apologize if this isn't the best forum. I thought of putting it in the SAP Oracle database forum, but the messages there seemed to be geared outside of ABAP SELECTs and programming. Here's my question:

I would like to join the tables FMIFIIT and AUFK. The INNER JOIN will be done between FMIFIIT's MEASURE (Funded Program) field, which is char(24), and AUFK's AUFNR (Order Number) field, which is char(12).

The problem I'm having is this: All of the values in AUFNR are preceeded by two zeros. For example, if I have a MEASURE value of '5200000017', the corresponding value in AUFNR is '005200000017'. Because I have my SQL statement coded to just match the two fields, I obviously get no records returned because, I assume, of those leading zeros.

Unfortunately, I don't have a lot of experience coding SQL, so I'm not sure how to resolve this.

Please help! As always, I will award points to ALL helpful responses!

Thanks!!

Dave

[Former Member]

Hi,

Instead of Select joins you can use SELECT with FOR ALL ENTRIES addition. Please check the below code...

Firt read data from AUFK.

SELECT ..... FROM aufk INTO TABLE i_aufk WHERE .......<your condition>.

IF sy-subrc = 0.

SORT i_aufk[] by AUFNR.

LOOP AT i_aufk.

call function module 'CONVERSION_EXIT_ALPHA_OUTPUT'. Pass i_aufk-aufnr to the importing and exporting parameters of the function module.

Modify i_aufk index sy-tabix.

ENDLOOP.

SELECT ........FROM FMIFIIT INTO TABLE i_FMIFIIT FOR ALL ENTRIES IN I_AUFK where MEASURE = i_aufk-aufnr.

ENDIF.

Rgds,

Bujji

[Former Member]

Use conversion exit CONVERSION_EXIT_ALPHA_INPUT for AUFK-AUFNR.

[Former Member]

I don't understand -- where do I use this? Do I use it before the SQL statement somehow? Afterwards? I'm not understanding how this will help.

[GauthamV]

hi,

after selecting data into the table use that function module and pass ur aufnr value.

reward if hlpful.

[Former Member]

Hi,

Before select query,call this FM * CONVERSION_EXIT_ALPHA_INPUT*.Pass AUFK-AUFNR to it.In the output you will get Order number with padded zeroes.

[Former Member]

Hi,

Instead of Select joins you can use SELECT with FOR ALL ENTRIES addition. Please check the below code...

Firt read data from AUFK.

SELECT ..... FROM aufk INTO TABLE i_aufk WHERE .......<your condition>.

IF sy-subrc = 0.

SORT i_aufk[] by AUFNR.

LOOP AT i_aufk.

call function module 'CONVERSION_EXIT_ALPHA_OUTPUT'. Pass i_aufk-aufnr to the importing and exporting parameters of the function module.

Modify i_aufk index sy-tabix.

ENDLOOP.

SELECT ........FROM FMIFIIT INTO TABLE i_FMIFIIT FOR ALL ENTRIES IN I_AUFK where MEASURE = i_aufk-aufnr.

ENDIF.

Rgds,

Bujji

[Former Member]

hiiii

use following code that will solve your leading zero proble

DATA:
          w_matnr LIKE mara-matnr.

ls_data-matnr        = ls_excel-value.
        w_matnr = ls_data-matnr.
        CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
          EXPORTING
            input  = w_matnr
          IMPORTING
            output = w_matnr.

        ls_data-matnr  = w_matnr.

\[removed by moderator\]

thx

twinkal

Edited by: Jan Stallkamp on Jun 17, 2008 6:17 PM

[Former Member]

hiii

you use this FM before using that value..suppose as given in this FM after converting it with leading zeros you can use that values in SELECT query.

\[removed by moderator\]

thx

twinkal

Edited by: Jan Stallkamp on Jun 17, 2008 6:17 PM

[Former Member]

Hi Deva,

Based on your requirement, you can't join the tables FMIFIIT and AUFK based on the condition MEASURE (Funded Program) field, which is char(24), and AUFK's AUFNR (Order Number) field, which is char(12). because if you join 2 tables the joining fields should be use the same domain. so that you can't join.

1. select the data from FMIFIIT table

select f1 f2 etc,,

MEASURE as aufnr

from FMIFIIT

into table it_fmifiit

where <Condition>

2. Select the data from AUFK.

select f1 f2 etc..

from AUFK

into table it_aufk.

for all entries in it_fmifiit

where aufnr = it_fmifiit-aufnr and <other conditions>.

\[removed by moderator\]

Regards,

Boobalan Suburaj

Edited by: Jan Stallkamp on Jun 17, 2008 6:17 PM

[christine_evans]

>

> Good day, everyone!
> I would like to join the tables FMIFIIT and AUFK.  The INNER JOIN will be done between FMIFIIT's MEASURE (Funded Program) field, which is char(24), and AUFK's AUFNR (Order Number) field, which is char(12).
> 
> The problem I'm having is this:  All of the values in AUFNR are preceeded by two zeros.  For example, if I have a MEASURE value of '5200000017', the corresponding value in AUFNR is '005200000017'.  Because I have my SQL statement coded to just match the two fields, I obviously get no records returned because, I assume, of those leading zeros.
> Dave

You can't do a join like this in SAP's open SQL. You could do it in real SQL ie EXEC.... ENDEXEC by using SUSBTR to strip off the leading zeros from AUFNR but this would not be a good idea because a) modifying a column in the WHERE clause will stop any index on that column being used and b) using real SQL rather than open SQL is really not something that should be encouraged for database portability reasons etc.

Forget about a database join and do it in two stages; get your AUFK data into an itab, strip off the leading zeros, and then use FAE to get the FMIFIIT data (or do it the other way round).

I do hope you've got an index on your FMIFIIT MEASURE field (we don't have one here); otherwise your SELECT could be slow if the table holds a lot of data.

[Former Member]

Like this:

REPORT ztest LINE-SIZE 80 MESSAGE-ID zc.

TABLES: fmifiit, aufk.

DATA: BEGIN OF itab1 OCCURS 0.
        INCLUDE STRUCTURE fmifiit.
DATA:   aufnr TYPE aufk-aufnr,
      END   OF itab1.

DATA: BEGIN OF itab2 OCCURS 0.
        INCLUDE STRUCTURE aufk.
DATA: END   OF itab2.

SELECT * FROM fmifiit UP TO 100 ROWS
  INTO CORRESPONDING FIELDS OF TABLE itab1.

LOOP AT itab1.
  MOVE itab1-measure TO itab1-aufnr.
  MODIFY itab1.
ENDLOOP.

SELECT * FROM aufk
  INTO CORRESPONDING FIELDS OF TABLE itab2
  FOR ALL ENTRIES IN itab1
  WHERE aufnr = itab1-aufnr.

Rob

[Former Member]

Thanks everyone. I think I've got it now.