CODEWITHSUNDEEP
sqldb2ibm-midrange
Monib
Monib

Reputation: 9

How to fix "RIGHT in T2 type *N not found" in SQL Server reading from AS400?

I am joining two files that reside on AS400 in an SSIS package. I am using ODBC Source and SQL command text field.

The SQL query against each table works well and populates data. Even when I join them, it works fine EXCEPT when the "RIGHT" function is there.

This query works fine:

SQL
SELECT
T1.DOC,
T1.DOCNUM,
T1.REF4
FROM SCHEMA.Table1 AS T1

This one also works fine:

SELECT
T2.YEAR,
T2.PERIOD
FROM SCHEMA.Table2 AS T2

Even this works well:

SELECT
T1.DOC,
T1.DOCNUM,
T1.REF4,
T2.YEAR,
T2.PERIOD
FROM SCHEMA.Table1 AS T1
INNER JOIN SCHEMA.Table2 AS T2
ON T1.DOCNUM=T2.DOCNUM

But when I have the RIGHT function in the query (below), it errors out:

SELECT
T1.DOC,
T1.DOCNUM,
T1.REF4,
T1.RIGHT(REF4, 8) as REF5,
T2.YEAR,
T2.PERIOD
FROM SCHEMA.Table1 AS T1
INNER JOIN SCHEMA.Table2 AS T2
ON T1.DOCNUM=T2.DOCNUM

I expect the query to capture the 8 right characters of REF4, but I get the following error:

Error at Data Flow Task TEST open Database Connectivity (ODBC) error occurred. state: '4292'. Native Error Code: -2N. fl8MISystem i Access ODBC DriverlD82 for i5/OS)SQL0204 - RIGHT in T1 type 'N not found.

Upvotes: 0

Views: 1886

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The correct syntax is:

RIGHT(T1.REF4, 8) as REF5

T1 qualifies the column name, not the function name.

Upvotes: 3

Related Questions