Hi guys,
I keep getting the above error when attempting to run a report within our Crystal Server but I do not have any issues when running the report through SQLYog or through Crystal Reports 10 on my own PC (where I obviously create all the reports before uploading to the server).
Does anyone have any ideas? We use Crystal Reports 10.
This is the SQL query being used:
select
distinct b.branchId
, branchName
, a.agentId
, Case when (concat(ntt.title, ' ', ndd.firstName, ' ', ndd.lastname)) = (CONCAT(nt.title,' ',nd.firstname,' ',nd.lastname))
then concat(ntt.title, ' ', ndd.firstName, ' ', ndd.lastname, ' ', '(Manager)')
else (concat(ntt.title, ' ', ndd.firstName, ' ', ndd.lastname)) end as AgentName
, b.creDate as createdDate
, b.creUser as CreatedBy
, CONCAT(nt.title,' ',nd.firstname,' ',nd.lastname) as agent
, branchRegion, label as Status, b.TAMPayableReference
, ifnull(i.authorized,'N') as Investment
, ifnull(m.authorized,'N') as Mortgage
, ifnull(p.authorized,'N') as Protection
, ifnull(pers.authorized,'N') as PersIn
, ifnull(ci.authorized,'N') as CommercialInsurance
, ifnull(pmi.authorized,'N') as PMI
, ifnull(lm.authorized,'N') as LifetimeMortgage
, ifnull(ltc.authorized,'N') as LongTermCare
, ifnull(op.authorized,'N') as OccupationalPension
, ifnull(cb.authorized,'N') as ComplianceBox
, ifnull(tb.authorized,'N') as TrainingBox
, ifnull(hhb.authorized,'N') as HouseholdBox
, ifnull(conb.authorized,'N') as ConveyancingBox
, address1
, address2
, address3
, address4
, address5
, postcode
, telephone
, mobilePhone
, fsaRegistrationNumber
from branch b
Left Join Agent a on b.branchId = a.branchId
Left Join Personal_details pd on a.personalDetailsId = pd.personalDetailsId
Left Join Name_Details ndd on pd.nameDetailsId = ndd.nameDetailsId
Left Join Name_title ntt on ndd.title = ntt.code
LEFT JOIN BRANCH_REGION br on b.regionId = br.branchRegionId
LEFT JOIN NAMED_ADDRESS na on b.primaryContactId = na.namedAddressId
LEFT JOIN ADDRESS addr on na.addressId = addr.addressId
LEFT JOIN NAME_DETAILS nd on na.nameDetailsId = nd.nameDetailsId
LEFT JOIN NAME_TITLE nt on nd.title = nt.code
LEFT JOIN COMMUNICATION_DETAILS cd on na.communicationDetailsId = cd.communicationDetailsId
LEFT JOIN LABELS l on b.branchStatusTypeID = l.code
left join
(select branchId, authorized
from branch_authorisations
where category = 'Investment') i
on b.branchId = i.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'Mortgage') m
on b.branchId = m.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'Protection') p
on b.branchId = p.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'PersonalInsurance') pers
on b.branchId = pers.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'CommercialInsurance') ci
on b.branchId = ci.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'PMI') pmi
on b.branchId = pmi.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'LifetimeMortgage') lm
on b.branchId = lm.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'LongTermCare') ltc
on b.branchId = ltc.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'OccupationalPension') op
on b.branchId = op.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'ComplianceBox') cb
on b.branchId = cb.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'TrainingBox') tb
on b.branchId = tb.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'HouseholdBox') hhb
on b.branchId = hhb.branchId
left join
(select branchId, authorized
from branch_authorisations
where category = 'ConveyancingBox') conb
on b.branchId = conb.branchId
where b.deldate is null and labelKey = 'statusType'
and (branchType = 'Authorised' or branchType = 'FS:BOX')
and (label = 'Active' or label = 'Suspended')
and b.branchId in (select distinct branchId
from branch_authorisations ba
where authorized = 'Y'
and ba.dateAuthorised between '2009-01-01' and '2009-02-01'
and branchId not in
(select distinct branchId
from branch_authorisations ba
where authorized = 'Y'
and ba.dateAuthorised < '2009-01-01'))
Order by BranchName, agentName