--- In firebird-support@yahoogroups.com, "magni_hbi" <magni@...> 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)

Is the database in use while this is happenning and if so, are you
using a read-committed transaction? If so, perhaps another transaction
deleted a bunch of records and committed during the counts, but that
would not explain why the coalesce worked.

Perhaps the foreign key index has been corrupted. This index is used
to quickly identify the data pages that contain the null fields:

SELECT COUNT(*)
FROM HBI_POS_TRAN
WHERE PARENT_TRNO || '' IS NULL

This will prevent the index from being used (so it will probably take
longer). If it comes up with the right number, then drop the foreign
key constraint and redefine it.

Adam

>
> 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?
>
> Best Regards
> Magni
>