Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Ignore leading zeros in SQL

schmelto
Active Participant

Is there a way to ignore leading zeros in a SQL-Select statement?

For example

SELECT txt50 FROM skat INTO @DATA(output)
	WHERE saknr = '552100'
	AND spras = @sy-langu
	AND ktopl = @h_ktopl.

is not giving back any data. But with

WHERE saknr = '0000552100'

It is working just fine.

Is there a way to ignore those leading zeros when selecting the TXT50 from table SKAT like it is working when putting in the selection in SE16, SE16N or SE16H.

I want to make all adjustments in the select and do not want to change the variable using convertion_exit_alpha...

Another example where this problem occurs for me is:

SELECT bseg~hkont, bseg~zuonr, bseg~belnr, bseg~gjahr, aufk~prctr FROM bseg INNER JOIN aufk
  ON bseg~zuonr = aufk~aufnr "<--
  WHERE bseg~hkont IN @s_hkont
  INTO TABLE @DATA(output).

aufk~aufnr has leading zeros (for example: 000072667023) and bseg~zuonr contains only the number without leading zeros (for example: 72667023) therefore the select is also not giving back any data.

A quick fix for this was to use concat( '0000', bseg~zuonr ) to append leading zeros to the zunor but I think its not really universal and reliabel.

1 ACCEPTED SOLUTION

thkolz
Contributor
lv_saknr = '552100'.

SELECT SINGLE txt50 FROM skat
  INTO @DATA(output)
  WHERE saknr = @( |{ lv_saknr ALPHA = IN }| )
    AND spras = @sy-langu
    AND ktopl = @h_ktopl.
3 REPLIES 3

thkolz
Contributor
lv_saknr = '552100'.

SELECT SINGLE txt50 FROM skat
  INTO @DATA(output)
  WHERE saknr = @( |{ lv_saknr ALPHA = IN }| )
    AND spras = @sy-langu
    AND ktopl = @h_ktopl.

Sandra_Rossi
Active Contributor
0 Kudos

6e829fd780204f76b10f492049a3773c

In case you need more information, see there Open SQL - Host Expressions - ABAP Keyword Documentation (sap.com)

Host Expressions exist since 7.50

Also see Formatting Options of String Templates for |... ALPHA ...|

Sandra_Rossi
Active Contributor

I'm not sure about bseg~zuonr = aufk~aufnr, but maybe it's more universal than what you think. Sometimes, SAP just adds zeroes without special logic.

It's not the same issue as with SAKNR.