ODBC PASSTHROUGH QUERY / SQL HELP.

[maxxev]

Hi, we have a query running out of Navision into access, this query was setup by someone on site who is presently on holiday....
A shortcut was taken to fix one issue but it has caused another, so in his absence i'm trying to fix the problem.

We have a field being cut down to 3 characters using:

Left([table].[field],3) as ING

The problem is if that field begins with "X" we need it to NOT be cut down to 3 characters, using an IIF code I could have fixed this, but I understand the SQL for ODBC will not accept IIF code (after trying for a couple of hours ).

Can anyone please suggest an alternative option (I tried some "CASE, THEN, ELSE" code but that didn't work either (might have just been me though). I am ok at databases i'm not so great at SQL.

Thank you.

 

[Olaf Doschke]

SQL Passthrough should work with the database dialect, so that should do it in T-SQL:

SELECT ...
CASE 
   WHEN Left([table].[field],1)='X' 
   THEN [table].[field] 
   ELSE Left([table].[field],3) 
END as ING
FROM [table] ...

Bye, Olaf.

 

[maxxev]

Hi thank you for the suggestion, very similar to the code I tried before (exceopt I missed the ,1 from the when line).

This brings up the following error:

ODBC--Call failed
[microsoft][ODBC SQL Server Driver][SQL Server]Incorrect Syntax near 'Table'. (#102)

 

[maxxev]

I have sorted it out now me being daft (I left the comma off the end (there is another field below)).

All working now.

Thank you very much for the help.