SHARE
TWEET

Untitled

a guest Jun 19th, 2017 40 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE VIEW [VIEW1] AS
  2. SELECT  DISTINCT OLN.ORD_NUM,
  3.     ORD.ORDR_DTE,
  4.     OLN.PMO_CDE,
  5.     ORD.CTM_NBR,
  6.     OLN.SHP_CTM
  7. FROM PROOLN_M AS OLN
  8. LEFT JOIN PROORD_M AS ORD ON OLN.ORD_NUM=ORD.ORD_NUM
  9. WHERE ITM_NUM='WTG11C'
  10.  
  11.  
  12. CREATE VIEW [BILLTOCTM] AS
  13. SELECT DISTINCT VIEW1.ORD_NUM,
  14.     VIEW1.CTM_NBR,
  15.     ADR.CMP_NME,
  16.     ((ADR.ATN_1ST)+(ADR.ATN_MID)+(ADR.ATN_END)) AS 'ATN_NME',
  17.     ADR.STR_1ST,
  18.     ADR.STR_2ND,
  19.     ADR.CTM_CTY,
  20.     ADR.CTM_STE,
  21.     ADR.ZIP_CDE,
  22.     ADR.CUN_TYP,
  23.     CTM.CTM_TYP,
  24.     EML.ADR_EMAL,
  25.     EML.PMO_EML
  26. FROM VIEW1
  27. LEFT JOIN CDSADR_M AS ADR ON VIEW1.CTM_NBR=ADR.CTM_NBR
  28. LEFT OUTER JOIN CDSCTM_M AS CTM ON VIEW1.CTM_NBR=CTM.CTM_NBR
  29. LEFT OUTER JOIN CDSEML_M AS EML ON VIEW1.CTM_NBR=EML.CTM_NBR
  30.  
  31.  
  32. CREATE VIEW [SHIPTOCTM] AS
  33. SELECT DISTINCT VIEW1.ORD_NUM,
  34.     VIEW1.SHP_CTM,
  35.     ADR.CMP_NME,
  36.     ((ADR.ATN_1ST)+(ADR.ATN_MID)+(ADR.ATN_END)) AS 'ATN_NME',
  37.     ADR.STR_1ST,
  38.     ADR.STR_2ND,
  39.     ADR.CTM_CTY,
  40.     ADR.CTM_STE,
  41.     ADR.ZIP_CDE,
  42.     ADR.CUN_TYP,
  43.     CTM.CTM_TYP,
  44.     EML.ADR_EMAL,
  45.     EML.PMO_EML
  46. FROM VIEW1
  47. LEFT JOIN CDSADR_M AS ADR ON VIEW1.SHP_CTM=ADR.CTM_NBR
  48. LEFT OUTER JOIN CDSCTM_M AS CTM ON VIEW1.SHP_CTM=CTM.CTM_NBR
  49. LEFT OUTER JOIN CDSEML_M AS EML ON VIEW1.SHP_CTM=EML.CTM_NBR
  50.  
  51.  
  52. CREATE TABLE #LIST (
  53. ORD_NUM <VARCHAR>,
  54. ORDR_DTE <DATETIME>,
  55. PMO_CDE <VARCHAR>,
  56. BCTM_NBR <VARCHAR>,
  57. BCMP_NME <VARCHAR>,
  58. BATN_NME <VARCHAR>,
  59. BSTR_1ST <VARCHAR>,
  60. BSTR_2ND <VARCHAR>,
  61. BCTM_CTY <VARCHAR>,
  62. BCTM_STE <VARCHAR>,
  63. BZIP_CDE <VARCHAR>,
  64. BCUN_TYP <VARCHAR>,
  65. BCTM_TYP <VARCHAR>,
  66. BADR_EMAL <VARCHAR>,
  67. BPMO_EML <VARCHAR>,
  68. SCTM_NBR <VARCHAR>,
  69. SCMP_NME <VARCHAR>,
  70. SATN_NME <VARCHAR>,
  71. SSTR_1ST <VARCHAR>,
  72. SSTR_2ND <VARCHAR>,
  73. SCTM_CTY <VARCHAR>,
  74. SCTM_STE <VARCHAR>,
  75. SZIP_CDE <VARCHAR>,
  76. SCUN_TYPE <VARCHAR>,
  77. SCTM_TYP <VARCHAR>,
  78. SADR_EMAL <VARCHAR>,
  79. SPMO_EML <VARCHAR>
  80. )
  81.  
  82.  
  83. INSERT INTO
  84.     #LIST
  85. SELECT DISTINCT VIEW1.ORD_NUM AS 'ORD_NUM',
  86.     VIEW1.ORDR_DTE AS 'ORDR_DTE',
  87.     VIEW1.PMO_CDE AS 'PMO_CDE',
  88.     VIEW1.CTM_NBR AS 'BCTM_NBR',
  89.     BILL.CMP_NME AS 'BCMP_NME',
  90.     BILL.ATN_NME AS 'BATN_NME',
  91.     BILL.STR_1ST AS 'BSTR_1ST',
  92.     BILL.STR_2ND AS 'BSTR_2ND',
  93.     BILL.CTM_CTY AS 'BCTM_CTY',
  94.     BILL.CTM_STE AS 'BCTM_STE',
  95.     BILL.ZIP_CDE AS 'BZIP_CDE',
  96.     BILL.CUN_TYP AS 'BCUN_TYP',
  97.     BILL.CTM_TYP AS 'BCTM_TYP',
  98.     BILL.ADR_EMAL AS 'BADR_EMAL',
  99.     BILL.PMO_EML AS 'BPMO_EML',
  100.     VIEW1.SHP_CTM AS 'SCTM_NBR',
  101.     SHIP.CMP_NME AS 'SCMP_NME',
  102.     SHIP.ATN_NME AS 'SATN_NME',
  103.     SHIP.STR_1ST AS 'SSTR_1ST',
  104.     SHIP.STR_2ND AS 'SSTR_2ND',
  105.     SHIP.CTM_CTY AS 'SCTM_CTY',
  106.     SHIP.CTM_STE AS 'SCTM_STE',
  107.     SHIP.ZIP_CDE AS 'SZIP_CDE',
  108.     SHIP.CUN_TYP AS 'SCUN_TYPE',
  109.     SHIP.CTM_TYP AS 'SCTM_TYP',
  110.     SHIP.ADR_EMAL AS 'SADR_EMAL',
  111.     SHIP.PMO_EML AS 'SPMO_EML'
  112. FROM VIEW1
  113. LEFT JOIN BILLTOCTM AS BILL ON VIEW1.ORD_NUM=BILL.ORD_NUM
  114.     AND VIEW1.CTM_NBR=BILL.CTM_NBR
  115. LEFT OUTER JOIN SHIPTOCTM AS SHIP ON VIEW1.ORD_NUM=SHIP.ORD_NUM
  116.     AND VIEW1.SHP_CTM=SHIP.SHP_CTM
  117. ORDER BY VIEW1.ORD_NUM
RAW Paste Data
Top