July 12, 2010 at 12:57 pm
I'm tring to join several tables together in a case-insensitive environment by property ID. Although the Property ID is a number in each of these tables, each table stores it differently:
123 - Property ID (char 30) (CAST from an integer to a char in a temp table)
123 - Property ID (char 30) - with database trigger upshifting this field
123 - Property ID (Integer)
123 - Property ID (Decimal)
When a program tries to compare these, will it , or should it see them as being the same?
July 12, 2010 at 2:33 pm
Comparing 123 accross those data types should evaluate to an equality. Are you seeing something different.
On another note, you may want to correct the problem of storing the same data in multiple places with different data types.
July 15, 2010 at 1:47 pm
.. it turns out that wasn't the issue, it had to do with the triggers on the tables and modifying fields within the primary key on that table.
On the table where property is defined as a char, the trigger upshifts the property ID by deleting the existing record in the database and re-inserting an entirely new record.
On the table where property is an integer, that wasn't happening. As it was explained to me, the trigger never found the record we intended to update, so it did nothing which is what made me think it wasn't matching correctly.
July 16, 2010 at 9:42 am
I'm glad you figured it out. I would still suggest that you take steps to make your data types consistent accross your tables.
July 16, 2010 at 9:58 am
Thanks, and yes if it were up to me I would make the datatypes consistent across tables. Unfortunately I'm neither the DBA, nor programmer. .I'm the QA guy that discovered the problem 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply