I keep trying to use the importable reports, I keep getting this error message; any ideas?

Description : [ProvideX][ODBC Driver]Expected lexical element not found: FROM
Number : -2147217900
Source :
Driver Error : 1015
SQL State : 37000

Attached is the code:

SELECT
CAST(ROUND(IIF(acrinv.duedte>=perlist.periodend,IIF((acrinv.amtpad = 0 AND acrinv.dsctkn = 0),acrinv.invttl-acrinv.retain,IIF(ISNULL(PAID.recnum),acrinv.invttl-acrinv.amtpad-acrinv.dsctkn-acrinv.retain,acrinv.invttl - ((NVL(acrinv.amtpad,0)+NVL(acrinv.dsctkn,0)+NVL(acrinv.retain,0))-(NVL(PAID.amtpd,0)+NVL(PAID.discount,0)+NVL(PAID.credit,0))))),0),2) AS DOUBLE) as Current,
CAST(ROUND(IIF(acrinv.duedte>=perlist.periodend-30,IIF(acrinv.duedte<=perlist.periodend-1,IIF((acrinv.amtpad = 0 AND acrinv.dsctkn = 0),acrinv.invttl-acrinv.retain,IIF(ISNULL(PAID.recnum),acrinv.invttl-acrinv.amtpad-acrinv.dsctkn-acrinv.retain,acrinv.invttl - ((NVL(acrinv.amtpad,0)+NVL(acrinv.dsctkn,0)+NVL(acrinv.retain,0))-(NVL(PAID.amtpd,0)+NVL(PAID.discount,0)+NVL(PAID.credit,0))))),0),0),2) AS DOUBLE) as '30days',
CAST(ROUND(IIF(acrinv.duedte>=perlist.periodend-60,IIF(acrinv.duedte<=perlist.periodend-31,IIF((acrinv.amtpad = 0 AND acrinv.dsctkn = 0),acrinv.invttl-acrinv.retain,IIF(ISNULL(PAID.recnum),acrinv.invttl-acrinv.amtpad-acrinv.dsctkn-acrinv.retain,acrinv.invttl - ((NVL(acrinv.amtpad,0)+NVL(acrinv.dsctkn,0)+NVL(acrinv.retain,0))-(NVL(PAID.amtpd,0)+NVL(PAID.discount,0)+NVL(PAID.credit,0))))),0),0),2) AS DOUBLE) as '60days',
CAST(ROUND(IIF(acrinv.duedte>=perlist.periodend-90,IIF(acrinv.duedte<=perlist.periodend-61,IIF((acrinv.amtpad = 0 AND acrinv.dsctkn = 0),acrinv.invttl-acrinv.retain,IIF(ISNULL(PAID.recnum),acrinv.invttl-acrinv.amtpad-acrinv.dsctkn-acrinv.retain,acrinv.invttl - ((NVL(acrinv.amtpad,0)+NVL(acrinv.dsctkn,0)+NVL(acrinv.retain,0))-(NVL(PAID.amtpd,0)+NVL(PAID.discount,0)+NVL(PAID.credit,0))))),0),0),2) AS DOUBLE) as '90days',
CAST(ROUND(IIF(acrinv.duedte>=perlist.periodend-36500,IIF(acrinv.duedte<=perlist.periodend-91,IIF((acrinv.amtpad = 0 AND acrinv.dsctkn = 0),acrinv.invttl-acrinv.retain,IIF(ISNULL(PAID.recnum),acrinv.invttl-acrinv.amtpad-acrinv.dsctkn-acrinv.retain,acrinv.invttl - ((NVL(acrinv.amtpad,0)+NVL(acrinv.dsctkn,0)+NVL(acrinv.retain,0))-(NVL(PAID.amtpd,0)+NVL(PAID.discount,0)+NVL(PAID.credit,0))))),0),0),2) AS DOUBLE) as '90+plusdays',
CAST(ROUND(IIF((acrinv.amtpad = 0 AND acrinv.dsctkn = 0),acrinv.invttl,IIF(ISNULL(PAID.recnum),acrinv.invttl-acrinv.amtpad-acrinv.dsctkn,acrinv.invttl - ((NVL(acrinv.amtpad,0)+NVL(acrinv.dsctkn,0))-(NVL(PAID.amtpd,0)+NVL(PAID.discount,0)+NVL(PAID.credit,0))))),2) as DOUBLE) as Balance,
CAST(ROUND(IIF((acrinv.amtpad = 0 AND acrinv.dsctkn = 0),acrinv.invttl-acrinv.retain,IIF(ISNULL(PAID.recnum),acrinv.invttl-acrinv.amtpad-acrinv.dsctkn-acrinv.retain,acrinv.invttl - ((NVL(acrinv.amtpad,0)+NVL(acrinv.dsctkn,0)+NVL(acrinv.retain,0))-(NVL(PAID.amtpd,0)+NVL(PAID.discount,0)+NVL(PAID.credit,0))))),2) as DOUBLE) as TotalDue,
reccln.addrs1 as Address1,
reccln.addrs2 as Address2,
clncnt.cllphn as CellPhone,
reccln.ctynme as CityName,
PAID.amtpd as Payment,
TRANSFORM(reccln.clnnme,'@T') as ClientName,
TRANSFORM(reccln.clnnme,'@T')+' - '+ CAST(reccln.recnum AS VARCHAR (10)) as ClientNameNumber,
reccln.recnum as ClientNumber,
RTRIM(CAST(reccln.recnum AS VARCHAR (10)))+' - '+TRANSFORM(reccln.clnnme,'@T') as ClientNumberName,
acrinv.pchord as orderNo,
TRANSFORM(reccln.shtnme,'@T') as ClientShortName,
clntyp.typnme as ClientType,
reccln.clntyp as ClientTypeNumber,
TRANSFORM(cmpany.cmpnme,'@T') as CompanyName,
TRANSFORM(clncnt.cntnme,'@T') as Contact,
TRANSFORM(acrinv.dscrpt,'@T') as Description,
acrinv.dsctkn as DiscCredit,
PAID.discount as Discount,
TRANSFORM(acrinv.dscdte,'@D') as DiscountDate,
TRANSFORM(acrinv.duedte,'@D') as DueDate,
acrinv.vodrec as EditVoid,
TRANSFORM(clncnt.e_mail,'@T') as Email,
clncnt.phnext as Extension,
clncnt.faxnum as Faxnumber,
acrinv.gstamt as GST,
acrinv.hldbll as HBBilled,
acrinv.hldrem as HBRemain,
acrinv.hldamt as HoldBack,
acrinv.hstamt as HST,
acrinv.invamt as InvoiceAmount,
TRANSFORM(acrinv.invdte,'@D') as InvoiceDate,
acrinv.invnum as InvoiceNumber,
acrinv.invttl as InvoiceTotal,
RTRIM(CAST(ICASE(acrinv.invtyp =1,"Contract",
acrinv.invtyp =2,"Memo",
acrinv.invtyp) as Varchar(10))) as InvoiceType,
TRANSFORM(actrec.jobnme,'@T') as JobName,
acrinv.jobnum as JobNumber,
TRANSFORM(actrec.jobnme,'@T')+' - '+ RTRIM(CAST(acrinv.jobnum as VARCHAR(10))) as JobNameNumber,
RTRIM(CAST(acrinv.jobnum as VARCHAR(10)))+' - '+TRANSFORM(actrec.jobnme,'@T') as JobNumberName,
TRANSFORM(actrec.shtnme,'@T') as JobShortName,
jobtyp.typnme as JobType,
actrec.jobtyp as JobTypeNumber,
acrinv.invnet as NetDue,
clncnt.othphn as OtherPhone,
acrinv.amtpad as PaidAmount,
acrinv.actper as Period,
acrinv.phsnum as PhaseNumber,
RTRIM(clncnt.phnnum) as PhoneNumber,
acrinv.postyr as Postingyear,
acrinv.pstamt as PST,
acrinv.recnum as RecordNumber,
RTRIM(acrinv.refnum)as Reference,
acrinv.retain as Retained,
acrinv.slstax as SalesTax,
perlist.periodend as SelectedPerDate,
perlist.period as SelectedPeriod,
reccln.state_ as State,
CAST(ICASE(acrinv.status=1,"Open",
 acrinv.status=2,"Review",
 acrinv.status=3,"Dispute",
 acrinv.status=4,"Paid",
 acrinv.status=5,"Void",
 acrinv.status=6,"Quote",
 acrinv.status=7,"WorkOrder",
 acrinv.status=8,"Completed",
 acrinv.status=9,"Route",
 acrinv.status=10,"Contract",
 acrinv.status) as Varchar(10)) as Status,
acrinv.subttl as Total,
acrinv.ttlpad as TotalPaid,
reccln.zipcde as ZipCode

FROM acrinv
LEFT JOIN jobphs
 ON acrinv.phsnum = jobphs.phsnum and acrinv.jobnum = jobphs.recnum
LEFT JOIN (Select acrpmt.recnum
             ,SUM(NVL(acrpmt.amount,0)) as amtpd
             ,SUM(NVL(acrpmt.dsctkn,0)) as discount
             ,SUM(NVL(acrpmt.aplcrd,0)) as Credit
           FROM acrpmt
           INNER JOIN lgrset
           ON lgrset.fscyrd = lgrset.fscyrd
           GROUP by acrpmt.recnum
           WHERE ((NVL(acrpmt.actper,0) > @AGEPER@  AND acrpmt.postyr= Year(lgrset.fscyrd)) OR (acrpmt.postyr> Year(lgrset.fscyrd)))
            ) as PAID
 ON acrinv.recnum = PAID.recnum

LEFT JOIN actrec
  ON acrinv.jobnum = actrec.recnum
LEFT JOIN jobtyp
 ON actrec.jobtyp = jobtyp.recnum
LEFT JOIN reccln
 ON actrec.clnnum = reccln.recnum
LEFT JOIN clntyp
  ON reccln.clntyp = clntyp.recnum
LEFT JOIN clncnt
  ON reccln.recnum = clncnt.recnum and linnum = 1
INNER JOIN cmpany
  ON cmpany.cmpnme = cmpany.cmpnme
LEFT JOIN (Select prdlist.period , prdlist.PeriodEnd, prdlist.fscyear  
     From (
SELECT
lgrset.fscyrd  as PeriodEnd,
12 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, 0) - DAY(GOMONTH(lgrset.fscyrd, 0)) as PeriodEnd,
11 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -1) - DAY(GOMONTH(lgrset.fscyrd, -1)) as PeriodEnd,
10 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -2) - DAY(GOMONTH(lgrset.fscyrd, -2)) as PeriodEnd,
9 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -3) - DAY(GOMONTH(lgrset.fscyrd, -3)) as PeriodEnd,
8 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -4) - DAY(GOMONTH(lgrset.fscyrd, -4)) as PeriodEnd,
7 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -5) - DAY(GOMONTH(lgrset.fscyrd, -5)) as PeriodEnd,
6 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -6) - DAY(GOMONTH(lgrset.fscyrd, -6))  as PeriodEnd,
5 as Period,
Year(lgrset.fscyrd)  as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -7) - DAY(GOMONTH(lgrset.fscyrd, -7)) as PeriodEnd,
4 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -8) - DAY(GOMONTH(lgrset.fscyrd, -8))  as PeriodEnd,
3 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -9) - DAY(GOMONTH(lgrset.fscyrd, -9)) as PeriodEnd,
2 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -10) - DAY(GOMONTH(lgrset.fscyrd, -10)) as PeriodEnd,
1 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset

UNION ALL

SELECT
GOMONTH(lgrset.fscyrd, -11) - DAY(GOMONTH(lgrset.fscyrd, -11)) as PeriodEnd,
0 as Period,
Year(lgrset.fscyrd) as fscyear
FROM lgrset
      ) as prdlist
where prdlist.period = @AGEPER@
 ) as perlist

ON perlist.fscyear = perlist.fscyear

WHERE
( ( (acrinv.actper <= @AGEPER@  AND acrinv.postyr = perlist.fscyear) OR (acrinv.postyr < perlist.fscyear) )
AND RTRIM(CAST(ICASE(acrinv.status=1,"Open",
 acrinv.status=2,"Review",
 acrinv.status=3,"Dispute",
 acrinv.status=4,"Paid",
 acrinv.status=5,"Void",
 acrinv.status=6,"Quote",
 acrinv.status=7,"WorkOrder",
 acrinv.status=8,"Completed",
 acrinv.status=9,"Route",
 acrinv.status=10,"Contract",
 acrinv.status) as Varchar(10))) In ('Open','Review','Dispute','Paid')

AND  (CAST(ROUND(IIF((acrinv.amtpad = 0 AND acrinv.dsctkn = 0),acrinv.invttl,IIF(ISNULL(PAID.recnum),acrinv.invttl-acrinv.amtpad-acrinv.dsctkn,acrinv.invttl - ((NVL(acrinv.amtpad,0)+NVL(acrinv.dsctkn,0))-(NVL(PAID.amtpd,0)+NVL(PAID.discount,0)+NVL(PAID.credit,0))))),2) as DOUBLE)) <>0
)
ORDER BY
acrinv.recnum , acrinv.jobnum , acrinv.invnum , acrinv.invdte

Views: 737

Replies to This Discussion

What accounting system is this for?

What report is this?

Attached. I'm not having any luck getting it to accept the report even when I alter the error sections. Error also attached.  

Attachments:

Is this version 2015? Tends to not work on lower versions. 

Yes this is Sage Intelligence Connector 2015. bummer. :(

what version of SQL?  Did you use the reports from the Report Utility or from the free templates?

Which Sage Software are you using? I see you tried to download Sage 100 & Sage 50 reports. And I am guessing that is why you are getting the errors, the reports are specific to Sage products so will only work on the Sage Product that it has been created for.

I would recommend looking at the Report Utility as we no longer upload reports to this site. All additional reports can be accessed via the Report Utility.

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service