topic Re: sub string in select in Application Development and Automation Discussions

sub string in select

Former Member — Mon, 02 Feb 2009 11:48:14 GMT

guys i want to fire this query by " AND b~material(4) = 'GH82'." gives error.Syntax not accepted

It gives error " b~material(4)" unknown.

Please do guide

SELECT a_{company a}customer a_{bill_no a}iris_repai

a_{svc_prod a}serial_no a_{crt_serial a}serialtype

a_{repair_rcv a}rcv_dt a_{comp_dt a}dlvry_dt

b~material

INTO CORRESPONDING FIELDS OF TABLE itab

FROM zest0 AS a INNER JOIN zest1 AS b

ON a_{customer = b}customer and

a_{company = b}company

WHERE a~company = 'C750'

AND a~svc_prod = 'HHP01'

AND a~iris_repai = 'A04'

AND b~material(4) = 'GH82'.

Re: sub string in select

Former Member — Mon, 02 Feb 2009 11:50:17 GMT

Hi.

I think b~matr(4) is not acceptable.

just give b~matreial = 'GH82'..

Edited by: tahir naqqash on Feb 2, 2009 4:50 PM

Re: sub string in select

GauthamV — Mon, 02 Feb 2009 11:50:28 GMT

use this.


SELECT a~company a~customer a~bill_no a~iris_repai
a~svc_prod a~serial_no a~crt_serial a~serialtype
a~repair_rcv a~rcv_dt a~comp_dt a~dlvry_dt
b~material
INTO CORRESPONDING FIELDS OF TABLE itab
FROM zest0 AS a INNER JOIN zest1 AS b
ON a~customer = b~customer and
a~company = b~company
WHERE a~company = 'C750'
AND a~svc_prod = 'HHP01'
AND a~iris_repai = 'A04'
AND b~material = 'GH82%'.

Re: sub string in select

former_member784222 — Mon, 02 Feb 2009 12:18:36 GMT

Hi,

Better select all the records into internal table and then delete those records which does not have a pattern 'GH82*'.

regards,

S. Chandramouli.

Re: sub string in select

SuhaSaha — Mon, 02 Feb 2009 12:21:58 GMT

Hello,

SAP does not understand

b~material(4) = 'GH82'

so the issue.

Please modify the code as follows:


SELECT a~company a~customer a~bill_no a~iris_repai
a~svc_prod a~serial_no a~crt_serial a~serialtype
a~repair_rcv a~rcv_dt a~comp_dt a~dlvry_dt
b~material
INTO CORRESPONDING FIELDS OF TABLE itab
FROM zest0 AS a INNER JOIN zest1 AS b
ON a~customer = b~customer and
a~company = b~company
WHERE a~company = 'C750'
AND a~svc_prod = 'HHP01'
AND a~iris_repai = 'A04'
AND b~material LIKE 'GH82%'

BR,

Suhas

Edited by: Suhas Saha on Feb 2, 2009 5:53 PM

Re: sub string in select

faisalatsap — Mon, 02 Feb 2009 12:23:11 GMT

Hi,

Try use this b~material+0(4) = 'GH82' may solve out your problem.

Kind Regards,

Faisal

Edited by: Faisal Altaf on Feb 2, 2009 5:24 PM

Re: sub string in select

Former Member — Mon, 02 Feb 2009 12:24:35 GMT

Hi ,

Use AND b~material like 'GH82%'.

Regards,

Himanshu Verma

Re: sub string in select

Former Member — Mon, 02 Feb 2009 12:31:13 GMT

Hi Alka,

Try it this way: b~material LIKE 'GH82%'.

SELECT a~company a~customer a~bill_no a~iris_repai
a~svc_prod a~serial_no a~crt_serial a~serialtype
a~repair_rcv a~rcv_dt a~comp_dt a~dlvry_dt
b~material
INTO CORRESPONDING FIELDS OF TABLE itab
FROM zest0 AS a INNER JOIN zest1 AS b
ON a~customer = b~customer and
a~company = b~company
WHERE a~company = 'C750'
AND a~svc_prod = 'HHP01'
AND a~iris_repai = 'A04'
AND b~material LIKE 'GH82%'.

With luck,

Pritam.

Edited by: Pritam Ghosh on Feb 2, 2009 1:33 PM

Re: sub string in select

faisalatsap — Mon, 02 Feb 2009 12:33:38 GMT

Hi,

Sorry, According to following Sample Code.

DATA: it_kna1 LIKE STANDARD TABLE OF kna1 WITH HEADER LINE.

SELECT * FROM kna1
  INTO CORRESPONDING FIELDS OF TABLE it_kna1
  WHERE sortl like 'MRS%'. " Do this sortl like 'MRS%' Not this sortl+0(4) =  'MRS' it is not working

so you can't use sortl(4) or sortl+0(4) in select .

Kind Regards,

Faisal

Re: sub string in select

former_member222860 — Mon, 02 Feb 2009 12:45:10 GMT

Hey Alka,

Use Substring in the where-cluase,, this works in SQL, but i'm not sure here.

SELECT a_{company a}customer a_{bill_no a}iris_repai

a_{svc_prod a}serial_no a_{crt_serial a}serialtype

a_{repair_rcv a}rcv_dt a_{comp_dt a}dlvry_dt

b~material

INTO CORRESPONDING FIELDS OF TABLE itab

FROM zest0 AS a INNER JOIN zest1 AS b

ON a_{customer = b}customer and

a_{company = b}company

WHERE a~company = 'C750'

AND a~svc_prod = 'HHP01'

AND a~iris_repai = 'A04'

AND b~material LIKE substr('GB123',1,4).

It works, If you can execute the above query using Native Sql, as i think SubStr() function is not available in ABAP.

thanks\

Mahesh

Edited by: Mahesh Reddy on Feb 2, 2009 1:45 PM

Edited by: Mahesh Reddy on Feb 3, 2009 4:48 AM

Re: sub string in select

Former Member — Mon, 03 Oct 2016 07:07:10 GMT

Hello,

I got it with new open SQL Syntax avalaible from enhancement 740.

For example:

Table ZHR_EXAMPLE_TABLE2 has the column ID_EMPLEADO with 6 characters instead of 8 like PERNR in table ZHR_EXAMPLE_TABLE1.

SELECT

T0~COLUMN1, T0~COLUMN2, T0~COLUMN3, T0~COLUM4, T1~COLUMN1 AS COLUMRESULT, T1~PERNR

FROM ZHR_EXAMPLE_TABLE1 AS T0

INNER JOIN ZHR_EXAMPLE_TABLE2 AS T1 ON SUBSTRING( T1~PERNR,3,6 ) = T0~ID_EMPLEADO

WHERE T0~IDPARTE = @ZIDPARTE AND T1~BEGDA LE T0~FECHA AND T1~ENDDA GE T0~FECHA

INTO CORRESPONDING FIELDS OF TABLE @ITAB.

IF SY-SUBRC EQ 0.

ENDIF.

Instead of "INTO CORRESPONDING FIELDS OF TABLE @ITAB" you can use "INTO TABLE @DATA(result)". With result no declarated before, the result of the select will be in the that table.

I hope it can be useful for someone.

Kind regards,

Julian.