Substring in a comparison (ON)

Question

Substring in a comparison (ON)

Navigation

#1 by (1 votes)

0

Friends, I'm doing a SQL query where I have to use only part of the characters in one column and compare it with another to get the matches:

SELECT A.NUMCLIEN,A.CODIDENT,A.CLAIDENT,A.DIGIDENT,C.ENTIOFI,C.CUENTA
FROM GPBC.UGDTMAE C JOIN GPBC.PEDT008 B
--C.CUENTA tiene 10 caracteres, necesito tomar los 8 ultimos, el campo B.NUMECTA tiene 8 caracteres, debo seleccionar los registros que coinciden en estos campos.
ON SUBSTRING(C.CUENTA,3,8)=B.NUMECTA
JOIN GPBC.PEDT001 A ON B.NUMCLIEN=A.NUMCLIEN

The error displayed when executing the query:

QUERY MESSAGES: Argument '3' of scalar function 'SUBSTRING' is invalid.

I have already tried without success with different examples and I have searched in different pages how this query could be corrected. Thanks.

sql join substring tablas

asked by Joel Fernandez 27.10.2018 в 02:18

source

1 answer

Problems with scrollTop for anchors in HTML how to use JList and Un ArrayList java

score 1 · Answer 1

The correction that must be made is simple, but it did make me search a lot. replace the SUBSTRING with SUBSTR. It would look like this:

SELECT A.NUMCLIEN,A.CODIDENT,A.CLAIDENT,A.DIGIDENT,C.ENTIOFI,C.CUENTA
FROM GPBC.UGDTMAE C JOIN GPBC.PEDT008 B
ON SUBSTR(C.CUENTA,3,8)=B.NUMECTA
JOIN GPBC.PEDT001 A ON B.NUMCLIEN=A.NUMCLIEN