• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

joining two tables with one column having 2 leading zeros.

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
asullivan62
Asked:
asullivan62
1 Solution
 
Patrick MatthewsCommented:
Hi asullivan62,

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

will *always* return '00000000'

Did you perhaps mean Right instead of Left?

Regards,

Patrick
0
 
asullivan62Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Anthony PerkinsCommented:
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
 
asullivan62Author Commented:
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
 
asullivan62Author Commented:
I found some non numeric data in the column which is why I got the error. Thanks
0
 
Anthony PerkinsCommented:
Great! Now please close the question.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now