At 05:49 AM 4/05/2006, you wrote:

>Hi all
>
>I'm a bit confused about these results
>
>SELECT COUNT(*) FROM HBI_POS_TRAN = 681385
>SELECT COUNT(*) FROM HBI_POS_TRAN WHERE PARENT_TRNO IS NULL = 681280
>SELECT COUNT(*) FROM HBI_POS_TRAN WHERE PARENT_TRNO IS NOT NULL = 10
>681385 - (681280 + 10) = 95
>I Seem to be missing 95 Records (or they seem to have PARENT_TRNO that
>is neither Null nor Not null)
>
>SELECT COUNT(*) FROM HBI_POS_TRAN WHERE (COALESCE(PARENT_TRNO,0) = 0)
>= 681375 (This seems to be correct)
>
>This is the table:
>CREATE TABLE HBI_POS_TRAN (
> TRNO VARCHAR(20) NOT NULL, /**/
> STR_ID INTEGER NOT NULL,
> WRKST_ID INTEGER NOT NULL,
> OPER_ID VARCHAR(24) NOT NULL COLLATE IS_IS,
> PARENT_TRNO VARCHAR(20),
> BSNS_DT DATE NOT NULL,
> TRAN_ID VARCHAR(20),
> TRAN_BEGIN_DTTM TIME_STAMP NOT NULL,
> TRAN_END_DTTM TIME_STAMP,
> TRAN_FINISH_DTTM TIME_STAMP,
> TRAIN_FG BOOLEAN,
> APP_INFO BLOB SUB_TYPE 0 SEGMENT SIZE 80,
> WRKST_Z INTEGER,
> OPER_Z INTEGER,
> WRKSTSHIFT_Z INTEGER,
> PRINT_CNT INTEGER,
> HBI_COMMIT_CD INTEGER,
> HBI_COMMIT_MSG VARCHAR(255) COLLATE IS_IS,
> HBI_TR_BAL DECIMAL(8,3),
> TRANS_CD INTEGER,
> EXT_TRNO VARCHAR(20),
> CONSTRAINT PKCPOS_TRAN PRIMARY KEY (TRNO)
>);
>ALTER TABLE HBI_POS_TRAN ADD CONSTRAINT FK3POS_TRAN FOREIGN KEY (
>PARENT_TRNO ) REFERENCES HBI_POS_TRAN ( TRNO );
>
>The database file is 2.5Gb and has been recently Backed up and
>restored and checked with "IBFirstAID Diagnostician"
>I'm running Firebird SuperServer 1.5.3.4870 on WinXP sp2
>
>Any ideas?

Are you running these queries in different transactions? The ones
giving the "right" answers can see the 95 newest rows, while those
giving the wrong answers started after the 95 were committed (or the
transaction that has added the new rows hasn't committed them yet,
even though it can count them).

./heLen