Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I really don't see any error without the exception of you concatenation missing a field for the last ","
"Contact_Phone as [Sublease Tenant Phone] ,
missing field
Resident Leases where LFlag=1;
"
what I normally do in these cases is add an extra field that does not match with the first table, so I can identify what came from where, in this cases I added 'Leases' as Source to the bottom table, I also noticed the left joins you were doing to create the counters, I suggest you create them in you first load instead of doing left joins, this way you can avoid processing overhead and some nasty join issues (I had plenty 
already)
Subleases:
LOAD
F_Pm_Sublease_SEQ as SLCode,
F_Pm_Sublease_SEQ,
SubleaseCode,
SubleaseCode&' - '&SubleaseDescription as Sublease,
SubleaseDescription as [Sublease Description],
TypeDescription as [Sublease Type],
CurrentExpiry as [Sublease Expiry Date],
date(FinalExpiry) as [Sublease Final Expiry],
RenewalNotice as [Sublease Renewal Notice],
FutureTerms as [Sublease Future Terms],
ExternalCode as [SL Ext Acc],
date(StartDate) as [Sublease Start Date],
Sublease_SQM as [Subleased Area SQM],
Sublease_CARPARKS as [Subleased Carpark Count],
Sublease_PARKRENTAL as [Sublease Carpark Rent PA],
Sublease_RENTPA as [Sublease Rent PA],
Sublease_OPEXPA as [Sublease Opex PA],
CurrentTerm as [Sublease Current Term],
Sublease_TOTALRENTPA as [Sublease Total Rent PA],
if(Description='Active','Current',if(Description='Inactive','Expired',if(Description='Pending','Proposed',Description))) as [Sublease Status],
[Sublease Tenant Code],
[Sublease Manager]
1 as #Sublease_COUNTER
if ([Sublease Status]='Current',1) as #Sublease_COUNTER_2 ;
SQL SELECT
F_Pm_Sublease.F_Pm_Sublease_SEQ,
F_Pm_Sublease.SubleaseCode,
F_Pm_Sublease.SubleaseDescription,
F_Pm_Sublease.CurrentExpiry,
F_Pm_Sublease.CurrentTerm,
F_Pm_Sublease.FinalExpiry,
F_Pm_Sublease.RenewalNotice,
F_Pm_Sublease.ExternalCode,
F_Pm_Sublease.GrossRental,
F_Pm_Sublease.StartDate,
F_Pm_Sublease.FutureTerms,
F_Pm_SubleaseType.TypeDescription,
fn_SubLeaseArea.Sublease_SQM,
fn_SubLeaseCarparks.Sublease_CARPARKS,
fn_SubLeaseRent.Sublease_PARKRENTAL,
fn_SubLeaseRent.Sublease_RENTPA,
fn_SubLeaseRent.Sublease_OPEXPA,
fn_SubLeaseRent.Sublease_TOTALRENTPA,
F_StatusSubLease.Description,
F_Contact.CO_CODE as [Sublease Tenant Code],
F_Contact1.CO_NAME as [Sublease Manager]
FROM F_Pm_Sublease
LEFT OUTER JOIN F_Pm_SubleaseType ON F_Pm_Sublease.F_Pm_SubleaseType_SEQ = F_Pm_SubleaseType.F_Pm_SubleaseType_SEQ
LEFT OUTER JOIN fn_SubLeaseArea() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseArea.F_Pm_Sublease_SEQ
LEFT OUTER JOIN fn_SubLeaseCarparks() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseCarparks.F_Pm_Sublease_SEQ
LEFT OUTER JOIN fn_SubLeaseRent() ON F_Pm_Sublease.F_Pm_Sublease_SEQ = fn_SubLeaseRent.F_Pm_Sublease_SEQ
LEFT OUTER JOIN F_Pm_Tenant ON F_Pm_Sublease.PMT_SEQ = F_Pm_Tenant.PMT_SEQ
LEFT OUTER JOIN F_StatusSubLease ON F_Pm_Sublease.F_StatusSubLease_SEQ = F_StatusSubLease.F_StatusSubLease_SEQ
LEFT OUTER JOIN F_Pm_LeaseManager ON F_Pm_Sublease.F_Pm_LeaseManager_SEQ = F_Pm_LeaseManager.F_Pm_LeaseManager_SEQ
LEFT OUTER JOIN F_Contact F_Contact1 ON F_Pm_LeaseManager.CO_SEQ = F_Contact1.CO_SEQ
LEFT OUTER JOIN F_Contact ON F_Pm_Tenant.CO_SEQ = F_Contact.CO_SEQ;
left join (Subleases)
LOAD
CO_CODE as [Sublease Tenant Code],
CO_NAME as [Sublease Tenant Name],
CO_EMAIL as [Sublease Tenant Email],
CO_PH_BUS as [Sublease Tenant Phone];
SQL SELECT
F_Contact.CO_CODE,
F_Contact.CO_NAME,
F_Contact.CO_EMAIL,
F_Contact.CO_PH_BUS
FROM F_Pm_Tenant
LEFT OUTER JOIN F_Contact ON F_Pm_Tenant.CO_SEQ = F_Contact.CO_SEQ;
Concatenate (Subleases)
LOAD
#PML_COUNTER as #Sublease_COUNTER,
#PML_COUNTER_2 as #Sublease_COUNTER_2,
[Current Term] as [Sublease Current Term],
[Expiry Date] as [Sublease Expiry Date],
[Final Expiry] as [Sublease Final Expiry],
[Future Terms] as [Sublease Future Terms],
[L Ext Acc] as [SL Ext Acc],
[Lease] as [Sublease],
[Lease Carpark Rent PA] as [Sublease Carpark Rent PA],
[Lease Code] as [SLCode],
[Lease Description] as [Sublease Description],
[Lease Manager] as [Sublease Manager],
[Lease Opex PA] as [Sublease Opex PA],
[Lease Rent PA] as [Sublease Rent PA],
[Lease Start Date] as [Sublease Start Date],
[Lease Status] as [Sublease Status],
[Lease Total Rent PA] as [Sublease Total Rent PA],
[Lease Type] as [Sublease Type],
[Leased Area SQM] as [Subleased Area SQM],
[Leased Carpark Count] as [Subleased Carpark Count],
[PML_CODE] as [SubleaseCode],
[Renewal Notice] as [Sublease Renewal Notice],
PML_SEQ as F_Pm_Sublease_SEQ,
TENANT_CODE as [Sublease Tenant Code],
[Tenant Name] as [Sublease Tenant Name],
[Tenant Email] as [Sublease Tenant Email] ,
Contact_Phone as [Sublease Tenant Phone] ,
'Leases' as Source
Resident Leases where LFlag=1;