Sage 100

Trying to link between AR_CustomerDocuments and AR_CustomerDocumentContacts but many of the records have the Document as all upper case in one table and mixed case in the other table, and is therefore missing some of the contact records.  I am trying to use a SQL Command, using the SQL from the Crystal report built on the tables.

FROM   {oj "AR_CustomerDocuments" "AR_CustomerDocuments" LEFT OUTER JOIN "AR_CustomerDocumentContacts" "AR_CustomerDocumentContacts" ON (("AR_CustomerDocuments"."ARDivisionNo"="AR_CustomerDocumentContacts"."ARDivisionNo") AND ("AR_CustomerDocuments"."CustomerNo"="AR_CustomerDocumentContacts"."CustomerNo")) AND ("AR_CustomerDocuments"."Document"="AR_CustomerDocumentContacts"."Document")}

tried changing the last line to:

UPPER(("AR_CustomerDocuments"."Document")=UPPER("AR_CustomerDocumentContacts"."Document"))}

also tried UCS and TOUPPER - same results:  "failed to retrieve data from database"

What is the. correct PVX ODBC syntax for this?

For Paperless settings I've only had success in proper SQL using sub-queries.  For a Crystal Report, from Providex, I'd try sub-reports and shared variables instead of trying to link the tables in the main report.

The correct ProvideX SQL command is UCASE. ChatGPT will tell you UPPER. This can't be put through Crystal as it doesn't push the WHERE clause through the ODBC. It does work via Excel's Power Query. See the example M language syntax below and in the attached Excel example file with your FROM clause.

let
    Source =
        Odbc.Query(
            "dsn=SOTAMAS90",
            "
SELECT *
FROM   {oj ""AR_CustomerDocuments"" ""AR_CustomerDocuments""
        LEFT OUTER JOIN ""AR_CustomerDocumentContacts"" ""AR_CustomerDocumentContacts""
        ON ((""AR_CustomerDocuments"".""ARDivisionNo"" = ""AR_CustomerDocumentContacts"".""ARDivisionNo"")
            AND (""AR_CustomerDocuments"".""CustomerNo"" = ""AR_CustomerDocumentContacts"".""CustomerNo"")
            AND ({fn UCASE(""AR_CustomerDocuments"".""Document"")} =
                 {fn UCASE(""AR_CustomerDocumentContacts"".""Document"")}))}
"
        )
in
    Source

Attachment(s)

Ex_ProvideX_UCASE.xlsx   22 KB 1 version

@Dan Burleson Thanks!   

let    Source =        Odbc.Query(            "dsn=SOTAMAS90",            "SELECT *FROM   {oj ""AR_CustomerDocuments"" ""AR_CustomerDocuments""        LEFT OUTER JOIN ""AR_CustomerDocumentContacts"" ""AR_CustomerDocumentContacts""        ON ((""AR_CustomerDocuments"".""ARDivisionNo"" = ""AR_CustomerDocumentContacts"".""ARDivisionNo"")            AND (""AR_CustomerDocuments"".""CustomerNo"" = ""AR_CustomerDocumentContacts"".""CustomerNo"")            AND ({fn UCASE(""AR_CustomerDocuments"".""Document"")} =                 {fn UCASE(""AR_CustomerDocumentContacts"".""Document"")}))}"        )in    Source