Crystal Reports and CODBC 3.7: linking two tables

SG
SG Member Posts: 3
edited 2005-05-19 in NAV/Navision Classic Client
I'm using Crystal Reports XI with C/ODBC 32 bit driver in version 3.70.00.13164.

When I need data from just one table, everything is fine. When I e.g. try to use the Table Customer and Salesperson/Purchaser to show the name of the salesperson for every customer, I get an error.
I linked the tables by the Crystal Reports Database Expert with Customer.Salesperson Code left outer join on Salesperson/Purchaser.Code


The Error:


Failed to retrieve data from the database
Details: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft Business Solutions ApS] [Microsoft Business Solutions -Navision ODBC Driver]Expected lexical element not found: <identifier>
SQL State: 37000
Native Error: 1015 [Database Vendor Code: 1015]


Just to test, I connected to the Database by MS Access through two linked tables (data still in the Navision DB). Then I used this two linked tables from Crystal Reports with the Access database the same way I tried it "direct" with the C/ODBC 32 bit driver. Everything worked fine :?:

Is the implementation of the C/ODBC driver poorly - or did I somthing wrong? Do I have to change some connection settings?

I also tried it with the MS Navision 4.0 ODBC, but it refused the connection to a 3.7 database :(

](*,) I'm out of ideas now. Can anyone help?

Comments

  • Savatage
    Savatage Member Posts: 7,142
    as far as i remember c\odbc does not support left outter joins. unless this has been fixed?

    are you using SQL or Native Database
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • SG
    SG Member Posts: 3
    I'm using the native database and tried the inner join as well. Same problem - only the description and the native error code are a little different:

    Description: [Microsoft Business Solutions ApS] [Microsoft Business Solutions -Navision ODBC Driver]Unexpected extra token: INNER
    Native Error: 1017 [Database Vendor Code: 1017]

    Is there a list of the Native Error Codes for C/ODBC or native Navision DB?


    When I change the data source of the same report to the Access DB with the tables linked to the Navision DB, everythin works.
    It's also no problem if I use a Left (Outer) Join or a Inner Join Query direct in Access.

    I agree that c/odbc doesn't support joins. The only answer, why I get results by MS Access you can see in the ODBC logs :evil: :

    Direct with queries from Access:

    INNER JOIN
      "SELECT "Customer"."Nr_","Salesperson/Purchaser"."Code" FROM "Customer","Salesperson/Purchaser" WHERE (("Customer"."Nr_" = '10000' ) AND ("Customer"."Salesperson Code" = "Salesperson/Purchaser"."Code" ) ) \ 0"
      LEFT OUTER JOIN
        "SELECT "Salesperson Code" ,"Customer"."Nr_" FROM "Customer" WHERE ("Nr_" = '10000' ) \ 0"

        "SELECT "Code" ,"Salesperson/Purchaser"."Code" FROM "Salesperson/Purchaser" WHERE ("Code" = ?)\ 0"

        "SELECT "Code","Name" FROM "Salesperson/Purchaser" WHERE "Code" = ? OR "Code" = ? OR "Code" = ? OR "Code" = ? OR "Code" = ? OR "Code" = ? OR "Code" = ? OR "Code" = ? OR "Code" = ? OR "Code" = ?\ 0"        It looks like Access fetches the data of both tables and merges them internal. The log for the Crystal Reports queries over Access looks similar and do also works. The following lines are from the log when I tried to access the Navision DB direct from Crystal Reports without the Access Links:

        INNER JOIN
          " SELECT "Customer"."Name", "Salesperson/Purchaser"."Name", "Customer"."Nr_" FROM "Customer" "Customer" INNER JOIN "Salesperson/Purchaser" "Salesperson/Purchaser" ON "Customer"."Verkaufercode"="Salesperson/Purchaser"."Code" WHERE "Customer"."Nr_"='10000'"
          LEFT OUTER JOIN
            " SELECT "Customer"."Name", "Salesperson/Purchaser"."Name", "Customer"."Nr_" FROM {oj "Customer" "Customer" LEFT OUTER JOIN "Salesperson/Purchaser" "Salesperson/Purchaser" ON "Customer"."Verkaufercode"="Salesperson/Purchaser"."Code"} WHERE "Customer"."Nr_"='10000'"

            In the end I'm forced to go a long way round with MS Access if I use C/ODBC driver from Navision. I won't call this a "solution" [-(
            Does anyone know a smart driver (or solution) that I can use instead of C/ODBC and MS Access? [-o<
          • Savatage
            Savatage Member Posts: 7,142
            I use Crystal 9 so maybe it's different.

            But here are my steps...
            1) New Report
            2)Select Blank Report
            3) Select ODBC (RDO)
            4) There I see My Source Data Name I Have Created "My Data"
            5)Select My Tables I want to Use
            6)Click on the link Tab -> then Clear Links (Cause their always wrong)
            7)link two of the fields
            click OK & it's ready to go.

            I've never had a problem linking two related tables. I Have also never needed to change the link options. I leave it as default - no problems.

            Once in a while it's good to check the database in case it's been updated

            Database->Verify Database
            Harry Ruiz
            http://www.BiloBeauty.com
            http://www.autismspeaks.org
          • FerryMan
            FerryMan Member Posts: 1
            I had the same issue with CR XI and C/ODBC drivers with Navision 3.7

            The drivers worked fine with MS Access 2003 so I ended up creating an Access database with linked tables to the Navision instance and then pointing Crystal reports at the MS Access database.. worked a treat.

            totally nuts solution I know.. but I couldn't find any other way around it either.

            hope that helps

            :mrgreen:

          Categories