2023 May 25 8:51 PM
Hello friends,
I came across a weird behavior when deleting a record from a ztable.. Let me give you a background of the scenario:
I have a ztable, that has 4 fields: mandt, orderid, delivery and log. During development I update the table and delete record when needed using the statement below:
Delete from ztable1 where orderid = 0001234567.
This worked for me for a day, now I received a new requirement that asks to delete delivery field from ztable.
After deleting the entry, I tested again and to my surprise it doesn't delete the record anymore...
I changed the statement to the one below and was able to delete again:
Delete from ztable1 where orderid = '1234567'.
I can't understand whats happening so I decided to create a new z table, ztable2 with the same fields as ztable1 but I'm able to delete in this table using the first statement without single quote:
Delete from ztable2 where orderid = 0001234567.
I attached a screenshot of the fields of the tables (exactly the same) the only difference which I guess does not matter is the package ztab1 using dev package while ztab2 is set to just local.
Hope you can enlighten me.
Geoff
2023 May 25 9:19 PM
Hi,
After deleting the DELIVERY field from the table, which was probably a key field, you should adjust the table (in Utilities menu, under Database Object, select Database Utility and click on Activate and Adjust table).
Domain VBELN (which is used by Data Element VBELN_VA) has a conversion routine of type ALPHA. This means it can contain letters and numbers, but if it contains numbers, it should be preceded by zeros. You should not read without single quotes as you are comparing a text field to a number, which would imply a conversion. It should always be '0001234567'. If necessary, call Function CONVERSION_EXIT_ALPHA_INPUT to convert to internal format, or CONVERSION_EXIT_ALPHA_OUTPUT to convert back to external format for display (or use ALPHA if you have ABAP 7.4 or higher).
Depending on the way you filled the table, you might have entries of the type 1234567 and others with the type 0001234567. These would be two different entries. You should do what is necessary to always save it with leading zeros. Check the internal values of entries by double clicking on the entry in SE16N and if you have entries without leading zeros, you should delete them and recreate them properly.
I hope this helps.
Patrice Poirier
2023 May 25 9:19 PM
Hi,
After deleting the DELIVERY field from the table, which was probably a key field, you should adjust the table (in Utilities menu, under Database Object, select Database Utility and click on Activate and Adjust table).
Domain VBELN (which is used by Data Element VBELN_VA) has a conversion routine of type ALPHA. This means it can contain letters and numbers, but if it contains numbers, it should be preceded by zeros. You should not read without single quotes as you are comparing a text field to a number, which would imply a conversion. It should always be '0001234567'. If necessary, call Function CONVERSION_EXIT_ALPHA_INPUT to convert to internal format, or CONVERSION_EXIT_ALPHA_OUTPUT to convert back to external format for display (or use ALPHA if you have ABAP 7.4 or higher).
Depending on the way you filled the table, you might have entries of the type 1234567 and others with the type 0001234567. These would be two different entries. You should do what is necessary to always save it with leading zeros. Check the internal values of entries by double clicking on the entry in SE16N and if you have entries without leading zeros, you should delete them and recreate them properly.
I hope this helps.
Patrice Poirier
2023 May 26 8:15 PM
2023 May 26 8:36 PM
Yes, ok. Well, what did you try exactly? Because reading this, it really looks like a case of conversion exit problem, where you created the entry without leading zeros and you are trying to delete with leading zeros. Did you check the value it actually has in SE16N like I suggested? I'm talking about the internal value unconverted (see image attached). What value does it contain? Is it the same as what you are trying to delete? Cause if there is any difference, even of leading zeros, then it won't work.
When one gives a detailed explanation of the way things work, and the only answer one gets is "it doesn't work", one can't help but wonder if the person really tried. If it doesn't work, please explain what you did and what doesn't work after having taken into account the explanation I gave.
2023 May 31 6:53 PM
Hello Patrice,
Upon checking, my ztable doesn't accept leading zeros... but I used the same domain vbeln_va. In se16 when I try to display the data checking user parameter (checkbox) it just contains the same number without leading zeros.
Is there a specific setting where we preserve leading zeros when we create a ztable? I see how I pass the value to my ztable, the source variable passes with 3 leading zeroes but when it gets into ztable, it removes the leading zeros automatically therefore when I select from it, it doesn't have it and when other operations like read from internal table containing this field it can't see the value because of lacking leading zeros..
2023 May 31 7:32 PM
I noticed another behavior, source variable contains 0001600187 then it passed to the ztable executed modify ztable and it reflected in the table as seen in the screenshot:
it doesn't have leading zeroes, when I retrieve data it gives the same value without leading zeroes..
Now, I tried deleting the leading zeroes from the source variable passing only 1600187 and it saved 0187 under unconverted...
this is weird because my program's already complete, i didn't need conversion alpha FM before and I can get the record just fine. Now, I'm surprised when I retrieve value from ztable it doesn't have the leading zeroes every check fails where it used to be all working just fine...
2023 May 31 7:37 PM
Hi Jeff,
Thanks for the response. It is not possible that this field does not accept leading zeros as you are using a standard SAP domain that uses an ALPHA conversion routine. There is only one possible behavior.
The problem is probably that in your tests, you are passing a numeric value (no single quotes) and not characters (with single quotes). As explained by Sandra Rossi, if you pass a number, whether you enter leading zeros does not matter, as it will be converted without leading zeros.
This is a character type field. There is no reason to use numeric values. You should append with '0001234567', with single quotes and it will be created like that. Then you should read or delete with the same value, '0001234567', with single quotes. You can use the Function Modules I have mentioned above to convert the content of your field to and from an internal value (with leading zeros) / external value (without leading zeros).
If for some reason you still don't manage to make it work, please provide the actual code.
Thank you.
Patrice Poirier
2023 May 31 7:55 PM
Thank you Patrice for the feedback..I didn't change anything in the source code reason why I'm so confused...I usually code step by step, testing each segment before adding additional logic and I already passed tests until I encounter this now.
Anyway, if I'll add the FMs, shouldn't the ztable atleast contain leading zeroes (unconverted column) otherwise it will not convert anything since both converted and unconverted shows 1600187... I checked vbak table and it has the leading zeroes.
2023 May 31 8:10 PM
Hi Jeff,
If the table contains an entry without leading zeros both in converted and unconverted, this means it is an incoherent entry. You should normally delete this entry and create it again with leading zeros.
It's important that you understand the concept behind conversion routines in SAP. This is fundamental and you will encounter it all the time. It's not just a question of adding a function call, it's about understanding the concept. There is nothing here that seems incoherent. Your system seem to be behaving in a normal way that is explainable and expected.
At this point I could try to explain it some other way but I think the best way would probably be for you to provide the actual code that gives you a record without leading zeros. But I think you should have all the information needed on this page to help you understand what is happening.
Thank you.
Patrice Poirier
2023 Jun 01 1:08 AM
Thank you Patrice for your feedback. I found the reason how it all started, when I deleted the delivery field from ztable, I used to declare the internal table like:
Initial Declaration used:
Data: itab type standard table of ztab1,
lwtab type ztab1.
Now after I deleted delivery field from ztable, I declared Itab this way:
New Declaration used:
types: begin of ty_ztab,
orderid type vbeln_va,
delivery type vbeln_vl,
log type char50.
types: end of ty_ztab.
Data: itab type standard table of ty_ztab,
lwtab type ty_ztab.
Now the code below works when I use the initial declaration (referenced directly to ztab1) it preserves the leading zeros as passed (checking se16n 2 values appear i.e. 1600187 and unconverted 0001600187, but when I used my own type structure, passed value containing leading zeroes gets deleted. When I view in se16n the ztable contains the value but unconverted shows same without leading zeros - both 1600187
lwtab-orderid = <fs_records>-vbeln. "fs always contain leading zeros -> 0001600187
lwtab-log = c_message.
modify ztab from lwtab.
Note: I used CONVERSION_EXIT_ALPHA_OUTPUT still the new declaration didn't preserve the leading zeroes. Only way leading zeroes are preserved is when I directly reference itab to ztable (please see initial declaration).
Appreciate the time, apologies for the confusion. I'll run some more tests but given the explanation above I hope someone can share if they encountered it already.
2023 Jun 01 3:26 AM
Hi Jeff,
Thanks for the response.
First, CONVERSION_EXIT_ALPHA_OUTPUT is to get the external value (without leading zeros). If you want the internal value (with leading zeros), you need to use CONVERSION_EXIT_ALPHA_INPUT.
But mainly, you are updating a database table (ZTAB) with a structure that doesn't have the same fields. LWTAB is missing field MANDT, and the database, from what you mentionned before, doesn't have field DELIVERY. So your three leading zeros are ending up in MANDT, which are then overwritten by the system client (cause even if you put nothing in MANDT, it'll always end up with the actual client in the database). If you add MANDT at the start of TY_ZTAB, it should solve your issue for the leading zeros.
But you'll still have issues because the structure is different because of field DELIVERY. If you intend to use a structure to update a database table, there really is no reason to use any other type than the table itself.
Hope this helps.
Patrice Poirier
2023 Jun 01 10:19 AM
jeff.broodwar
2023 Jun 01 12:51 PM
2023 Jun 01 1:27 PM
@Patrice - Thank you, helped me realize the structure difference, you're right mandt field caused the issue, as for the delivery it's not a problem cause I don't record delivery in the ztable only itab. I appreciate everyone's help, it's really helpful to talk to other people while doing tests. Appreciate the help.
2023 Jun 01 2:22 PM
Hi Jeff,
I'm glad to hear it solved your problem. Happy to help.
As for DELIVERY, if the structure you are using to update the table is not exactly the same as the table, you'll most likely end up with fields being offset. In your case, DELIVERY would end up in the first 10 characters of LOG, the actual LOG would start at the 11th characters and the last 10 characters of LOG would be lost. In "MODIFY ztab FROM lwtab", if ZTAB and LWTAB don't have the exact same structure, you'll likely have problems.
Thanks.
Patrice Poirier
2023 Jun 01 4:37 PM
2023 May 25 9:24 PM
I guess all the below statements are equivalent:
Delete from ztable2 where orderid = 0001234567. " DB will convert number 1234567 to characters
Delete from ztable2 where orderid = 1234567. " DB will convert number 1234567 to characters
Delete from ztable1 where orderid = '1234567'.
Delete from ztable1 where orderid = '1234567 '. " ABAP ignores trailing spaces with single quotes
Above statements are different from:
Delete from ztable1 where orderid = '0001234567'.
PS: no idea why it doesn't delete anymore (except if you created the line with different orderid value or have dropped/recreated the table during the adjustment...)
2023 May 26 8:35 AM
I cannot see the point. Why you must provide a hardcoded id? It's nonsense. In the real world, you will use a parameter, assigned to the proper data element and you will not face that problem.
But I like to learn new things, so thanks.
2023 May 26 3:20 PM
Vicenç Xavier Lozano : They are showing the content of the variables for explaining purposes. They are not implying that the code would actually contain a harcoded value. It is a discussion about a domain that contains a conversion routine and how it behaves in different situations.
2023 May 26 8:19 PM
@Vicenc Xavier, as Patrice said - It's to show the scenario as clear as possible.
It's nonsense to apply coding standards/real world logic when you're trying to explain and show the scenario through example. Glad you learned something.
2023 May 26 8:20 PM
2023 May 29 8:33 AM
So, if the OP provides a true variable to the WHERE, the data element of the variable will determine the behaviour of the SELECT, so...
2023 May 31 9:16 PM
I just see your comments to Vicenc and to me.
If you want to target someone, if this person has posted an Answer, use the button COMMENT, if this person is the Original Poster of the question he/she will be automatically informed, otherwise copy/paste their hyperlinked name so that the person receives a warning (NB: @ doesn't work/but typing this character will suggest hyperlinked names).
e.g. this is the wrong way: @jeff broodwar
this is the right way: jeff.broodwar
2023 May 31 9:35 PM
I feel that the discussion is extending a lot because for sake of simplicity (to your honor), you are not explaining every step (your exact code, how you pass the values) and so it's very difficult to tell you where exactly is the problem (despite Patrice very good explanations).
Maybe it's useless and now you have understood how conversion routines work, what database contains, what is shown, but just in case here is a summary how conversion routines work, here with example of the ALPHA conversion routine (like for sales order numbers):
Addendum: