Subtract number field in command database

[GoBear]

I am trying to build a command temp table to use in Crystal Reports but to link to another table, I need to take a sequence number and subtract 1 so it matches the sequence number in the joined table. I keep getting an error "error in assignment" and "expression evaluation error". I can successfully put a formula in the report and then do a subquery and link on run and sequence but then when the linked sub is blank, I still get the detail. Maybe it is just something with the Sage 300 ODBC that does not allow for this but you can say field=1 in a where clause so it knows that it is a number field. I have tried writing this with and without " around the table.field, putting a bracket around the formula, etc. Thoughts?

Here is the SQL statement:

SELECT CMT_REGISTER__TRANSACTION.Bank_Account, CMT_REGISTER__TRANSACTION.Description, CMT_REGISTER__TRANSACTION.Run, CMT_REGISTER__TRANSACTION.Sequence, CMT_REGISTER__TRANSACTION.Sequence-1 AS NewSeq,
CMT_REGISTER__TRANSACTION.Type, CMT_REGISTER__TRANSACTION.Accounting_Date, CMT_REGISTER__TRANSACTION.Reference_Date, CMT_REGISTER__TRANSACTION.Subtraction, CMM_MASTER__BANK_ACCOUNT.Description
FROM CMT_REGISTER__TRANSACTION INNER JOIN CMM_MASTER__BANK_ACCOUNT ON CMT_REGISTER__TRANSACTION.Bank_Account = CMM_MASTER__BANK_ACCOUNT.Bank_Account
where CMT_REGISTER__TRANSACTION.Batch_Source='Bank Transfers' and
cmt_register__transaction.bank_account = '{?BankFrom}' and cmt_register__transaction.accounting_date >= {?FromDate} and
cmt_register__transaction.accounting_date <= {?ToDate}

 

[kray4660]

I do not know the SAGE system, but you have verified that the CMT_REGISTER__TRANSACTION.Sequence field is a numeric field that you can do a calculation on. It is the only thought I had.

 

[GoBear]

@Kray4660, yes it is a numeric field and if I bring it into Access or whatever, I can do table.field - 1 or +1 without any issues. Should be pretty simple. I do have it calculating in the Crystal report so no data mismatch there. I must be missing something or it cannot be done.

 

[pmax9999]

I am not familiar with Sage either. Are you sure that the error is actually coming from the CMT_REGISTER__TRANSACTION.Sequence-1 line? Is the code accepted if you remove that line? Do you have access to a query tool so that you can test the code and rule out a CR/ODBC issue?

One thing to bear in mind generally when using a command is that linking a command to other tables within CR is inefficient because the linking is done locally rather than at the database server. If you do get the first issue resolved I'd suggest you expand the command so it provides all the columns required, so as to avoid linking to other tables within CR.

Hope this helps.

Cheers
Pete

 

[GoBear]

@pmas9999 Yes I have built the query without the formula and all runs fine. I have even just chosen bank, run, sequence and the formula and it is not happy. I know Crystal well, I know SQL well but sometimes the Sage applications humble me as they are a different animal. I know I can ODBC to the data in Access and make a link server in SQL to the database and do what I need and the formula works perfectly when it is not in a Crystal report. I guess where I now need to point my efforts is to a subreport to pass the variable if it has rows to the main, suppress main if sub does not have rows and then show the bank from and bank to.

I did not mention that for this exercise I must use the same table twice. You have a bank transfer and you say the bank to transfer from and then the bank to transfer to. This is in the same table. Out is one sequence and the To is sequence +1 on a new record. The link is run and sequence +/- 1 depending on whether you are having Out or In as your master. You have to look at the IN because you do not care what other transfers were made from the FROM.

 

[kray4660]

From the SQL I see, it is not using that field for linking or in the where clause. Thus the easiest solution would just do the math in Crystal Reports as a formula. That eliminates the SQL oddity factor.