Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Inner join NOT working - numeric fields

Posted on 2016-10-20
11
Medium Priority
?
102 Views
Last Modified: 2016-11-07
I am trying to do an inner join in an open query (Oracle) where both join fields are numeric, but it returns zero results when I can see that there are matches between the two tables.


One table has almost 1 million records and the other has only 678. I'd hate to use a left outer join as it takes too long and gives me unwanted records/results.

"inner join on isnumeric(b.EMPL_NO) = isnumeric(a.emplid)"

How can I solve this?
Thanks
0
Comment
Question by:saved4use
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41853190
Without looking into the data, it's hard to tell what's wrong. If they are numeric, why do you want to apply isnumeric again?
0
 

Author Comment

by:saved4use
ID: 41853218
Because the open query would not even run without isnumeric before the two fields.
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 41853252
Are you certain that you are querying an Oracle database?  To the best of my knowledge, Oracle does not have an "isnumeric" function.  And if it did, this type of usage would be returning, and comparing, two boolean values.

I'm guessing that the actual datatypes of the two table fields is one of the various "char" types.  If so, you could use the "to_number" function to convert them from character to numeric.
0
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41853635
It shouldn't be
"inner join on b.EMPL_NO = a.emplid" ?

What for do you need the isnumeric function?
0
 

Author Comment

by:saved4use
ID: 41854133
@JesterToo: I'm using an Oracle open query through SQL server.

@Vitor: I tried your suggestion above and it does not work/does not return results.

I even used this on the Oracle db and I get null in the results:

select

LENGTH(TRIM(TRANSLATE(EMPL_NO, ' +-.0123456789', '')))

from IBDD

where

LENGTH(TRIM(TRANSLATE(EMPL_NO, ' +-.0123456789', ''))) is null
0
 
LVL 32

Expert Comment

by:awking00
ID: 41854296
You cannot use an empty string for the to_string parameter of the translate function. You might try the following -
WHERE REPLACE(REGEXP_REPLACE(EMPL_NO,'[0-9\. +-]',''x'),'x','') IS NULL
Note - I used 'x' but it can be any character that will never show up in empl_no.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41856325
Could you provide a few values of each please? E.g.

Select top (10) b.EMPL_NO from b
Select top (10) a.emplid from a

Just a few of each should be enough
0
 

Accepted Solution

by:
saved4use earned 0 total points
ID: 41857762
I ended up dumping the query results into two tables, exporting the content to Excel and converting from text to number and working with them that way. It appears I was trying to join '00000518903' to '518903'
Nevertheless, thanks.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41857945
next time try something like these:

'00000518903' to 518903 (joining padded string to integer)

e.g.
inner join on b.EMPL_NO = RIGHT('00000000000' + cast(a.emplid as varchar(11)),11)

=====================
'00000518903' to '518903' (if joining padded string to string)

e.g.
inner join on b.EMPL_NO = RIGHT('00000000000' + a.emplid,11)

================

swap the columns names as needed (wasn't sure which one is zero padded)

=================
{+ edit}

it IS possible to work the other way (to convert the strings to numbers) BUT it might produce conversion error. However you could try these:


inner join on try_cast(b.EMPL_NO as integer) = a.emplid --<< try_cast() requires SQL Server 2012 or later

inner join on cast(b.EMPL_NO as integer) = a.emplid
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41858098
It appears I was trying to join '00000518903' to '518903'
Oh, now I think I understand the isnumeric(b.EMPL_NO) = isnumeric(a.emplid)

You should use CAST function to convert both fields to integer and to assure that those strings are valid number then use the ISNUMERIC function in the WHERE clause:
SELECT *
FROM Table1 a
    INNER JOIN Table2 b ON CAST(b.EMPL_NO AS INT) = CAST(a.emplid AS INT)
WHERE ISNUMERIC(b.EMPL_NO) = 1 AND ISNUMERIC(a.emplid) = 1

Open in new window

0
 

Author Closing Comment

by:saved4use
ID: 41876870
No other reasonable answer worked.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question