Solved

joining two tables with one column having 2 leading zeros.

Posted on 2006-10-27
7
373 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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift 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 250 total points
ID: 17835737
Great! Now please close the question.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

756 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