Hi Jason,

that was a bit unlucky from me... I wanted to make the statements a bit better readable by renaming / shortening some of the parts and used STATE accidentaly, but in real statements there is no reserved word used. Here the corrected samples:

------------------------------
select count(ID) as SOMECOUNT, SOMESTATE
from TABLE_A
where (ID > 0) and (SOMESTATE > 0)
group by SOMESTATE
------------------------------
select
    count(TABLE_A.ID) as SUM_ID
from
    TABLE_A
    inner join TABLE_B on TABLE_A.ID = TABLE_B.REF_ID
where
    TABLE_A.ID > 0 and TABLE_B.SOMESTATE = 3
------------------------------
select
    sum(TOTAL) as SUM_TOTAL
from
    TABLE_A
where
    (TABLE_A.ORDER_DATE >= :DATE_FROM) and (TABLE_A.ORDER_DATE < :DATE_TO) and (ID > 0)
------------------------------

Also a small correction regarding the error message itself:
it's not "Field SOMECOUNT not found"
but "Field name 'SOMECOUNT' not found"
etc.

All these statements are valid and work most of the time, but sometimes the same calls lead to the said errors, just to be working fine again 1-2 calls later. There must be a bug somewhere. Not sure what could cause such a behaviour though. Also as I said, it has been working fine with older releases for years now. The problems started with one of the x latest releases. My feeling is with the release Version 5.7.7 [Build 2340], but not sure.

Best regards,
Patrick


---In IBObjects@yahoogroups.com, <supportlist@...> wrote :

This is likely due to using STATE instead of “STATE”.

This is a reserved word that needs special treatment.

I recommend that you rename it to something else.

 

Jason

 

---

From: IBObjects@yahoogroups.com [mailto: IBObjects@yahoogroups.com ]
Sent: Wednesday, May 11, 2016 10:17 AM
To: IBObjects@yahoogroups.com
Subject: [IBO] Random "field not found" errors

 

Hello,

since a few releases I'm getting some "field not found" errors by using a IB_Cursor. It happens randomly, so I can't say exactly when it started. My feeling is with the release Version 5.7.7 [Build 2340], but not sure.

I'm using some IB_Cursors to gather some data for a dashboard: counts, sums etc. There are SQL statements like these, for instance:

----------------
with IB_Cursor1 do begin
    Close;
    SQL.Clear;
    SQL.Add('select count(ID) as SOMECOUNT, STATE');
    SQL.Add('from TABLE_A');
    SQL.Add('where (ID > 0) and (STATE > 0)');
    SQL.Add('group by STATE);
    Open;
end;
----------------
select
    count(TABLE_A.ID) as SUM_ID
from
    TABLE_A
    inner join TABLE_B on TABLE_A.ID = TABLE_B.REF_ID
where
    TABLE_A.ID > 0 and TABLE_B.STATE = 3
----------------
select
    sum(TOTAL) as SUM_TOTAL
from
    TABLE_A
where
    (TABLE_A.ORDER_DATE >= :DATE_FROM) and (TABLE_A.ORDER_DATE < :DATE_TO) and (ID > 0)
----------------

Sometimes when I open that dashboard, I'm getting errors like

"Field SOMECOUNT not found"
or
"Field SUM_ID not found"
or
"Field SUM_TOTAL not found"

When I close the dashboard / the application and open / start it again, if often works with the same data, the same code the same everything. Sometimes it needs 2-3 more attempts, sometimes it works directly at the first attempt / start.

Not idea what is going on. It happens randomly and it happens with the current release as well (Version 5.7.11 [Build 2388]). Never had such problems with older versions.

Any ideas?