?
Solved

joining two tables with one column having 2 leading zeros.

Posted on 2006-10-27
7
Medium Priority
?
376 Views
Last Modified: 2006-11-18
I need to join two tables on the order number columns. One table.column has two leading zeros. The results are not what I expected.

my query:
select i.ship_dt,i.ord_no as FDX_ord_no,l.ord_no as Mac_ord_no from fdx_info i join oelincmt_sql l
on (left('0000000000' + i.ord_no, 8)) = l.ord_no
where i.ship_dt = '20061025'
group by i.ship_dt,i.ord_no,l.ord_no
order by i.ord_no

my results:
date          ord_no      Mac_ord_no
20061025      669631      00000000
20061025      669642      00000000
20061025      680878      00000000
20061025      680933      00000000
20061025      684350      00000000
20061025      684518      00000000

I have also tried :
on ('00' + i.ord_no) = l.ord_no

So what am I doing wrong
0
Comment
Question by:asullivan62
[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
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 17822741
Hi asullivan62,

left('0000000000' + i.ord_no, 8)

will *always* return '00000000'

Did you perhaps mean Right instead of Left?

Regards,

Patrick
0
 

Author Comment

by:asullivan62
ID: 17822905
What I really want to do is, add two zeros from one column of data to perform the join, or
join
on substring(myfield,2,8) = my_other_field.
trying to join on the last 8 characters.

0066666666 = 66666666
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17823117
Is ord_no a numeric field in either table?  Or a varchar in both?

In oelincmt_sql is mustn't be a numeric field is it?
And is it always of 8 characters with two leading zeros?

Did you try Patricks recommendation to use Right instead of Left?
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17824150
Or:

select      i.ship_dt,
      i.ord_no as FDX_ord_no,
      l.ord_no as Mac_ord_no
from      fdx_info i
      join oelincmt_sql l on CAST(i.ord_no as integer) = CAST(l.ord_no as integer)
where      i.ship_dt = '20061025'
group by
      i.ship_dt,
      i.ord_no,
      l.ord_no
order by
      i.ord_no
0
 

Author Comment

by:asullivan62
ID: 17833764
acperkins,
I have tried the , on (right('0000000000' + i.ord_no, 8)) = l.ord_no. I only get 2 records back.

When running your query I get an error:
Syntax error converting the varchar value '00A.O.F.' to a column of data type int.

fdx_info.ord_no  = varchar(50)
oelincmt_sql.ord_no = char(8)

oelincmt_sql.ord_no       fdx_info.ord_no
00689112                689112

The first 2500 records for oelincmt_sql.ord_no are simply '00'. This must be old data from a different system. After the 2500 records all the order numbers are 8 characters with leading zeros. The zeros are removed before Fedex's software scans and fills in the ord_no to fill in info for shipping and then re-written to oelincmt_sql with a new record and tracking number. I am trying to compare the fdx_info and the oelincmt_sql to create a report of what was shipped for the day. The shipping manager is having issues and needs a report of daily shipping compared to our system which creates our billing, which he says is not getting billed becasue it is not written back to our system, oelincmt_sql. FDX_info is a table created and written to by Fedex software.
0
 

Author Comment

by:asullivan62
ID: 17834588
I found some non numeric data in the column which is why I got the error. Thanks
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 750 total points
ID: 17835737
Great! Now please close the question.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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