the following is the sql code used to run the project. If i run this on management studio, it returns 771 rows of data. However running it on goanywhere results in 0 rows of data generated. (is there certain sql commands that goanywhere does not handle like cross apply?
Code: Select allselect Value, Description
into #CyGlobalPayCodeSetXHR2
from LinkSmartMaster.SmartMaster.dbo.CyGlobalPayCodeSet
where Codeset = 'XHR2'
select Value, Description
into #CyGlobalPayCodeSetHR59
FROM LinkSmartMaster.SmartMaster.dbo.CyGlobalPayCodeSet cs59
where CODESET LIKE 'HR59'
select Social, SchoolKey, Subject, Fte, SchoolCode, row_number()
over (partition by Social order by Fte desc) rownumber
into #TempRepAssign
from RepAssign
--delete from #TempRepAssign where rownumber <> 1
--select * from #TempRepAssign
select Social, SchoolKey, Subject, Fte, SchoolCode, rownumber
into #TempRepAssign2
from #TempRepAssign
where rownumber = 1
CREATE TABLE #TempRepTable
(
EmployeeNumber char(10) NULL,
Subject char(05) NULL,
Fte decimal(5,2) NULL,
RepSchoolCode char(10) NULL,
RepSubjectAssign char(50) NULL
);
insert into #TempRepTable (EmployeeNumber, Subject, Fte,
RepSchoolCode, RepSubjectAssign)
select ra.Social, ra.Subject, ra.Fte, ra.SchoolCode,
/*use subselect for this field to get into paycodeset*/
(select Description from #CyGlobalPayCodeSetXHR2 cs02
where ra.Subject = cs02.Value) RepSubjectAssign
--cs02.Description
from #TempRepAssign2 ra
UPDATE #TempRepTable
-- take out ',' from RepSubjectAssign
set RepSubjectAssign = replace(RepSubjectAssign, ',', '')
-- select * from #TempRepTable
---------------------------------------
CREATE TABLE #TempEndorsements
(
CredentialIdent int NULL,
Endorsements varchar(MAX) NULL
);
insert into #TempEndorsements (CredentialIdent, Endorsements)
select ec.CredentialIdent, edx.list
from EmpCredentials ec
--from #EmpCredentials ec
cross apply (
select ee.Endorsement + ','
from EmpEndorsements ee
where ec.CredentialIdent = ee.CredentialIdent
order by DateIssued desc, REPOverride asc
for XML path('') ) edx (list)
UPDATE #TempEndorsements
-- take out ',' from Endorsements
set Endorsements = replace(Endorsements, ',', '/')
--select * from #TempEndorsements
--------------------------------------
CREATE TABLE #EmpCredentials
( CredentialIdent int not null,
EmployeeNumber char(10) NULL,
CertificateNumber char(15) NULL,
CertificateTypeCode char(4) NULL,
RepOverride char(1) NULL,
DateIssued smalldatetime NULL,
DateExpired smalldatetime NULL,
Endorsements varchar(MAX) NULL
)
insert into #EmpCredentials (CredentialIdent, EmployeeNumber,
CertificateNumber, CertificateTypeCode, RepOverride,
DateIssued, DateExpired, Endorsements)
select --*
ec.CredentialIdent, ec.EmployeeNumber, ec.CertificateNumber,
ec.CertificateTypeCode,
ec.RepOverride, ec.DateIssued, ec.DateExpired, ee.Endorsements
from EmpCredentials ec
left join #TempEndorsements ee
on ec.CredentialIdent = ee.CredentialIdent
WHERE RepOverride IN ('R', 'S')
-------------------------------------------above----- certificate retrieval
CREATE TABLE #TempEmpCredentials
(
EmployeeNumber char(10) NULL,
CertificateNumber char(15) NULL,
CertificateTypeCode char(4) NULL,
RepOverride char(1) NULL,
DateIssued smalldatetime NULL,
DateExpired smalldatetime NULL,
CertificateTypeDesc char(50) NULL,
Endorsements varchar(MAX) NULL
);
insert into #TempEmpCredentials (EmployeeNumber, CertificateNumber,
CertificateTypeCode, RepOverride, DateIssued,
DateExpired, CertificateTypeDesc, Endorsements )
SELECT ec.EmployeeNumber, ec.CertificateNumber,
ec.CertificateTypeCode, ec.RepOverride, ec.DateIssued,
ec.DateExpired,
/*use subselect for this field to get into paycodeset*/
(select Description from #CyGlobalPayCodeSetHR59 cs59
where ec.CertificateTypeCode = cs59.Value) RepSubjectAssign,
--cs59.Description,
ec.Endorsements
from #EmpCredentials ec
--left join #CyGlobalPayCodeSetHR59 cs59
-- ON ec.CertificateTypeCode = cs59.Value
UPDATE #TempEmpCredentials
-- take out ',' from CertificateTypeDesc
set CertificateTypeDesc = replace(CertificateTypeDesc, ',', '')
-- select * from #TempEmpCredentials
------------------------------------------------------
select ed.EmployeeNumber, ed.Lastname, ed.FirstName,
pc.Description as CheckLocDesc,
ej.JobTitle,
tr.RepSubjectAssign,
ec.CertificateTypeDesc,
ec.Endorsements,
convert(decimal(8,4), ROUND(ex.RateValue4 * .01, 4)) AS RateValue4,
convert(decimal(8,4), ROUND(ex.RateValue5 * .01, 4)) AS RateValue5,
ed.UnionCode
from EmpDemo ED
left join dbo.EmpJobAssignment ej
on ed.EmployeeNumber = ej.EmployeeNumber
and ej.currentflag = '1'
LEFT join #TempRepTable tr
on ed.EmployeeNumber = tr.EmployeeNumber
inner join #TempEmpCredentials ec
on ed.EmployeeNumber = ec.EmployeeNumber
LEFT join dbo.EmpExtraNumeric ex
on ed.EmployeeNumber = ex.EmployeeNumber
AND ex.RateValue4 > 0
left JOIN CyPayCodeset pc
ON ed.CheckLocation = pc.Value
AND pc.codeset = 'BARL'
INNER JOIN EmpHire eh ON ed.EmployeeNumber = eh.EmployeeNumber and eh.CurrentFlag = 1
and SUBSTRING(eh.ResultingStatusCode,1,1) not in ('1', '5', '9')
-- and SUBSTRING(eh.ResultingStatusCode,1,1) = '0'
where ed.UnionCode in ('0A', '0D', '0E', '0K') and
RateValue4 is not NULL
order by ex.RateValue4 DESC, ex.RateValue5
GO
the following is the log report: and results from goanywhere are attached.
1/25/17 3:27:32 PM INFO Start Date and Time: 1/25/17 3:27:32 PM
1/25/17 3:27:32 PM INFO Job Number: 1373818465599
1/25/17 3:27:32 PM INFO Project Name: /Finance/TEA Placement Staffing Detail WA Smart Export
1/25/17 3:27:32 PM INFO Submitted By: stimetb
1/25/17 3:27:32 PM INFO Submitted From: Administrator UI
1/25/17 3:27:32 PM INFO GoAnywhere 5.4.1 running on Windows Server 2012 R2 6.3 (amd64)
1/25/17 3:27:32 PM INFO Executing project 'TEA Placement Staffing Detail WA Smart Export'
1/25/17 3:27:32 PM INFO Project location: C:\Program Files\Linoma Software\GoAnywhere\userdata\projects\Finance\TEA Placement Staffing Detail WA Smart Export.xml
1/25/17 3:27:32 PM INFO Executing module 'Main'
1/25/17 3:27:32 PM INFO Executing task 'sql 1.0 (Connect to DB)'
1/25/17 3:27:32 PM INFO Executing sub-task 'query'
1/25/17 3:27:32 PM INFO Executing statement - exec spxLocal_TEAPlacementStaffingDetails
1/25/17 3:27:33 PM INFO Query completed successfully and 0 row(s) were affected
1/25/17 3:27:33 PM INFO Finished sub-task 'query'
1/25/17 3:27:33 PM INFO Finished task 'sql 1.0 (Connect to DB)'
1/25/17 3:27:33 PM INFO Executing task 'writeCSV 1.0 (Convert TEA Details to CSV)'
1/25/17 3:27:33 PM ERROR [9000 - Convert TEA Details to CSV] Invalid input. Input must be of type RowSet. Full stack trace written to '1373818465599_error_1.log'
1/25/17 3:27:33 PM INFO Finished project 'TEA Placement Staffing Detail WA Smart Export'
1/25/17 3:27:33 PM ERROR [9000 - Convert TEA Details to CSV] Invalid input. Input must be of type RowSet.
1/25/17 3:27:33 PM INFO End Date and Time: 1/25/17 3:27:33 PM
