ODBC data source doesn't like my SQL query

Good morning all,

I have created this query in SQL Server 2005 which uses an MS SQL Server data source - I've tested it and it perfroms as it should:

SELECT DISTINCT
WIP.R0 AS [Job No], WIP_R23.R0 AS [Pack No], PlanningM2.R1 AS [Part No], WIP.R17 AS FKF, WIP2.R17 AS FKF2, SUBSTRING(PlanningM2.R0, 1, 3)
AS Ref, PlanningM2.R5 AS Qty, LTRIM(RTRIM(WIP_R23.R23)) AS Shortages, LEN(PlanningM2.R1) AS StrLen, Stock.R2 AS [Stock Qty],
WIP.R10 AS Status, WIP2.R10 AS [Pack Status], WIP.R15 AS [Qty UC]
FROM C001_UNIDATA_WIP_NF AS WIP INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM ON WIP.R0 = PlanningM.ASSY INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM2 ON PlanningM.R1 = PlanningM2.ASSY INNER JOIN
C001_UNIDATA_WIP_R23 AS WIP_R23 ON PlanningM.R1 = WIP_R23.R0 INNER JOIN
C001_UNIDATA_WIP_NF AS WIP2 ON WIP_R23.R0 = WIP2.R0 INNER JOIN
C001_UNIDATA_STOCK_NF AS Stock ON PlanningM2.R1 = Stock.R0
WHERE (WIP_R23.R23 IS NULL) AND (WIP.R0 LIKE N'%' + @.RP1 + N'%') OR
(WIP.R0 LIKE N'%' + @.RP1 + N'%') AND (PlanningM2.R1 = RIGHT(LTRIM(RTRIM(WIP_R23.R23)), LEN(PlanningM2.R1)))
ORDER BY [Job No], [Pack No]

I am now trying to recreate this query using ODBC and am having one helluva problem with it as it doesn't seem to like many of the functions I've used, such as LTRIM, RTRIM, LEN, RIGHT and won't accept the parameter I've included, or any other parameter come to think of it.

Has anyone else had a similar problem at all, and could you direct me to a solution if you have?

Thanks in advance,

Chris

you could use a table function to execute your query and get results from it...

Select * From MyTableFunction(<parameters>)

This should work...

|||You'd be better off building this into a stored procedure and executing the procedure via your ODBC connection.|||

Check the ODBC driver documentation for the canonical functions. You need to use those instead of data source specific functions to run the same SQL statement using different drivers. This also applies to the SQL grammar. Of course, this depends on the capability of the driver and the level of implementation. For example, below are some of the mappings:

LEN() -> {fn LENGTH(<col_or_expr>)}

LTRIM -> {fn LTRIM(<col_or_expr>)}

RTRIM -> {fn RTRIM(<col_or_expr>)}

|||

Yes the ODBC driver is very limited in what it allows you to do.

Thanks for the nod in the right direction.