Firebird 2.5 sql error code = -104 token unknown

Question

I having a problem while I trying to select data from a Firebird database, here is my code:

string SQLCash = "SELECT sum(t2.Cash-Change)" +
            "FROM dbFBHdr t1, dbFBCollection t2" +
            "WHERE t1.Branch = t2.Branch AND" +
            "t1.CashNo = t2.CashNo AND" +
            "t1.CashDate >= '08/10/2018 00:00:00' AND" +
            "t1.CashDate <= '08/10/2018 23:59:59' AND" +
            "t1.Status = 'CLOSED'";
FbCommand cmdCASH = new FbCommand(SQLCash, FbCon);
cmdCASH.ExecuteNonQuery();
FbDataReader readerCASH = cmdCASH.ExecuteReader();
while (readerCASH.Read() == true)
{
   PDC.CASH += (reader["Cash"].ToString());
}
this.txtCash.Text = PDC.CASH;

But with this code, I having an error but I don't know how to solve it.
I attach my error at below:

enter image description here

The error says that you have unknown token `t1`, probably you need to add keyword `as` like this: `FROM dbFBHdr AS t1` — Markiian Benovskyi, Oct 29 '18 at 13:33
Hi Markiian Benovskyi, I had been tried to add as keyword in my sql query but the problem still existed string SQLCash = "SELECT sum(t2.Cash-Change)" + "FROM dbFBHdr as t1, dbFBCollection as t2" + "WHERE t1.Branch = t2.Branch AND" + "t1.CashNo = t2.CashNo AND" + "t1.CashDate >= '08/10/2018 00:00:00' AND" + "t1.CashDate <= '08/10/2018 23:59:59' AND" + "t1.Status = 'CLOSED'"; — Teo Lawrence, Oct 29 '18 at 13:40
Ah, when you are concatenating this with `+` you receive incorrect string, try to write the command in single string without concatenation — Markiian Benovskyi, Oct 29 '18 at 13:44
change your query to: SELECT sum(t2.Cash-Change)" + " FROM dbFBHdr as t1, dbFBCollection as t2" + " WHERE t1.Branch = t2.Branch AND" + " t1.CashNo = t2.CashNo AND" + " t1.CashDate >= '08/10/2018 00:00:00' AND" + " t1.CashDate <= '08/10/2018 23:59:59' AND" + " t1.Status = 'CLOSED. what I did was adding white space between lines. — Long Luong, Oct 29 '18 at 13:53
Hi Thank you ~ I changed my code to {"SELECT sum(t2.Cash-Change) AS Cash FROM dbFBHdr t1, dbFBCollection t2 WHERE t1.Branch = t2.Branch AND t1.CashNo = t2.CashNo AND t1.CashDate >= '08/10/2018 00:00:00' AND t1.CashDate <= '08/10/2018 23:59:59' AND t1.Status = 'CLOSED'"} but another problem error comes is 'Could not find specified column in results: Cash — Teo Lawrence, Oct 29 '18 at 14:41
what is `Change` ? is it `t1.Change` or `t2.Change` ? unqualified columns are shakey, better avoid them — Arioch 'The, Oct 29 '18 at 17:13
`t1.CashDate >= ...` - see `BETWEEN` https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-commons-predicates.html#fblangref25-commons-comppreds and also see `date` type enforcer in that example. But yet much better - use parameters instead, see http://bobby-tables.com/ — Arioch 'The, Oct 29 '18 at 17:20
`t1.CashDate >= '08/10/2018 00:00:00' AND t1.CashDate <= '08/10/2018 23:59:59'` - this condition misses one second every date, such as `08/10/2018 23:59:59.50`. Maybe you better remove time part of that column, assuming your database is `dialect 3` you can have `date` column data type. Alternatively you can go with `t1.CashDate >= date '08/10/2018' AND t1.CashDate < date '09/10/2018'` or even `t1.CashDate >= date '08/10/2018' AND t1.CashDate < (date '08/10/2018')+1`. But yet better - use parameters, not inlining. That, assuming you have an `index` over that `date`/`timestamp` column — Arioch 'The, Oct 29 '18 at 17:32
Assuming your database using SQL Dialect 3, you can just `typecast`the time part out of the window. Granted, this probably would preclude any `index` on that field be used, but OTOH we do not know if you have indexed that field or not. `...AND cast(t1.CashDate as date) = date '08/10/2018' ` See https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-datetime.html and also https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-scalarfuncs.html#fblangref25-functions-casting — Arioch 'The, Oct 29 '18 at 17:35
Easy check would be like `select cast( cast( timestamp '08/10/2018 21:30' as date) as timestamp) from rdb$database` - it should have the time part zeroed out. Or not. — Arioch 'The, Oct 29 '18 at 17:42

score 1 · Answer 1 · answered Oct 29 '18 at 13:46

1

I ran your code inside my project and this is the result

"SELECT sum(t2.Cash-Change)FROM dbFBHdr t1, dbFBCollection t2WHERE t1.Branch = t2.Branch ANDt1.CashNo = t2.CashNo ANDt1.CashDate >= '08/10/2018 00:00:00' ANDt1.CashDate <= '08/10/2018 23:59:59' ANDt1.Status = 'CLOSED'"

as you can see the code above is missing spaces between string addition, either add it in the beginning of each string or at the end of them.

also since you are using multiple lines you can use @ before the string which will allow you to have multiple lines in one string, example

string SQLCash = @"SELECT sum(t2.Cash-Change)
        FROM dbFBHdr t1, dbFBCollection t2
        WHERE t1.Branch = t2.Branch AND
        t1.CashNo = t2.CashNo AND
        t1.CashDate >= '08/10/2018 00:00:00' AND
        t1.CashDate <= '08/10/2018 23:59:59' AND
        t1.Status = 'CLOSED'";

answered Oct 29 '18 at 13:46

Hoshani

746
1
10
27

Hi Hoshani, Thank you ! I changed my code to ["SELECT sum(t2.Cash-Change) AS Cash FROM dbFBHdr t1, dbFBCollection t2 WHERE t1.Branch = t2.Branch AND t1.CashNo = t2.CashNo AND t1.CashDate >= '08/10/2018 00:00:00' AND t1.CashDate <= '08/10/2018 23:59:59' AND t1.Status = 'CLOSED'"] but another problem error comes is 'Could not find specified column in results: Cash' – Teo Lawrence Oct 29 '18 at 13:53
May I know how to comment with a quote ? because my replies is very messy due to I don't know how to quote my codes... – Teo Lawrence Oct 29 '18 at 13:57
the error is pretty self explanatory, it says there is no column with the name 'Cash'. From what I understood in [here](http://forum.lazarus.freepascal.org/index.php?topic=20561.0) you can not have a name with hyphen '-' in it. For editing your reply below there is a link [https://stackoverflow.com/editing-help#comment-formatting](https://stackoverflow.com/editing-help#comment-formatting) – Hoshani Oct 29 '18 at 14:44
Hi @Hoshani , Thanks you ~ I was already change my code to SELECT sum(t2.Cash-Change) AS Cash , Does it suppose will return a column call Cash ? – Teo Lawrence Oct 29 '18 at 14:45
1

I have not used firebird before, but I guess your problem is in the column name itself and using hyphen '-', you might want to change it to underscore '_'. follow this guideline for naming columns in databases [in this post](https://stackoverflow.com/a/7724/10422932) – Hoshani Oct 29 '18 at 14:55
@TeoLawrence I'd suggest trying `CASH`, unquoted object names are uppercased. – Mark Rotteveel Oct 29 '18 at 15:08
On column and other names: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-structure-identifiers.html – Arioch 'The Oct 29 '18 at 17:23
1

@MarkRotteveel it seems he managed to make one column named `"Cash-Change"` Ж8-[...]. So now while he writes in his SQL instructions to the server "`Sum` me column `Cash` from `t1` table subtracted by column `Change` from any table" - that is not what he exactly meant to write, judging by other comments... – Arioch 'The Oct 29 '18 at 17:26

Firebird 2.5 sql error code = -104 token unknown

1 Answers1