Re: TRIM bug

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