how to use substring function in SQL join condition?

[former_member625844]

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.

[former_member625844]

Ok. Look's like

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

work. Can someone tell me why?

[MateuszAdamus]

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

[Sandra_Rossi]

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

[venkateswaran_k]

Hi

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

[venkateswaran_k]

Hi

Is your SQL issue resolved?

[gasparerdelyi]

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.

[gasparerdelyi]

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