SQL help.

Factual Questions
1

I am trying to join two tables. I am trying to do this in a macro language which allows sql statements to run on files delimited with a | (I think they are called ‘pipe-delimited’)

The trouble is, the field I am tring to join with is stored as different data type in each database. One table it is stored as type char_array, in the other it’s stored as type long. In both it is a seven digit integer (an account number)

Is there any way of performing a type conversion on the join condition before comparing the two?

a sample of the query is thus…
select firstname, lastname acct_nr, balance from db1 left join db2 on db1.acct_nr = db2.acct_nr order by balance

only db1.acct_nr is ‘char_array’ and db2.acct_nr is ‘long’.

2

Lobsang, if you’re running this in a stored procedure, yes, you can either do Clng or Cint conversion when you declare the variables. (I’m not at work to verify, so take with a grain of salt.)

3

I am not at work either… so can’t try your suggestion yet. But thanks anyway.

I assume the query would go something like

select firstname, lastname acct_nr, balance from db1 left join db2 on Clng(db1.acct_nr) = db2.acct_nr order by balance

(db2 contains the long field)

4

That’s a VB statement, unless I’m missing something.
In SQL, you’d do convert(char(20), long_field) or convert(long, char_field) with the first being safer since a char field will pretty much take anything.
If you’re using MS SQL Server, they also have a cast statement.
See cast and convert.

Also, I don’t recall there being a long type in SQL; I think it’s int only (with smallint and tinyint or something along those lines).