Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2013-10-02 : 18:36:59
|
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?SELECTALL 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 JOINASTDTA.OEIND1 T02ON T01.OHORD# = T02.IDORD# LEFT OUTER JOINASTDTA.OEINHDIH T03ON T01.OHORD# = T03.IHORD#WHERE T01.OHOSTC = 'CL'AND T01.OHORDD >= 20120101ORDER BY T01.OHORD# ASC |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-02 : 18:40:34
|
What is the data type? Could you show us a data example?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2013-10-02 : 18:56:42
|
the data is char 8 for the 3 tablesand it is char 10 for the 4rth that we need to add in. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-10-02 : 20:00:15
|
[code]SELECT T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#, T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#FROM ASTDTA.OEORHDOH T01LEFT 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 charsWHERE T01.OHOSTC = 'CL'AND T01.OHORDD >= 20120101ORDER BY T01.OHORD# ASC[/code]=================================================The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2013-10-03 : 09:17:17
|
Thanks very much BK.I am getting this to work but it's not bringing the column we need, this MHZONESELECT 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-03 : 12:46:49
|
What does "it's not bringing the column we need" mean? Please be more clear.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|