Solved

joining two tables with one column having 2 leading zeros.

Posted on 2006-10-27
7
370 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
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
Great! Now please close the question.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now