Advanced Subqueries - query fields
Answered
When building a report and using the Advanced sub query function to join two Views is it "required", "recommended" or "best practice" that the fields sued for Master Query and Sub Query be numeric versus character?
I struggled to get this to work using fields the DB team created that were defined as CHAR in the database for the application. Once I had them create new columns defined as bigint with the same values I got much better and more predictable results.
Looking for guidance for future DB designs
The same question
Hi Dan,
Thanks for reaching out to us on this. To be honest, this question is a little outside the scope of normal support but let me do my best to respond.
Fundamentally, there should be no reason for there to be issues joining on non-numeric fields. The only issues I can see are the potential for null values or inconsistent values in the data. We often join fields on varchar values and have few issues.
With this being said, from what you have stated, it sounds like the data in the fields you were joining on were in fact numeric but the DB team created them as CHAR. It is generally recommended to have the datatype in the database directly correlate to the type of data it holds, so if the values you are using are numbers then defining the datatype as a number (bigint or similar) is the best way forward.
Hopefully that helps, if you need anything further from me or if I have missed something please let me know.
Cheers,
Neal
Hi Dan,
Thanks for reaching out to us on this. To be honest, this question is a little outside the scope of normal support but let me do my best to respond.
Fundamentally, there should be no reason for there to be issues joining on non-numeric fields. The only issues I can see are the potential for null values or inconsistent values in the data. We often join fields on varchar values and have few issues.
With this being said, from what you have stated, it sounds like the data in the fields you were joining on were in fact numeric but the DB team created them as CHAR. It is generally recommended to have the datatype in the database directly correlate to the type of data it holds, so if the values you are using are numbers then defining the datatype as a number (bigint or similar) is the best way forward.
Hopefully that helps, if you need anything further from me or if I have missed something please let me know.
Cheers,
Neal
Replies have been locked on this page!