Hi guys,
I have the following query
/* SELECT FROM [dbo].[@EPARAMTABLE] T0 */
declare @FromDate as DATETIME
declare @ToDate as DATETIME
/* WHERE */
set @fromDate = /* T0.U_fromdate */ '[%0]'
set @toDate = /* T0.U_todate */ '[%1]'
select
'Purchase Invoice' as 'Document Type'
, t0.docdate as 'Document Date'
, t1.acctcode as 'G/L Account'
, t2.acctname as 'G/L Account Description'
, t1.ocrcode as 'Profit Centre Code'
, t3.prcname as 'Profit Centre Description'
, t1.freetxt as 'Freetext'
, '' as 'Transaction Code'
, t1.vatgroup as 'Tax Code'
, t0.docnum as 'Transaction Reference'
--, 0 as 'Debit'
--, 0 as 'Credit'
, t1.linetotal as 'Deb./Cred. (LC)'
, t1.U_eEmpID as 'Employee ID'
, t1.U_eVehicleRegNo as 'Vehicle Reg. No.'
, t1.U_eMaintAgree as 'Maintenance Agreement'
, t1.U_eLeaseAgreement as 'Lease Agreement'
, t1.U_ePropertyCodes as 'Property Code'
, t1.U_eRBS as 'RBS Code'
, t1.U_eE1Links as 'E1 Links'
, t1.U_eTel as 'Telephone Code'
, t1.U_eFax as 'Fax Code'
, t1.U_eDataConn as 'Data Connection Code'
from
opch t0 join pch1 t1 on t0.docentry = t1.docentry
left outer join oact t2 on t2.acctcode = t1.acctcode
left outer join oprc t3 on t3.prccode = t1.ocrcode
where
(T0.DocDate between @FromDate and @ToDate) OR (t2.acctcode between [%2] and [%3])
UNION ALL
select
'Purchase Credit Note'
, t0.docdate as 'Document Date'
, t1.acctcode as 'G/L Account'
, t2.acctname as 'G/L Account Description'
, t1.ocrcode as 'Profit Centre Code'
, t3.prcname as 'Profit Centre Description'
, t1.freetxt as 'Freetext'
, '' as 'Transaction Code'
, t1.vatgroup as 'Tax Code'
, t0.docnum as 'Transaction Reference'
--, 0 as 'Debit'
--, 0 as 'Credit'
, t1.linetotal as 'Row Total exc. VAT'
, t1.U_eEmpID as 'Employee ID'
, t1.U_eVehicleRegNo as 'Vehicle Reg. No.'
, t1.U_eMaintAgree as 'Maintenance Agreement'
, t1.U_eLeaseAgreement as 'Lease Agreement'
, t1.U_ePropertyCodes as 'Property Code'
, t1.U_eRBS as 'RBS Code'
, t1.U_eE1Links as 'E1 Links'
, t1.U_eTel as 'Telephone Code'
, t1.U_eFax as 'Fax Code'
, t1.U_eDataConn as 'Data Connection Code'
from
orpc t0 join rpc1 t1 on t0.docentry = t1.docentry
left outer join oact t2 on t2.acctcode = t1.acctcode
left outer join oprc t3 on t3.prccode = t1.ocrcode
where
(T0.DocDate between @FromDate and @ToDate) OR (t2.acctcode between [%2] and [%3])
UNION ALL
select
'Journal Entry'
, t0.refdate as 'Document Date'
, t1.account as 'G/L Account'
, t2.acctname as 'G/L Account Description'
, t1.profitcode as 'Profit Centre Code'
, t3.prcname as 'Profit Centre Description'
, t0.memo as 'Freetext'
, t0.transcode as 'Transaction Code'
, t1.vatgroup as 'Tax Code'
, t0.transid as 'Transaction Reference'
--, t1.debit
--, t1.credit
, t1.debit - t1.credit as 'Deb./Cred. (LC)'
--, 0 as 'Row Total exc. VAT'
, t1.U_eEmpID as 'Employee ID'
, t1.U_eVehicleRegNo as 'Vehicle Reg. No.'
, t1.U_eMaintAgree as 'Maintenance Agreement'
, t1.U_eLeaseAgr as 'Lease Agreement'
, t1.U_eProperty as 'Property Code'
, t1.U_eRBS as 'RBS Code'
, t1.U_eE1Links as 'E1 Links'
, t1.U_eTel as 'Telephone Code'
, t1.U_eFax as 'Fax Code'
, t1.U_eDataConn as 'Data Connection Code'
from
ojdt t0 join jdt1 t1 on t0.transid = t1.transid
left outer join oact t2 on t2.acctcode = t1.account
left outer join oprc t3 on t3.prccode = t1.profitcode
where
(t0.transtype = 30) and (T0.RefDate between @FromDate and @ToDate)OR (t2.acctcode between [%2] and [%3])
order by
'Document Type'
, t0.docnum
The problem is that it is giving me "No matching records found 'Service Contracts' (OCTR)". when I remove the acctcode part in the WHERE clause, it works fine.
Any idea what it might be please??