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: 

how to use substring function in SQL join condition?

former_member625844
Participant
0 Kudos
7,575

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.

7 REPLIES 7

former_member625844
Participant
0 Kudos
1,409

Ok. Look's like

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

work. Can someone tell me why?

MateuszAdamus
Active Contributor
1,409

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

1,409

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

venkateswaran_k
Active Contributor
1,409

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

0 Kudos
1,409

Hi

Is your SQL issue resolved?

0 Kudos
1,409

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
Active Participant
0 Kudos
1,409

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