Reply to: SQL join query

[woohoo]

A WITH (NOLOCK) hint may speed it up if you've got performance issues.

I don't know what kind of data OP has or how often its updated. But I would suggest if it's financial or needs to be accurate and is updated frequently then he at least understands the downsides of NOLOCK.

[LondonManc]

The big danger is data quality - the VARCHAR field might not contain just the equivalent integers.

Just run a select cast(table.varcharcol as int) from table on the table with the varchar field.
If it breaks, you've got data integrity issues; if it doesn't, go with the cast in the join as others have suggested. A WITH (NOLOCK) hint may speed it up if you've got performance issues.

[original PM]

Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...

Yes

Generally it is the cost centre which i am trying to join on but the I am trying to get the query to run using databases from 2 different systems

In 1 this is a varchar in the other it is an int - so same data but different types.

Same restriction on entry as well - but managed at front end not db level.

[WTFH]

Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...

...and strip off any spaces.

CAST(LTRIM(RTRIM(thatfield)) as INT)

[oscarose]

Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...

[original PM]

There are only two things that might indicate there is a God.

The first is your liver is six times as big as it needs to be.

The second is MS Access.

It is just some static data I need to load into a new system and update every 6 months or so.

As I currently do not use Access not thinking there is any point bringing another system into the mix.....

Excel lookups it is although I will play around with CAST if I get the chance.

[Cirrus]

FTFY

It is probably going to be as easy to just dump into Access.

There are only two things that might indicate there is a God.

The first is your liver is six times as big as it needs to be.

The second is MS Access.

[BigRed]

The CAST is going to make it non SARGable, but if you think you can dump it all into excel and use VLOOKUP the tables are obviously small enough to do full scans on.

[WTFH]

... and sack the guy who created the tables that way.

Also, given that it's MicroShaft, if CAST doesn't work on one side, try it on the other, and then try it on both.
(Can you tell I've had issues like this before? Field "order" is an 8 digit integer. Field "Original order" is an 8 character alphanumeric. Field "Related order" is an 8 character alphanumeric.)

[WTFH]

How many records are we talking about, and how billable is all our time to fix it for you?

If it doesn't work in a few minutes, I'll dump to excel and do it there, then go back in the future and try to work it out.

[SeanT]

I'm just chuckling at the title (I'd expect cast to work though)

[woohoo]

It is probably going to be as easy to just dump into excel and then do a lookup

I was trying to be clever but that rarely works!

Cheers guys

ok but what's the issue with trying a cast, looks simple. I'm genuinely interested if this didn't work.

INNER JOIN dbo.TestB ON TestB.Age = CAST(TestA.Age AS VARCHAR(255));

[original PM]

It is probably going to be as easy to just dump into excel and then do a lookup

I was trying to be clever but that rarely works!

Cheers guys

[BrilloPad]

Personally I have always found sql server to be very good at int/varchar. Perhaps only issue is the have an invoice number supposed to be an integer in a varchar field. If you say InvoiceNumber <> 0 that can cause issues if non integer values occur : so you have to use '0'.

Biggest issue I have found is collation. LInked survers with difference default collations. gggrrrrrrr

[woohoo]

You could try using a cast, though in this example with or without cast worked for me. It's going to perform horribly though. I'm assuming this is a one off?

CREATE TABLE TestA ( Age INT );

INSERT  INTO dbo.TestA
        ( Age )
VALUES  ( 30  -- Age - int
          );
INSERT  INTO dbo.TestA
        ( Age )
VALUES  ( 40  -- Age - int
          );

CREATE TABLE TestB ( Age VARCHAR(255), );

INSERT  INTO dbo.TestB
        ( Age )
VALUES  ( '30'  -- Age - varchar(255)
          );

SELECT  *
FROM    dbo.TestA
        INNER JOIN dbo.TestB ON TestB.Age = TestA.Age;

SELECT  *
FROM    dbo.TestA
        INNER JOIN dbo.TestB ON TestB.Age = CAST(TestA.Age AS VARCHAR(255));

If it's not a one-off I guess you could "fix" the data types or add a computed column with an index.