Skip to Content
author's profile photo Former Member
Former Member

select stmt to update database table

Hi All,

when the option blockremov is pressed and check box is checked, I have to update likp table, with lifsk = ' '. Can you please help me with it.

when 'BLOCKREMOV'.

LOOP AT IT_OUTPUT.

IF ( IT_OUTPUT-CHECK = 'X' AND sy-ucomm = 'BLOCKREMOV' ).

UPDATE TABLE LIKP

WHERE VBELN = IT_OUTPUT-VBELN

TRANSPORTING LIFSK

WHERE LIFSK = ' '.

ENDIF.

ENDLOOP.

Thanks,

Veni.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

12 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 09:40 PM

    Hi,

    Check this code ...For the AT USER-COMMAND...

    AT USER-COMMAND.

    DATA: v_index TYPE syindex.

    CASE sy-ucomm.

    WHEN 'SELALL'.

    DESCRIBE TABLE it_output.

    DO sy-tfill TIMES.

    v_index = sy-index + 6.

    DATA: wa LIKE it_output.

    READ LINE v_index FIELD VALUE it_output-check.

    MODIFY LINE v_index FIELD VALUE it_output-check FROM 'X'.

    IF sy-subrc <> 0.

    EXIT.

    ENDIF.

    ENDDO.

    WHEN 'DESALL'.

    DESCRIBE TABLE it_output.

    DO sy-tfill TIMES.

    v_index = sy-index + 6.

    READ LINE v_index FIELD VALUE it_output-check.

    MODIFY LINE v_index FIELD VALUE it_output-check FROM ' '.

    IF sy-subrc <> 0.

    EXIT.

    ENDIF.

    ENDDO.

    WHEN 'BLOCKREMOV'.

    DATA: vbkok LIKE vbkok.

    DATA: v_check LIKE it_output-check.

    DATA: v_vbeln LIKE it_output-vbeln.

    DESCRIBE TABLE it_output.

    DO sy-tfill TIMES.

    CLEAR: v_check,

    v_vbeln.

    v_index = sy-index + 6.

    READ LINE v_index FIELD VALUE it_output-check INTO v_check

    it_output-vbeln INTO v_vbeln.

    IF sy-subrc <> 0.

    EXIT.

    ENDIF.

    CHECK v_check = 'X'.

    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

    EXPORTING

    input = v_vbeln

    IMPORTING

    output = v_vbeln.

    vbkok-vbeln_vl = v_vbeln.

    vbkok-kzlsp = 'X'.

    vbkok-lifsk = ' '.

    CALL FUNCTION 'WS_DELIVERY_UPDATE'

    EXPORTING

    vbkok_wa = vbkok

    delivery = v_vbeln

    EXCEPTIONS

    OTHERS = 1.

    COMMIT WORK.

    DELETE IT_OUTPUT WHERE VBELN = v_vbeln.

    ENDDO.

    PERFORM display_data.

    ENDCASE.

    Hope this helps..

    Thanks,

    Naren

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you everyone for your help.

      Rob, I will definitely consider your suggestion, actually when my lead gave this req, I told him that I will do it with BDC or BAPI, then he told me to directly update the table with select stmts. I will show this to him and tell the consequences also. Thanks a lot.

      Thank you Naren. It is working fine now. I really appreciate your help.

      Regards,

      Veni.

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 05:04 PM

    when 'BLOCKREMOV'.

    LOOP AT IT_OUTPUT.

    IF ( IT_OUTPUT-CHECK = 'X' AND sy-ucomm = 'BLOCKREMOV' ).

    UPDATE TABLE set LIFSK = ' ' where vbeln = it_output-vbeln

    ENDIF.

    ENDLOOP.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 05:45 PM

    IT_OUTPUT_TMP

    First thing is there is no need to check

    sy-ucomm = 'BLOCKREMOV' in if condition,

    since it has already been done using WHEN 'BLOCKREMOV'.

    <b>1. First option is</b>

    WHEN 'BLOCKREMOV'.

    LOOP AT IT_OUTPUT WHERE CHECK = 'x'.

    UPDATE LIKP SET LIFSK = ' '

    WHERE VBELN = IT_OUTPUT-VBELN.

    ENDLOOP.

    <b>2. Second option</b> is for say if you don't want to hit database for each itab record..that is if u want to update database in single hit..

    data : it_output_tmp type hashed table of likp

    with unique key key1 key2 ...

    WHEN 'BLOCKREMOV'.

    it_output_tmp[] = it_output[].

    delete it_output_tmp where check <> 'X'.

    update likp from table it_output_tmp.

    But in this second option you will have to take little

    care..there few points that u shd take care of

    a. Use this only u have all the key for table

    b. If table record with key in internal table is

    not found, same is inserted in database as

    new record...

    check that you have records with proper

    existing key in internal table it_output

    c. Also it will update all the fields from itab

    work area..so u shd not modify other fields

    else same will updated in database.

    If you are taking care of this points then you can go

    for 2nd option else go for 1st otion..

    Enjoy SAP.

    Pankaj Singh

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      when I executed the report I got 8 records, I Checked 1 of it and pressed Blockremov, it is not going in to the loop.

      Please help.

      when 'BLOCKREMOV'.

      LOOP AT IT_OUTPUT WHERE CHECK = 'X'.

      UPDATE LIKP SET LIFSK = ' '

      WHERE VBELN = IT_OUTPUT-VBELN.

      ENDLOOP.

      sy-lsind = sy-lsind - 1.

      PERFORM DISPLAY_DATA.

      endcase.

      Thanks,

      Veni.

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 06:24 PM

    Hi

    Remember to use FM for updation, instead of direct table update. (We shouldnt do direct update)

    Coming to your problem, check in debug what is happening to the check field of selected row in PAI. Guess, you are loosing the checked record.

    Regards,

    Raj

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      This is my entire program, am I doing anything wrong. Please help.

      Thanks

      Veni.

      REPORT ZSDR_DELIBLOCK
             no standard page heading line-size 255.
      
      TABLES: LIKP.
      
      DATA: Begin of IT_LIKP occurs 0,
            VBELN like LIKP-VBELN,
            LFDAT like LIKP-LFDAT,
            LIFSK LIKE LIKP-LIFSK,
            KUNNR like LIKP-KUNNR,
            KNKLI LIKE LIKP-KNKLI,
            NETWR like LIKP-NETWR,
            End of IT_LIKP.
      
      DATA: Begin of IT_OUTPUT occurs 0,
            CHECK TYPE C,
            VBELN like LIKP-VBELN,
            LFDAT like LIKP-LFDAT,
            LIFSK LIKE LIKP-LIFSK,
            KUNNR like LIKP-KUNNR,
            KNKLI LIKE LIKP-KNKLI,
            NETWR like LIKP-NETWR,
            End of IT_OUTPUT.
      
      SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
      SELECT-OPTIONS: S_LFDAT FOR LIKP-LFDAT,
                      S_KUNNR FOR LIKP-KUNNR.
      PARAMETERS: P_LIFSK TYPE LIKP-LIFSK DEFAULT '01'.
      
      SELECTION-SCREEN END OF BLOCK b1.
      
      at selection-screen output.
      
        loop at screen.
          if screen-name = 'P_LIFSK'.
            screen-input = 0.
            modify screen.
          endif.
        endloop.
      
      start-of-selection.
        PERFORM GET_DATA.
        PERFORM PROCESS_DATA.
        PERFORM DISPLAY_DATA.
      
      at user-command.
        case sy-ucomm.
          when 'SELALL'.
            IT_OUTPUT-CHECK = 'X'.
            MODIFY IT_OUTPUT
            TRANSPORTING CHECK
            WHERE CHECK = ' '.
            sy-lsind = sy-lsind - 1.
            PERFORM DISPLAY_DATA.
      
          when 'DESALL'.
            IT_OUTPUT-CHECK = ' '.
            MODIFY IT_OUTPUT
            TRANSPORTING CHECK
            WHERE CHECK = 'X'.
            sy-lsind = sy-lsind - 1.
            PERFORM DISPLAY_DATA.
      
          when 'BLOCKREMOV'.
            DATA: vbkok LIKE vbkok.
      
            LOOP AT IT_OUTPUT WHERE CHECK = 'X'.
              vbkok-vbeln_vl = it_output-vbeln.
              vbkok-kzlsp = 'X'.
              vbkok-lifsk = ' '.
      
              CALL FUNCTION 'WS_DELIVERY_UPDATE'
                   EXPORTING
                        vbkok_wa = vbkok
                        delivery = it_output-vbeln
                   EXCEPTIONS
                        OTHERS   = 1.
      
              COMMIT WORK.
            ENDLOOP.
            sy-lsind = sy-lsind - 1.
            PERFORM DISPLAY_DATA.
        endcase.
      
      
      FORM get_data.
        SELECT VBELN LFDAT LIFSK KUNNR KNKLI NETWR
               FROM LIKP
               INTO TABLE IT_LIKP
               WHERE LFDAT IN S_LFDAT
                  AND KUNNR IN S_KUNNR
                  AND LIFSK = P_LIFSK.
      ENDFORM.                    " get_data
      
      
      FORM process_data.
        loop at it_LIKP.
          if sy-subrc = 0.
            move-corresponding it_LIKP to it_output.
            append it_output.
            clear it_output.
          endif.
        endloop.
      ENDFORM.                    " process_data
      
      
      
      FORM DISPLAY_DATA.
      
        format color col_heading intensified on.
        write:    /2 'Report ID  :',
                  14 sy-repid,
                  72 text-ttl,
                  133 'Page:', '    ',
                  sy-pagno left-justified.
        write:    /2 text-h12,
                  14 sy-tcode,
                  133 'Date:', sy-datum, ''.
        write:    /133 'Time:', sy-uzeit, '  ',
                  73 'As Of:',
                  80 sy-datum.
        new-line.
        uline.
        format color col_heading intensified off.
      
        format color col_group intensified on.
        set pf-status 'ZSTAT01'.
        write:/1 'Chk',
                 5 'Delivery',
                 18 'Delivery date',
                 36 'Deli Block',
                 48 'Ship-to party',
                 65 'Cred. acct',
                 86 'Net value      '.
        uline.
        format color col_group intensified off.
      
        loop at it_output.
          write:/1 it_output-CHECK as checkbox,
                 5  it_output-VBELN,
                20 it_output-LFDAT,
                39 it_output-LIFSK,
                51 it_output-KUNNR,
                67 it_output-KNKLI,
                80 it_output-NETWR.
        endloop.
        uline.
      ENDFORM.                    " DISPLAY_DATA
      

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 09:30 PM

    Veni - directly updating SAP tables is playing with fire. When Standard SAP functions update the tables, normally other tables are updated as well. You would be far better off to consider one of these:

    BAPI_INB_DELIVERY_CHANGE       Change inbound delivery             
    BAPI_OUTB_DELIVERY_CHANGE      BAPI for Change to Outbound Delivery
    

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 05:29 PM

    Hi,

    Use the FM WS_DELIVERY_UPDATE to remove the delivery block instead of direct table update..

    DATA: vbkok LIKE vbkok.

    vbkok-vbeln_vl = p_vbeln.

    vbkok-kzlsp = 'X'.

    vbkok-lifsk = ' '.

    CALL FUNCTION 'WS_DELIVERY_UPDATE'

    EXPORTING

    vbkok_wa = vbkok

    delivery = p_vbeln

    EXCEPTIONS

    OTHERS = 1.

    COMMIT WORK.

    Thanks,

    Naren

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 06:12 PM

    Hi

    Check in debug if there are any records with CHECK = 'X'.

    You can directly loop only on those records where CHECK - 'X'. and update table.

    Regards,

    Raj

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 06:32 PM

    Hi

    Do you find the 'X' in the CHECK field of the selected rows of internal table? Is it a Char type field of length 1?

    Strange that you say, there is an 'X', but control doesnt come into loop body...how come this can happen....

    Regards,

    Raj

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 06:54 PM

    Hi,

    Check your modified code..

    REPORT zsdr_deliblock

    NO STANDARD PAGE HEADING LINE-SIZE 255.

    TABLES: likp.

    DATA: BEGIN OF it_likp OCCURS 0,

    vbeln LIKE likp-vbeln,

    lfdat LIKE likp-lfdat,

    lifsk LIKE likp-lifsk,

    kunnr LIKE likp-kunnr,

    knkli LIKE likp-knkli,

    netwr LIKE likp-netwr,

    END OF it_likp.

    DATA: BEGIN OF it_output OCCURS 0,

    check TYPE c,

    vbeln LIKE likp-vbeln,

    lfdat LIKE likp-lfdat,

    lifsk LIKE likp-lifsk,

    kunnr LIKE likp-kunnr,

    knkli LIKE likp-knkli,

    netwr LIKE likp-netwr,

    END OF it_output.

    SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

    SELECT-OPTIONS: s_lfdat FOR likp-lfdat,

    s_kunnr FOR likp-kunnr.

    PARAMETERS: p_lifsk TYPE likp-lifsk DEFAULT '01'.

    SELECTION-SCREEN END OF BLOCK b1.

    AT SELECTION-SCREEN OUTPUT.

    LOOP AT SCREEN.

    IF screen-name = 'P_LIFSK'.

    screen-input = 0.

    MODIFY SCREEN.

    ENDIF.

    ENDLOOP.

    START-OF-SELECTION.

    PERFORM get_data.

    PERFORM process_data.

    PERFORM display_data.

    AT USER-COMMAND.

    DATA: v_index TYPE syindex.

    CASE sy-ucomm.

    WHEN 'SELALL'.

    DESCRIBE TABLE it_output.

    DO sy-tfill TIMES.

    v_index = sy-index + 6.

    MODIFY LINE v_index FIELD VALUE it_output-check FROM 'X'.

    IF sy-subrc <> 0.

    EXIT.

    ENDIF.

    ENDDO.

    WHEN 'DESALL'.

    DESCRIBE TABLE it_output.

    DO sy-tfill TIMES.

    v_index = sy-index + 6.

    MODIFY LINE v_index FIELD VALUE it_output-check FROM ' '.

    IF sy-subrc <> 0.

    EXIT.

    ENDIF.

    ENDDO.

    WHEN 'BLOCKREMOV'.

    DATA: vbkok LIKE vbkok.

    DATA: v_check LIKE it_output-check.

    DATA: v_vbeln LIKE it_output-vbeln.

    DESCRIBE TABLE it_output.

    DO sy-tfill TIMES.

    CLEAR: v_check,

    v_vbeln.

    v_index = sy-index + 6.

    READ LINE v_index FIELD VALUE it_output-check INTO v_check

    it_output-vbeln INTO v_vbeln.

    IF sy-subrc <> 0.

    EXIT.

    ENDIF.

    CHECK v_check = 'X'.

    vbkok-vbeln_vl = v_vbeln.

    CALL FUNCTION 'WS_DELIVERY_UPDATE'

    EXPORTING

    vbkok_wa = vbkok

    delivery = v_vbeln

    EXCEPTIONS

    OTHERS = 1.

    COMMIT WORK.

    ENDDO.

    ENDCASE.

    ----


    • FORM get_data *

    ----


    • ........ *

    ----


    FORM get_data.

    SELECT vbeln lfdat lifsk kunnr knkli netwr

    FROM likp

    INTO TABLE it_likp

    WHERE lfdat IN s_lfdat

    AND kunnr IN s_kunnr

    AND lifsk = p_lifsk.

    ENDFORM. " get_data

    ----


    • FORM process_data *

    ----


    • ........ *

    ----


    FORM process_data.

    LOOP AT it_likp.

    IF sy-subrc = 0.

    MOVE-CORRESPONDING it_likp TO it_output.

    APPEND it_output.

    CLEAR it_output.

    ENDIF.

    ENDLOOP.

    ENDFORM. " process_data

    ----


    • FORM DISPLAY_DATA *

    ----


    • ........ *

    ----


    FORM display_data.

    FORMAT COLOR COL_HEADING INTENSIFIED ON.

    WRITE: /2 'Report ID :',

    14 sy-repid,

    72 text-ttl,

    133 'Page:', ' ',

    sy-pagno LEFT-JUSTIFIED.

    WRITE: /2 text-h12,

    14 sy-tcode,

    133 'Date:', sy-datum, ''.

    WRITE: /133 'Time:', sy-uzeit, ' ',

    73 'As Of:',

    80 sy-datum.

    NEW-LINE.

    ULINE.

    FORMAT COLOR COL_HEADING INTENSIFIED OFF.

    FORMAT COLOR COL_GROUP INTENSIFIED ON.

    SET PF-STATUS 'ZSTAT01'.

    WRITE:/1 'Chk',

    5 'Delivery',

    18 'Delivery date',

    36 'Deli Block',

    48 'Ship-to party',

    65 'Cred. acct',

    86 'Net value '.

    ULINE.

    FORMAT COLOR COL_GROUP INTENSIFIED OFF.

    LOOP AT it_output.

    WRITE:/1 it_output-check AS CHECKBOX,

    5 it_output-vbeln,

    20 it_output-lfdat,

    39 it_output-lifsk,

    51 it_output-kunnr,

    67 it_output-knkli,

    80 it_output-netwr.

    ENDLOOP.

    ULINE.

    ENDFORM. " DISPLAY_DATA

    Thanks,

    Naren

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2006 at 08:34 PM

    Hi,

    VBKOK is already declared..

    DATA: vbkok LIKE vbkok.

    The code which I gave is working fine for me..Did you add any additional code??

    Thanks,

    Naren

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Naren,

      No, I copied the same code. When I execute it_output is comming up with 8 records and I checked 1 record and pressed Blockremov then it said ' Delivery 50614030 does not exist.' I went in to the table and checked it, the delivery number is there in it.

      Then for samething I debuged it, then it said'specified table name not recognized', at line CALL FUNCTION 'WS_DELIVERY_UPDATE'.

      Thanks

      Veni.

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.