| Subject | Re: [firebird-support] Question: RDB$SYSTEM_FLAG = 0 No Results |
|---|---|
| Author | Robert Tulloch |
| Post date | 2019-02-28T17:06:01Z |
Fixed:
GetUniqueStatisticsStatement.Add(FORMAT('select Cast(%s AS varchar(32))AS TABLE_NAME, cast(%s AS varchar(32)) AS INDEX_NAME, cast(RDB$STATISTICS as varchar(32)) as RDB$STATISTICS',[tableName, indexName]));
GetUniqueStatisticsStatement.Add('from rdb$indices where (RDB$SYSTEM_FLAG is null or RDB$SYSTEM_FLAG = 0 )');
GetUniqueStatisticsStatement.Add(FORMAT('AND RDB$INDICES.RDB$INDEX_NAME = %s',[indexName]));
GetUniqueStatisticsStatement.Add('ORDER BY RDB$STATISTICS DESC');
Resulting query
select Cast('ADVERPAY' AS varchar(32))AS TABLE_NAME, cast('EXPIREDATE' AS varchar(32)) AS INDEX_NAME, cast(RDB$STATISTICS as varchar(32)) as RDB$STATISTICS
from rdb$indices where (RDB$SYSTEM_FLAG is null or RDB$SYSTEM_FLAG = 0 )
AND RDB$INDICES.RDB$INDEX_NAME = 'EXPIREDATE'
ORDER BY RDB$STATISTICS DESC
Honing my SQL skills.
Best regards
On 2/28/2019 7:36 AM, Robert Tulloch tultalk@... [firebird-support] wrote:
GetUniqueStatisticsStatement.Add(FORMAT('select Cast(%s AS varchar(32))AS TABLE_NAME, cast(%s AS varchar(32)) AS INDEX_NAME, cast(RDB$STATISTICS as varchar(32)) as RDB$STATISTICS',[tableName, indexName]));
GetUniqueStatisticsStatement.Add('from rdb$indices where (RDB$SYSTEM_FLAG is null or RDB$SYSTEM_FLAG = 0 )');
GetUniqueStatisticsStatement.Add(FORMAT('AND RDB$INDICES.RDB$INDEX_NAME = %s',[indexName]));
GetUniqueStatisticsStatement.Add('ORDER BY RDB$STATISTICS DESC');
Resulting query
select Cast('ADVERPAY' AS varchar(32))AS TABLE_NAME, cast('EXPIREDATE' AS varchar(32)) AS INDEX_NAME, cast(RDB$STATISTICS as varchar(32)) as RDB$STATISTICS
from rdb$indices where (RDB$SYSTEM_FLAG is null or RDB$SYSTEM_FLAG = 0 )
AND RDB$INDICES.RDB$INDEX_NAME = 'EXPIREDATE'
ORDER BY RDB$STATISTICS DESC
Honing my SQL skills.
Best regards
On 2/28/2019 7:36 AM, Robert Tulloch tultalk@... [firebird-support] wrote:
On 2/28/2019 12:54 AM, helebor@... [firebird-support] wrote:
---In firebird-support@yahoogroups.com, <tultalk@...> wrote :
On 2/27/2019 2:44 PM, Robert Tulloch tultalk@... [firebird-support] wrote:
Hi:
I just ran this
select 'MEMPAY1' AS TABLE_NAME, 'M1ID' AS INDEX_NAME, cast(RDB$STATISTICS as varchar(32)) as RDB$STATISTICS from rdb$indices where RDB$SYSTEM_FLAG is null
or RDB$SYSTEM_FLAG = 0 order by RDB$STATISTICS
And it returned 76 "records" all the same table and index but with different values for RDB$STATISTICS
ranging from 0.0001628664467716590 to 1.000000000000000
Over my head. How can there be 76 different values?
You got what you asked for.Tthere are 76 tables and 76 indices, all of which have been returned with the
same name for the table and the index, respectively, due to your forcing a constant on
each. I have no idea why you think you have to do things like this.
HB
Hi:
Thanks for response. I am trying to return the selectivity for that unique table/unique index.
Looking at the tables in the database, there is one table with the unique name and with a unique index.
Running same query on 3 other tables returns 76 records each with the RDB$STATISTICS values.
I)n IB_SQL I get:
ISC ERROR CODE:335544652
ISC ERROR MESSAGE:
multiple rows in singleton select
STATEMENT:
(TApplication).frmSQL.dsqlEdit
When I run the statistics on the whole database, 76 unique records and the same RDB$STATISTICS values.
Obviously something wrong with my statements.
Best Robert