Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

joining two tables with one column having 2 leading zeros.

Posted on 2006-10-27
7
Medium Priority
?
378 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

604 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