My Union All SQL Command Statement worked great until I added
h.code, h.descript
& the last statement
LEFT OUTER JOIN RESOURCES.EMPSECTION AS h ON e.SECT=h.CODE
It then automatically pointed b.CODE & b.DESCRIPT to h.code & h.descript and I no longer got the proper data for b.CODE & b.DESCRIPT.
How do I put an alias in the statement for one of the code & descript sets?
SELECT
u.SourseTable,
u.HISTKEY, u.CHRGDTTM, u.CHRGDTTMTO, u.DSTBGTKEY, u.USAGE, u.PAYTYPE,
u.TOTCOST, u.empid, u.comptype,
b.BUDGETNUMBERKEY, b.CODE, b.DESCRIPT,
e.empid, e.contactkey, e.sect,
f.cntctkey, f.idkey,
g.idkey, g.NAMELAST, g.NAMEFIRST, g.NAMEMID,
h.code, h.descript
FROM (
SELECT 'wo' AS SourseTable,
L1.HISTKEY, L1.CHRGDTTM, L1.CHRGDTTMTO, L1.DSTBGTKEY,
L1.USAGE, L1.PAYTYPE, L1.TOTCOST, L1.empid, null as comptype
FROM WORKMANAGEMENT.COSTLABR AS L1
union all
SELECT 'timesheet' AS SourseTable,
L2.COSTKEY, L2.CHRGDTTM, L2.CHRGDTTMTO, L2.BGTNO,
L2.USAGE, L2.PAYTYPE, L2.TOTCOST, L2.empid, null as comptype
FROM RESOURCES.EMPOTHER AS L2
union all
SELECT 'sr' AS SourseTable,
L3.SERVNO, L3.CHRGDTTM, L3.CHRGDTTMTO, L3.DSTBGTKEY,
L3.USAGE, L3.PAYTYPE, L3.TOTCOST, L3.empid, null as comptype
FROM CRM.ICSTLABR AS L3
union all
SELECT 'inspection' AS SourseTable,
L4.INSPKEY, L4.CHRGDTTM, L4.CHRGDTTMTO, L4.DSTBGTKEY,
L4.USAGE, L4.PAYTYPE, L4.TOTCOST, L4.empid, L4.comptype
FROM ASSETMANAGEMENT.INSPCOSTLABR AS L4
) AS u
LEFT OUTER JOIN RESOURCES.BUDGETNUMBER AS b ON u.DSTBGTKEY=b.BUDGETNUMBERKEY
INNER JOIN RESOURCES.EMPLOYEE AS e ON u.EMPID=e.empid
INNER JOIN RESOURCES.CONTACT AS f ON e.CONTACTKEY= f.CNTCTKEY
INNER JOIN RESOURCES.CNTCTID AS g ON f.IDKEY=g.IDKEY
LEFT OUTER JOIN RESOURCES.EMPSECTION AS h ON e.SECT=h.CODE
Edited by: Debi Herbert on Jun 7, 2011 9:41 AM
Edited by: Debi Herbert on Jun 7, 2011 9:42 AM