Solved: if or case statement in SQL view using Query Designer within MS SQL Server Management Studio 2008

I have a view that pulls in multiple ship to and corporate addresses assigned to one invoice transaction. I need to push my if then statement back down do the view level out of the report i wrote so other apps can hit it. How do i add a column(s) and populate that column(s) from data ALREADY in the view using a key ALREADY in the view. I have a field that will tell me which address they put on the invoice i want to populate a new column with that data, which will just come from other columns.

Thanks

SELECT     TOP (100) PERCENT DTL.IHTRX_NO, HDR.IHINVOICE_NO, DTL.IHDSEQ_NO, HDR.IHINV_DATE, DTL.IHDDLV_DATE, DTL.IHD_DLVRCT_NO, 
                      DD.TRIP_NO, HDR.IHAR_CUST_CD, ST.BILLTO_NUM, STv2.NAME AS MAIL_NAME, STv2.ADDR1 AS MAIL_ADDR1, STv2.ADDR2 AS MAIL_ADDR2, 
                      STv2.ADDR3 AS MAIL_ADDR3, STv2.CITY AS MAIL_CITY, STv2.STATE AS MAIL_STATE, STv2.ZIP AS MAIL_ZIP, STv2.COUNTRY AS MAIL_COUNTRY, 
                      C.CSNAME, C.CSADDR1, C.CSADDR2, C.CSADDR3, C.CSCITY, C.CSST, C.CSZIP, C.CSCOUNTRY, HDR.SHIP_TO_NO, HDR.CSCODE, ST.NAME, 
                      ST.ADDR1, ST.ADDR2, ST.ADDR3, ST.CITY, ST.STATE, ST.ZIP, ST.COUNTRY, TKT.TRUCK_TYPE_COMMENT, HDR.TERMCODE, T.TERMNAME, 
                      HDR.SACODE, SA.SANAME, HDR.CARRIER_TRUCK_CD, TC.DESCR, HDR.IHFOB_CD, DTL.IHDQTY_ORD, DTL.ORDER_NO, O.JOB_NUMBER, 
                      DTL.IHDDESCR_1, DTL.IHDDESCR_2, DTL.IHDNOTE, DTL.CUST_REL_NO, DTL.IHDCUST_PO_NO, DTL.IHDBILL_QTY, DTL.IHDPRICE, 
                      RTI.TOTALPRICE, DTL.IHDPRICE_METHOD, DTL.IHDAR_AMT1, DTL.IHDTOT_AR_AMT, IM.MISCCODE, MC.DESCR AS MISC_DESCR, IM.IHDMISC_AMT, 
                      MC.CHG_BASIS, IM.IHDMISC_EXT_AMT, HDR.IHDISC_AMT, HDR.IHDISC_DATE, HDR.IHTOT_AR_AMT
FROM         BURD.dbo.INVHIST_HDR AS HDR WITH (nolock) LEFT OUTER JOIN
                      BURD.dbo.INVHIST_DTL AS DTL WITH (nolock) ON HDR.IHTRX_NO = DTL.IHTRX_NO LEFT OUTER JOIN
                      BURD.dbo.TERMS AS T WITH (nolock) ON HDR.TERMCODE = T.TERMCODE LEFT OUTER JOIN
                      BURD.dbo.CUSTOMER AS C WITH (nolock) ON HDR.IHAR_CUST_CD = C.CSCODE LEFT OUTER JOIN
                      BURD.dbo.SALESAGENT AS SA WITH (nolock) ON HDR.SACODE = SA.SACODE LEFT OUTER JOIN
                      BURD.dbo.TRUCK_CARRIER AS TC WITH (nolock) ON HDR.CARRIER_TRUCK_CD = TC.CARRIER_TRUCK_CD LEFT OUTER JOIN
                      BURD.dbo.INVHST_MISCCHG AS IM WITH (nolock) ON HDR.IHTRX_NO = IM.IHTRX_NO AND IM.include_in_sp_flg = 'N' LEFT OUTER JOIN
                      BURD.dbo.MISC_CODES AS MC WITH (nolock) ON IM.MISCCODE = MC.MISCCODE LEFT OUTER JOIN
                      BURD.dbo.ORDERS AS O WITH (nolock) ON DTL.ORDER_NO = O.ORDER_NO LEFT OUTER JOIN
                      BurdInventoryImaginera.dbo.RTI AS RTI WITH (nolock) ON O.ORDER_NO = RTI.ORDERNO LEFT OUTER JOIN
                      BURD.dbo.SHIP_TO AS ST WITH (nolock) ON HDR.CSCODE = ST.CSCODE AND HDR.SHIP_TO_NO = ST.SHIP_TO_NO LEFT OUTER JOIN
                      BURD.dbo.SHIP_TO AS STv2 WITH (nolock) ON ST.CSCODE = STv2.CSCODE AND ST.BILLTO_NUM = STv2.SHIP_TO_NO LEFT OUTER JOIN
                      BURD.dbo.DLVDET AS DD WITH (nolock) ON DTL.ORDER_NO = DD.ORDER_NO AND DTL.RELEASE_NO = DD.RELEASE_NO LEFT OUTER JOIN

Open in new window