10-28-2016 8:24 AM
I want to select from ADRC to find customers who have a particular phone number.
The phone numbers are stored in ADRC with spaces in different places e.g. 012345 or 012 345 or 0123 45 etc.
Is there a way to select from ADRC with the input phone number 012345 and for it to return all customers with 012345 or 012 345 etc.
10-31-2016 8:52 AM
Even better solution : ADR2 has the field TELNR_CALL which stores numbers without spaces. So I can use that!
10-28-2016 9:27 AM
Any possibility with Like operator in Select query .
Else.
Each time move the 012345 to any string and remove the Space and
now check it
If 012345 eq 012345
append the record to the table.
10-28-2016 9:29 AM
10-28-2016 10:06 AM
10-28-2016 9:29 AM
I do not think it's possible to do via a single select (at least not before 740, but i did not used too much 740 so i speak for previous releases).
My suggestion is to select into an internal table KUNNR - Phone Number and then work with the internal table: loop it, condense no gaps the phone number, and the read the table for your give phone number.
I do not think performances are so highly impacted by this approach (not clean but surely works)
10-28-2016 9:41 AM
Use ADBC to issue native SQL to the database with replace - https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/e66a2c75191014bac6a279955db665/content.htm
. E.g. SELECT ... WHERE replace(TEL_NUMBER,' ','') = phone_number_without_spaces
10-28-2016 9:54 AM
10-28-2016 12:06 PM
Not only Hana, some other database, like Oracle for example, support the REPLACE function (also the REGEXP_REPLACE function too) So you could use ABDC classes like CL_SQL_STATEMENT.
From 7.51 (or less?) you can also use the REPLACE function in OPEN-SQL, read ABAP News for Release 7.51 – Case Insensitive Search in SQL and Other New Functions from horst.keller.
Regards,
Raymond
10-28-2016 12:18 PM
Thanks for the explain, Raymond.
My old Sybase with 731 cannot support it, i just tried 😐
10-28-2016 2:55 PM
Thanks everyone for the suggestions.
We have decided to load all customers/phone numbers into a Z table without spaces. A BADI will populate this table when any phone numbers are created or changed.
The retrieval of the customers based on the phone number needs to be fast which is why we have gone down this route.
10-31-2016 8:52 AM
Even better solution : ADR2 has the field TELNR_CALL which stores numbers without spaces. So I can use that!