On 10/06/2016 04:42 PM, James Bellinger wrote:
> Hello,
>
> I just ran into this and narrowed it down to TRIM. Without the TRIM(),
> it works fine.
>
> In MS Access,
> SELECT DISTINCT [Name] FROM [Customer Information] WHERE [Name] IS NOT
> NULL AND LEN(TRIM([Name])) > 1 ORDER BY [Name] ASC;
> becomes
> SELECT DISTINCT "public"."Orders"."Name" FROM ("public"."Orders" LEFT
> OUTER JOIN "public"."POs" ON ("public"."Orders"."OnlineCode" =
> "public"."POs"."OnlineCode" ) ) WHERE (NOT(("public"."Orders"."Name" IS
> NULL ) ) AND (char_length(ltrim(rtrim("public"."Orders"."Name" )))))> 1
> ) ) ORDER BY "public"."Orders"."Name"
> As you can see, there is an extra right parenthesis. (Customer
> Information is an Access query onto Orders, in case the name change is
> confusing from my test example.)
Access version?
psqlobdc version?
Postgres version?
Is this being generated by the Access query builder?
What if you do a Pass-Though query?
Where are fetching the final query text from?
SELECT
DISTINCT "public"."Orders"."Name"
FROM (
"public"."Orders"
LEFT OUTER JOIN "public"."POs" ON (
"public"."Orders"."OnlineCode" = "public"."POs"."OnlineCode" ) )
WHERE (
NOT (
(
"public"."Orders"."Name" IS NULL ) )
AND (
char_length (
ltrim (
rtrim (
"public"."Orders"."Name" ) ) ) ) )
> 1 ) )
ORDER BY
"public"."Orders"."Name"
I count two extra right parentheses.
>
> Thanks!
>
> James
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com