none
Data format and joins RRS feed

  • Question

  • Hi.

    One of my BO reports used to import to an Access 2010 database has been redeveloped in our new system and now the fields are exporting from BO with 2 decimal places where before, these weren't present.

    I have imported the report and ran the queries and they appear to work but I just wanted to check if differing formats between tables would affect the joins in anyway or is format ignored?

    Thursday, October 26, 2017 8:51 AM

Answers

  • The different numeric formats will not per se affect the joins.  It's the values which are important, so if one table has non-integer values, i.e either of the decimal places in the formatted value is not zero, then those rows will not be able to map directly to any rows in the other table if the column in question is an integer data type.  It would be necessary to join the tables on INT(TableA.ColumnName) = TableB.ColumnName.

    Ken Sheridan, Stafford, England

    • Marked as answer by fbxiii Thursday, October 26, 2017 11:30 AM
    Thursday, October 26, 2017 10:58 AM

All replies

  • The different numeric formats will not per se affect the joins.  It's the values which are important, so if one table has non-integer values, i.e either of the decimal places in the formatted value is not zero, then those rows will not be able to map directly to any rows in the other table if the column in question is an integer data type.  It would be necessary to join the tables on INT(TableA.ColumnName) = TableB.ColumnName.

    Ken Sheridan, Stafford, England

    • Marked as answer by fbxiii Thursday, October 26, 2017 11:30 AM
    Thursday, October 26, 2017 10:58 AM
  • Hi Ken.

    Yes, the data types and physical values are the same it's just the way it is formatted in the new report.

    I thought it should be OK but just wanted to check before I release the new report to the team.

    Thanks :)

    Thursday, October 26, 2017 11:18 AM