topic Re: select stmt to update database table in Application Development and Automation Discussions

select stmt to update database table

Former Member — Mon, 23 Oct 2006 16:56:09 GMT

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.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 17:04:24 GMT

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.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 17:29:21 GMT

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

Re: select stmt to update database table

messier31 — Mon, 23 Oct 2006 17:45:31 GMT

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

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:06:11 GMT

Hi,

when 'BLOCKREMOV'.

LOOP AT IT_OUTPUT.

IF IT_OUTPUT-CHECK = 'X'.

UPDATE LIKP SET LIFSK = ' '

WHERE VBELN = IT_OUTPUT-VBELN.

ENDIF.

ENDLOOP.

sy-lsind = sy-lsind - 1.

PERFORM DISPLAY_DATA.

endcase.

I did this and when I debug, it is not going in to IF condition. Please help me resolve this.

Thanks

Veni.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:12:00 GMT

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

Re: select stmt to update database table

messier31 — Mon, 23 Oct 2006 18:14:39 GMT

The only reason why it is not going in if is

it_output-check does'nt contain 'X'.

can you please debug and check what value is getting populated in it_output-check..

Also just look in database also chk whether it is updating any...

let us know status on this...

Enjoy SAP.

Pankaj Singh.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:18:22 GMT

Please use Function Module - WS_DELIVERY_UPDATE - any direct table updates to a core table, specifically, will put your database's referential integrity AT GREAT RISK.

Use the SAP FM above to make sure that the DB is not damaged.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:18:32 GMT

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.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:24:19 GMT

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

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:26:05 GMT

Hi,

I did this, but still not going in to loop stmt.

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.

Thanks,

Veni.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:30:27 GMT

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

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:32:03 GMT

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

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:37:36 GMT

Hi Raj,

It is a char type field of length 1. when I select 1 of the records, there is a Right symbol in it.

Thanks,

Veni.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:43:11 GMT

Hi,

In the output, I noticed on the tool bar next to Tcode window, the save, left arrow, exit and cacel buttons are deactivated.

Am I doing anything wrong.

Thanks

Veni.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 18:54:01 GMT

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

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 20:32:21 GMT

Hi Naren,

Now it is saying 'Specified table not recognized'. While assigning do I have to assign vbkok to different table.

Thanks

Veni.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 20:34:59 GMT

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

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 20:51:32 GMT

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.

Re: select stmt to update database table

Former Member — Mon, 23 Oct 2006 21:00:10 GMT

Hi,

CHeck the new code for 'BLOCKREMOV'.

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.

Please let me know if you have any questions..

Thanks,

Naren