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 - 2015-02-26 : 10:02:26
|
| I have following view which is working but not sure how to add 2 tables to join.This table is adres1 and it will join on the IDENT# and IDSFX# to tableprodta.adres1 called adent# and adsfx#, there I need a col. ads15. then i also need to get the ship to, row in this adres1. this we get first from the order table, prodta. oeord1 in col. odgrc#. This grc# is 11 pos and is combined 8 and 3 of the ent and suf. these 2 represent the ship to record and looking in same table adres1 (we do have many logical views on them if it's easier, like adres15) we can get col. ADSTTC for the ship to state.Not sure if can included these 2 new parts to the current view created code below. PLease ask if something not clear, it's an old system and somewhat developed convoluted. its for db2 but the concepts are the same.CREATE VIEW Prolib.SHPWEIGHTP AS SELECT T01.IDORD#, T01.IDDOCD, CREATE VIEW Prolib.SHPWEIGHTP AS SELECT T01.IDORD#, T01.IDDOCD, T01.IDPRT#, t01.idsfx#, T01.IDSHP#, T01.IDNTU$, T01.IDENT#, ( T01.IDNTU$ * T01.IDSHP# ) AS LINTOT , T02.IAPTWT, T02.IARCC3, T02.IAPRLC, T03.PHVIAC, T03.PHORD#,PHSFX#, T01.IDORDT, T01.IDHCD3, t04.ads15 FROM PRODTA.OEINDLID T01 INNER JOIN PRODTA.ICPRTMIA T02 ON T01.IDPRT# = T02.IAPRT# INNER JOIN (SELECT DISTINCT PHORD#, PHSFX#, PHVIAC, PHWGHT FROM proccdta.pshippf) AS T03 ON t01.idord# = T03.phord# JOIN prodta.adres1 as t04 on t04.adent# = t01.adent# and t04.adsfx# = t01.adsfx# JOIN prodta.oeord1 t05 on t05.odgrc# = T01.IDENT# || T01.SUFFIX WHERE T01.IDHCD3 IN ('MDL','TRP') |
|
|
|
|
|
|
|
need to add 2 new aspects to this view