SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to join in as a partial column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AdamWest
Constraint Violating Yak Guru

USA
353 Posts

Posted - 10/02/2013 :  18:36:59  Show Profile  Reply with Quote
We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)
whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done. How am I to add this in here?

SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC

tkizer
Almighty SQL Goddess

USA
37128 Posts

Posted - 10/02/2013 :  18:40:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
What is the data type? Could you show us a data example?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

USA
353 Posts

Posted - 10/02/2013 :  18:56:42  Show Profile  Reply with Quote
the data is char 8 for the 3 tables
and it is char 10 for the 4rth that we need to add in.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 10/02/2013 :  20:00:15  Show Profile  Reply with Quote
SELECT
  T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
  T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM
  ASTDTA.OEORHDOH T01
LEFT OUTER JOIN
  ASTDTA.OEIND1 T02
    ON T01.OHORD# = T02.IDORD#
LEFT OUTER JOIN
  ASTDTA.OEINHDIH T03
    ON T01.OHORD# = T03.IHORD#
LEFT OUTER JOIN
  MFH1MHL0 M
    ON T01.OHORD# = left(M.MHORDR, 8)  -- I'm assuming left 8 chars
WHERE
  T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC


=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

USA
353 Posts

Posted - 10/03/2013 :  09:17:17  Show Profile  Reply with Quote
Thanks very much BK.

I am getting this to work but it's not bringing the column we need, this MHZONE

SELECT

T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#,
M.MHZONE
FROM
ASTDTA.OEORHDOH T01
LEFT OUTER JOIN
ASTDTA.OEINDLID T02

ON T01.OHORD# = T02.IDORD#
LEFT OUTER JOIN

ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
LEFT OUTER JOIN
SHP4VAR27F.MFH1MH M
ON T01.OHORD# = left(M.MHORDR, 8)
WHERE


T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37128 Posts

Posted - 10/03/2013 :  12:46:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
What does "it's not bringing the column we need" mean? Please be more clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000