Hierarchy based issue

[Ashutosh11]

I have a hierarchy script but I want to change the levels like HqId> UserEmpCode>ManagerEmpCode> UserName and their level.

Can any one give me solution?

Script:

LIB CONNECT TO 'Extraction_Files:MySQL_Enterprise_Edition_SFA';

HHH:

Load

    num#(UserEmpCode) as UserEmpCode,

    UserName,

    num#(ManagerEmpCode) as ManagerEmpCode,

    ManagerName,

Segment as SFASegment, 

DepartmentName, 

DesignationName, 

StateName, 

DateOfJoining, 

DateOfResign, 

LabCode, 

LabName, 

AccountStatus, 

HqId, 

HqName, 

ZoneID, 

ZoneName ;

SELECT *

FROM ollsfaprod.`CliksenseSFA_SL_USERS`; 

New_Hierarchy_Temp:

Hierarchy (UserEmpCode, ManagerEmpCode, UserName , , UserName, NodePath, '/', NodeDepth)

LOAD

  UserEmpCode ,

  ManagerEmpCode ,

  UserName,

  ManagerName,

  SFASegment,    

DepartmentName, 

DesignationName, 

StateName, 

DateOfJoining, 

DateOfResign, 

LabCode, 

LabName, 

AccountStatus, 

HqId, 

HqName, 

ZoneID, 

ZoneName 

Resident HHH;

DROP Table HHH;

NoConcatenate 

Latest_Hierarchy_PCT:

Load

  UserEmpCode as pct_employee_code,

  ManagerEmpCode  as ManagerEmpCode_P,

  UserName as UserName_P,

  UserName1 as UserName1_P,

  UserName2 as UserName2_P,

  UserName3 as UserName3_P,

  UserName4 as UserName4_P,

  UserName5 as UserName5_P,

  UserName6 as UserName6_P,

  ManagerName as ManagerName_P,

  SFASegment as SFASegment_P, 

DepartmentName as DepartmentName_P, 

DesignationName as DesignationName_P, 

StateName as StateName_P, 

DateOfJoining as DateOfJoining_P, 

DateOfResign as DateOfResign_P, 

LabCode as LabCode_P, 

LabName as LabName_P, 

AccountStatus as AccountStatus_P, 

HqId as HqId_P, 

HqName as HqName_P, 

ZoneID as ZoneID_P, 

ZoneName as ZoneName_P

Resident New_Hierarchy_Temp;

Latest_Hierarchy_PMT:

Load

  UserEmpCode as pmt_employee_code,

  ManagerEmpCode  as ManagerEmpCode_M,

  UserName as UserName_M,

  UserName1 as UserName1_M,

  UserName2 as UserName2_M,

  UserName3 as UserName3_M,

  UserName4 as UserName4_M,

  UserName5 as UserName5_M,

  UserName6 as UserName6_M,

  ManagerName as ManagerName_M,

  SFASegment as SFASegment_M, 

DepartmentName as DepartmentName_M, 

DesignationName as DesignationName_M, 

StateName as StateName_M, 

DateOfJoining as DateOfJoining_M, 

DateOfResign as DateOfResign_M, 

LabCode as LabCode_M, 

LabName as LabName_M, 

AccountStatus as AccountStatus_M, 

HqId as HqId_M, 

HqName as HqName_M, 

ZoneID as ZoneID_M, 

ZoneName as ZoneName_M

Resident New_Hierarchy_Temp ;

Whole_Hierarchy:

Load

  UserEmpCode as UserEmpCode,

  ManagerEmpCode  as ManagerEmpCode_B,

  UserName as UserName_B,

  UserName1 as UserName1_B,

  UserName2 as UserName2_B,

  UserName3 as UserName3_B,

  UserName4 as UserName4_B,

  UserName5 as UserName5_B,

  UserName6 as UserName6_B,

  ManagerName as ManagerName_B,

  SFASegment as SFASegment_B, 

DepartmentName as DepartmentName_B, 

DesignationName as DesignationName_B, 

StateName as StateName_B, 

DateOfJoining as DateOfJoining_B, 

DateOfResign as DateOfResign_B, 

LabCode as LabCode_B, 

LabName as LabName_B, 

AccountStatus as AccountStatus_B, 

HqId as HqId_B, 

HqName as HqName_B, 

ZoneID as ZoneID_B, 

ZoneName as ZoneName_B

Resident New_Hierarchy_Temp where SFASegment = 'BOTH';

DROP Table New_Hierarchy_Temp;