Fixed - Spreadsheet reports - MSSQL Errors

Fixed - MSSQL Errors - Spreadsheet reports

Transactions => Per period

Incorrect syntax near '|'.

Still replicate error after changing || to {concat}

Current SQL

case Mainaccount.WACCOUNTTYPEID when 0 then

'G' when 1 then 'D' when 2 then 'C' when 3 then

'B' else 'T' end || coalesce(Mainaccount.SMAINACCOUNTCODE,'') || coalesce(Mainaccount.SSUBACCOUNTCODE,'') TRN_34,

Mainaccount.SDESCRIPTION TRN_1213,

Changed to

case Mainaccount.WACCOUNTTYPEID when 0 then

'G' when 1 then 'D' when 2 then 'C' when 3 then

'B' else 'T' end ,'') {concat} coalesce(Mainaccount.SMAINACCOUNTCODE,'') {concat} coalesce(Mainaccount.SSUBACCOUNTCODE,'') TRN_34,

Mainaccount.SDESCRIPTION TRN_1213,

Sales analysis => Sales per period

'Extract' is not a recognized built-in function name.

Current SQL

select Extract(year from ddate) TRN_901022_Year,

Extract(month from ddate) TRN_901019_MONTH ,

sum(fdocamount) TRN_1866_IncAmt,

sum(FTAXAMOUNT) TRN_1138_TaxAmt,

sum(fdocamount-FTAXAMOUNT) TRN_1200_ExcAmt

from dochead where

<@DDATE@>

group by Extract(year from ddate),Extract(month from ddate)

Changed to

select {if interbase}Extract(year from ddate){else}Year(ddate) {endif} TRN_901022_Year,

{if interbase}Extract(month from ddate){else}Month(ddate) {endif} TRN_901019_MONTH ,

sum(fdocamount) TRN_1866_IncAmt,

sum(FTAXAMOUNT) TRN_1138_TaxAmt,

sum(fdocamount-FTAXAMOUNT) TRN_1200_ExcAmt

from dochead where

<@DDATE@>

group by {if interbase}Extract(year from ddate),Extract(month from ddate)){else}Year(ddate),Month(ddate) {endif}

Produced Firebird Error

Location : plug_ins\reports\excelreports\TRN_85\TRN_906122\trn_1296.sql

Firebird error

Dynamic SQL Error

SQL error code = -104

Token unknown - line 8, column 60

)

NOTE - before we fixed for MSSQL = Works + Populates the Spreadsheet in MSSQL Database

Changed to

select {if interbase}Extract(year from ddate){else}Year(ddate) {endif} TRN_901022_Year,

{if interbase}Extract(month from ddate){else}Month(ddate) {endif} TRN_901019_MONTH ,

sum(fdocamount) TRN_1866_IncAmt,

sum(FTAXAMOUNT) TRN_1138_TaxAmt,

sum(fdocamount-FTAXAMOUNT) TRN_1200_ExcAmt

from dochead where

<@DDATE@>

group by {if interbase}Extract(year from ddate),Extract(month from ddate){else}Year(ddate),Month(ddate) {endif}

FIXED = ASCII_CHAR Changed to {char}

Spreadsheet reports	MSSQL Error
General Ledger => Budget => Column balances => Reporting group1	'ASCII_CHAR' is not a recognized built-in function name.
General Ledger => Budget => Column balances => This year	'ASCII_CHAR' is not a recognized built-in function name.
General Ledger => Column balances => Reporting group1	'ASCII_CHAR' is not a recognized built-in function name.
General Ledger => Column balances => This year	ASCII_CHAR' is not a recognized built-in function name.

Used Notepad++ to change the SQL in plug_ins\ reports \ excelreports folder

Seems to fix this error

OK done some exports from firebird - Accounts + Debtors + Creditors = OK Stock Import error to MSSQL database - See the MSSQL in the error message - Similar to the one in a normal firebird database

Error 3violation of FOREIGN KEY constraint "FK_DOCLINE_STOCK" on table "DOCLINE"

Foreign key reference target does not exist normal firebird message

spreadsheet import (Export Save as xlxs) i source database and import in target database) works to import stock - After that "3violation of FOREIGN KEY constraint "FK_DOCLINE_STOCK" on table "DOCLINE"

is not replicated after that - could make txt exports and imports

"ASCII_CHAR" (108 hits in 4 files of 30 searched)

C:\TCASH5-2\plug_ins\reports\excelreports\TRN_32\TRN_1027\TRN_3106\TRN_1323\trn_3106.sql (28 hits)

Line 4: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUP' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",1,0,0,0)")' TRN_185,

Line 5: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPOPENBALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",14,1,0,0)")' Period0,

Line 6: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",14,1,0,0)")' Period1,

Line 7: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",15,1,0,0)")' Period2,

Line 8: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",16,1,0,0)")' Period3,

Line 9: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",17,1,0,0)")' Period4,

Line 10: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",18,1,0,0)")' Period5,

Line 11: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",19,1,0,0)")' Period6,

Line 12: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",20,1,0,0)")' Period7,

Line 13: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",21,1,0,0)")' Period8,

Line 14: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",22,1,0,0)")' Period9,

Line 15: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",23,1,0,0)")' Period10,

Line 16: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",24,1,0,0)")' Period11,

Line 17: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",25,1,0,0)")' Period12

C:\TCASH5-2\plug_ins\reports\excelreports\TRN_32\TRN_1027\TRN_3106\TRN_603\trn_3106.sql (26 hits)

Line 5: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTOPENBALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",14,1,0,0)")' Period0,

Line 6: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",14,1,0,0)")' Period1,

Line 7: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",15,1,0,0)")' Period2,

Line 8: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",16,1,0,0)")' Period3,

Line 9: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",17,1,0,0)")' Period4,

Line 10: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",18,1,0,0)")' Period5,

Line 11: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",19,1,0,0)")' Period6,

Line 12: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",20,1,0,0)")' Period7,

Line 13: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",21,1,0,0)")' Period8,

Line 14: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",22,1,0,0)")' Period9,

Line 15: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",23,1,0,0)")' Period10,

Line 16: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",24,1,0,0)")' Period11,

Line 17: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",25,1,0,0)")' Period12

C:\TCASH5-2\plug_ins\reports\excelreports\TRN_32\TRN_3106\TRN_1323\trn_3106.sql (28 hits)

Line 4: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUP' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",0,0,0,0)")' TRN_185,

Line 5: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPOPENBALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",14,0,0,0)")' Period0,

Line 6: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",14,0,0,0)")' Period1,

Line 7: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",15,0,0,0)")' Period2,

Line 8: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",16,0,0,0)")' Period3,

Line 9: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",17,0,0,0)")' Period4,

Line 10: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",18,0,0,0)")' Period5,

Line 11: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",19,0,0,0)")' Period6,

Line 12: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",20,0,0,0)")' Period7,

Line 13: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",21,0,0,0)")' Period8,

Line 14: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",22,0,0,0)")' Period9,

Line 15: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",23,0,0,0)")' Period10,

Line 16: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",24,0,0,0)")' Period11,

Line 17: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETGROUPTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",25,0,0,0)")' Period12

C:\TCASH5-2\plug_ins\reports\excelreports\TRN_32\TRN_3106\TRN_603\trn_3106.sql (26 hits)

Line 5: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTOPENBALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",14,0,0,0)")' Period0,

Line 6: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",14,0,0,0)")' Period1,

Line 7: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",15,0,0,0)")' Period2,

Line 8: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",16,0,0,0)")' Period3,

Line 9: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",17,0,0,0)")' Period4,

Line 10: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",18,0,0,0)")' Period5,

Line 11: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",19,0,0,0)")' Period6,

Line 12: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",20,0,0,0)")' Period7,

Line 13: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",21,0,0,0)")' Period8,

Line 14: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",22,0,0,0)")' Period9,

Line 15: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",23,0,0,0)")' Period10,

Line 16: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",24,0,0,0)")' Period11,

Line 17: '=OSFEVALUATE("CUSTOM(' {concat} ASCII_CHAR(39) {concat} 'GETACCOUNTTOTALPERIOD' {concat} ASCII_CHAR(39) {concat}'," &A<@LINENR@>&",25,0,0,0)")' Period12

Search "{char}" (0 hits in 0 files of 30 searched)

TurboCASH 5.2 - (453) - 2021 - © Dan van Zyl - Special thanks to Pieter Valentijn (Delphi Dreams) and Philip Copeman (TurboCASH)