Skip to Content

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

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

8 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 12:26 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 12:38 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 12:19 PM

    Use conversion exit CONVERSION_EXIT_ALPHA_INPUT for AUFK-AUFNR.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 12:27 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 12:29 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 17, 2008 at 01:15 PM

    >

    > 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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 01:49 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 17, 2008 at 04:07 PM

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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.