Originally posted by LondonManc
View Post
- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Reply to: SQL join query
Collapse
You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:
- You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
- You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
- If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
Logging in...
Previously on "SQL join query"
Collapse
-
-
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.
Leave a comment:
-
Originally posted by oscarose View PostAre you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...
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.
Leave a comment:
-
Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...
Leave a comment:
-
Originally posted by Cirrus View PostThere 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.
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.
Leave a comment:
-
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.
Leave a comment:
-
... 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.)
Leave a comment:
-
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.
Leave a comment:
-
Originally posted by original PM View PostIt 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
INNER JOIN dbo.TestB ON TestB.Age = CAST(TestA.Age AS VARCHAR(255));
Leave a comment:
-
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
Leave a comment:
-
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
Leave a comment:
-
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?
Code: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));
Last edited by woohoo; 25 April 2017, 13:17.
Leave a comment:
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: