Sql Custom Query

Legacy Poster

• 2014年05月13日 16:48

Hi:

 

I have an issue with my Bartender Query, well first I have to explain the situation:

 

We have to make a label which needs data from 2 tables in our DB, unfortunately the DB design is a sh*t, the relation field in one table is numeric and in the other table is char, so the data "00012345" is not equeal to 12345 (the left zeros are not stored in the numeric field).

 

I need to:

 

1.- Create a query in which I can put an IF...ELSE statement, I mean, SELECT field1, field2, from TABLE ... WHERE IF LENGHT(field1) < 8 ... and then concatenate as many zeros as the data needs.

 

2.- Store in a variable the data that has no left zeros, concatenate the zeros and then use this variable in my WHERE clause.

 

Ok, I can not change the data type in the DB field because it is validated in other apps.

 

Some ideas?

 

Regards,

0

• 

  Ian Cummings

  モデレータ

  ★ BarTender Hero ★

  • 2014年05月26日 12:01

  Have you tried creating a custom view of the two tables in the database system itself?  I would imagine that it's easier to pull off a join between the ill matching fields in that context rather than attempting it in BarTender.

   

  This article on Stackoverflow seems to go along with this idea: http://stackoverflow.com/questions/1228639/how-to-join-tables-together-on-columns-with-different-datatypes

   

  For more information perform a Google search on the following terms: sql join fields of different type

  0

サインインしてコメントを残してください。