Query Manager error

[Former Member]

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??

[Former Member · Accepted solution]

You have to add two more parameters on top of the query just like the date parameters. [%2\] and [%3\] will not work here.

Please close the other duplicate thread of yours.

Thanks,

Gordon

Edited by: Paulo Calado on Jun 12, 2009 5:21 PM