topic Re: UPDATE only INITIAL fields in Table... Anyone know how? in Application Development and Automation Discussions

UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Tue, 05 Jun 2012 15:19:49 GMT

Hello.

Here's my program. (segment)

SELECT REQUEST_NUM FROM ZREQ_ITGOV

INTO pREQ-REQUEST_NUM

WHERE REQUEST_NUM = lREQ-REQUEST_NUM.

ENDSELECT.

IF pREQ-REQUEST_NUM = lREQ-REQUEST_NUM.

UPDATE ZREQ_ITGOV from lREQ.

(In this part I want to add more... I need to specify to update the initial fields only...)

I do not want the existing fields in the table overwritten when I run the method.

I do not want to specify exactly which fields to update because there are a TON!!

So.... is there a way to specify updating only the initial fields within that table?

Otherwise I need to use Where (and list each field) IS INITIAL - correct? - will be testing while waiting for answers...

THANKS

<< Moderator message - please do not offer points >>

Message was edited by: Rob Burbank

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Tue, 05 Jun 2012 15:52:51 GMT

Hi,

I don't think there is anything in ABAP SQL where you can update only intial fields or something like that.

But this is possible in native SQL.

Please check the beow link:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/619dbb5d-08bc-4bf7-8bdf-9bc473dadf66

The native SQL query needs to be written inside EXEC SQL and END EXEC in ABAP to work.

Else the good old loop and check is initial .

Please let me know how it goes!!!

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Tue, 05 Jun 2012 16:21:49 GMT

Try this:

SELECT SINGLE * FROM ZREQ_ITGOV

INTO PREQ

WHERE REQUEST_NUM = lREQ-REQUEST_NUM.

ENDSELECT

ASSIGN preq TO <wa>.

ASSIGN ireq TO <target>.

DO.

ASSIGN COMPONENT sy-index OF STRUCTURE <wa> TO <field>.

IF sy-subrc <> 0.

exit.

ENDIF.

ASSIGN COMPONENT sy-index OF STRUCTURE <target> TO <field1>.

IF sy-subrc = 0.

IF <field> IS INITIAL and <field1> IS NOT initial.

<field1> = <field>. "Overwrite with original data　　

ENDIF.

ENDDO.

UPDATE ZREQ_ITGOV FROM PREQ.

Thanks,

Shambu

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Tue, 05 Jun 2012 16:25:53 GMT

"<field1> = <field>. "Overwrite with original data　　"

but, is it me, or is this statement backward? Simple process, really, surprised that someone would suggest native SQL.

Re: UPDATE only INITIAL fields in Table... Anyone know how?

laurent_fournier2 — Tue, 05 Jun 2012 16:26:08 GMT

Hello,

I believe that there is not an automated way to do it. But, instead writing if statements for every field why don't you try to create a dynamic piece of coding .

One way could be to get the fields of the table ( dd_nametab_get ) and then while looping in each field assigning the table value and check if it it initial or not.

data : fieldname(30).

field symbols : <FS>.

Loop at nametab.

concatenate : 'ZREQ_ITGOV-' nametab-fieldname into fieldname.

assign : ( fieldname ) to <fs> .

check : sy-subrc = 0.

if <fs> is initial.

...

else.

endif.

endloop.

Sorry for the possible errors in coding but it was written very quickly !

Regards.

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Tue, 05 Jun 2012 16:26:19 GMT

I think you should check your logic first. You could be SELECTing a number of rows from the Z table, but only attempting to modify the last one taken.

If at least one record is picked, then the condition

IF pREQ-REQUEST_NUM = lREQ-REQUEST_NUM.

Will always be true.

Rather you should check sy-subrc after the SELECT.

Rob

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Tue, 05 Jun 2012 16:33:12 GMT

Yes, its backward. Thanks Break Point!

SELECT SINGLE * FROM ZREQ_ITGOV

INTO PREQ

WHERE REQUEST_NUM = lREQ-REQUEST_NUM.

ENDSELECT

ASSIGN preq TO <wa>.

ASSIGN ireq TO <target>.

DO.

ASSIGN COMPONENT sy-index OF STRUCTURE <wa> TO <field>.

IF sy-subrc <> 0.

exit.

ENDIF.

ASSIGN COMPONENT sy-index OF STRUCTURE <target> TO <field1>.

IF sy-subrc = 0.

IF <field> IS INITIAL and <field1> IS NOT initial.

<field> = <field1>. "Overwrite with original data

ENDIF.

ENDDO.

UPDATE ZREQ_ITGOV FROM PREQ.

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Tue, 05 Jun 2012 18:28:55 GMT

Thanks all for your great suggestions. There is alot for me to look over and read about and learn.

I ended up doing the following:

Selecting the whole line from the table into the WA where my value equals a given value in the line.

Then if it equals I do 12 individual field conditions to check if the fields ( I care about) are initial or not. If it is not initial I move to another work area where all values are collected.

Then I UPDATE with the second work area and it adds the values to the existing record.

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Wed, 06 Jun 2012 04:54:06 GMT

Hi,

Just out of curiosity regarding your statement "really, surprised that someone would suggest native SQL" should native SQL be avoided? is using native SQL a bad practice?

There are a lot of queries functionalities that can be executed in SQL but not in ABAP SQL like:

ex: Select * from ztable where x = 'true' and field1 = 'value'.

Here x is a variable and the query will fetch data only when x is true. such queries are not possible in ABAP SQL. For such an instance i had to write a complicated if..elseif with different query strategies.

Can you suggest anything for such scenarios? I have come across many people who don't like using native SQL a bit info on why it should be avoided would really help me a lot !!!

my suggestion was solely based on the update query requirement, hence i suggested native SQL rather than a dynamic logic.

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Wed, 06 Jun 2012 13:28:48 GMT

The problem with native SQL is that it is dependent on the database and is trickier to use with internal tables. But if what you want to do is not avaialble in open SQL but can be done in native SQL, go for it.

But bear in mind that if the code you are writing will be used on different databases, you will have to check the type of database and write different code for each.

Rob

Re: UPDATE only INITIAL fields in Table... Anyone know how?

Former Member — Wed, 06 Jun 2012 14:56:28 GMT

Thank you Rob for the information!!!