topic Re: how to use substring function in SQL join condition? in Application Development and Automation Discussions

how to use substring function in SQL join condition?

former_member625844 — Thu, 02 Jul 2020 09:45:52 GMT

I want to write a SQL which join the cosp_bak and csks table. The join condition is last 12 digits of cosp_bak 's objnr equal csks's kostl. So I wrote a SQL like

 select csks~kostl,cosp_bak~kstar into  @data(t_result) from csks left join cosp_bak on csks~kostl = cosp_bak~objnr+10(12).

and it has error

Unknown column name "OBJNR+10(12)". until runtime, you cannot specify a field list.

So what's the correct way to use function in SQL condition?Thx.

Re: how to use substring function in SQL join condition?

former_member625844 — Thu, 02 Jul 2020 10:01:44 GMT

Ok. Look's like

left join cosp_bak on SUBSTRING( cosp_bak~objnr,12,10 ) =  csks~kostl

work. Can someone tell me why?

Re: how to use substring function in SQL join condition?

MateuszAdamus — Thu, 02 Jul 2020 10:15:21 GMT

Hello loki_luo15

OBJNR+10(12) is an ABAP syntax, which means "after first 10 characters, take 12 characters". Where SUBSTRING( cosp_bak~objnr,12,10 ) is an SQL function which DB can understand and execute and in your example it means "after first 12 characters, take 10 characters".

Since KOSTL is a 10 character field I recon the difference between 12 characters from ABAP and 10 characters from SQL made the difference.

Also, the COSP_BAK is not really an ABAP variable, so the JOIN takes place on DB side only (at least for this condition), hence the SQL SUBSTRING seems to be more in place here.

Kind regards,

Mateusz

Re: how to use substring function in SQL join condition?

venkateswaran_k — Thu, 02 Jul 2020 10:30:30 GMT

You can directly use the objnr from csks table itself.

select csks~kostl,cosp_bak~kstar into@data(t_result)from csks leftjoin cosp_bak on csks~objnr = cosp_bak~objnr.

The CSKS table store the same object number

Re: how to use substring function in SQL join condition?

Sandra_Rossi — Thu, 02 Jul 2020 13:08:12 GMT

More information about dataobject+offset(length) in ABAP documentation: Substring Access

Re: how to use substring function in SQL join condition?

venkateswaran_k — Fri, 03 Jul 2020 18:18:33 GMT

Is your SQL issue resolved?

Re: how to use substring function in SQL join condition?

gasparerdelyi — Fri, 03 Jul 2020 21:11:39 GMT

Because the SUBSTRING( ) is evaluated on the database and the dobj+offset(length) is not correct syntax for SQL, even in OpenSQL.

Re: how to use substring function in SQL join condition?

gasparerdelyi — Fri, 03 Jul 2020 21:15:47 GMT

This is really a good idea because the substring operation in the join condition prevents the optimizer of the database from applying the condition before joining. Also most indices will not help at all with such a substring operation.