on 03-24-2009 9:59 AM
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
Hi Phil,
When your query runs fine on your local machine then it should either run on the server or if there is an error then it should give you a different error, not the one you specified.
It seems either one of the table in you are using is missing in the database or you have not given the right database information to the server. Please check with that.
Thanks
-Azhar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Azhar,
When connecting via SQLYog I am connecting to the same database that our reports server connects to and using the same username and password combo. I upload loads of reports each day and none of them have ever had this issue, its driving me mad!
I have checked the connection details on the server for the report and they are the same as always. The tables used in the query are all definately in place on the database too.
Oh I have been such a numpty - Although I can say that I didn't write the query - I was just looking into the issue....
One of the left joins is resulting in no data, not even null's so in effect that created table doesn't exist so that is why the issue is being caused.
I'm off to berate my manager now!
Cheers for you help
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.