Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Union All Command alias for duplicate field names

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
Former Member replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question