Skip to Content
0
Former Member
Jun 07, 2011 at 01:41 PM

Union All Command alias for duplicate field names

165 Views

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