Union All Command alias for duplicate field names

[Former Member]

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

[Former Member · Accepted solution]

hi,

Replace

h.code, h.descript

with

h.code as emp_code, h.descript as emp_descript

Now you will get these 2 new columns independently without pointing to b.CODE & b.DESCRIPT

Regards,

Vamsee

[Former Member · Accepted solution]

I adore you both for responding so quickly!

h.code as emp_code, h.descript as emp_descript

Vamsee, your solution worked perfectly!

Thank you both

Debi

[Former Member]

Try

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 budget_code, b.DESCRIPT budget_desc,
 
 e.empid, e.contactkey, e.sect,
 f.cntctkey, f.idkey,
 g.idkey, g.NAMELAST, g.NAMEFIRST, g.NAMEMID,
 
 h.code emp_cde, h.descript emp_desc
 
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

Ian