[firebird-support] Is it true that old alter index ... inactive scripts are invalid for Firebird 3.0?

[jonatan.laurit...@yahoo.dk [firebird-support]]

Hi!
 

 Sometimes I need to deactive all indices (for some special processing whose 
intermediate results can invalidate constraints) and then reactive them all 
again. Of course, I can do this with DB restore option which does not activate 
indices during restore process. But so far I have always preferred to generate 
scripts inactivation and activation scripts for indices using:

 

 SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;' FROM RDB$INDICES WHERE 
(RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL) and (RDB$FOREIGN_KEY is null); 
 SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;' FROM RDB$INDICES WHERE 
(RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL) and (RDB$FOREIGN_KEY is not 
null); 

 Now I am trying to use this process for Firebird 3.0, but I am always 
receiving:
 unsuccessful metadata update. XXX failed.
action cancelled by trigger (2) to preserve data integrity.
Cannot deactivate index used by an integrity constraint.
 

 As I rememeber then I was able to reactive foreign keys always in the past for 
the previouse Firebird versions, is is really true that I can not do this in 
Firebird 3.0? Or maybe there is some command for inactivating foreign key 
indices and then inactivating primary key indices which have been references by 
foreign keys?
 

 Jonatan

 

[firebird-support] Re: SELECT FIRST N BY GROUP

[pg...@mccolls.co.uk [firebird-support]]

Thanks Dimitri, unfortunately we are using FB 2.5

Re: [firebird-support] SELECT FIRST N BY GROUP

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

ROW_NUMBER, as Dimitry suggests, is available from Firebird 3. The
following query should work regardless of Firebird version:

SELECT s.*
FROM SALARIES s
LEFT JOIN SALARIES s2 on s.EMPID = s2.EMPID and s.EFFDATE < s2.EFFDATE
LEFT JOIN SALARIES s3 on s2.EMPID = s3.EMPID and s2.EFFDATE < s3.EFFDATE
WHERE s3.EMPID IS NULL

If there are any rows without EMPID or EFFDATE, they are ignored by this
query. Note that the query doesn't return the latest two rows, but all rows
for the latest two EFFDATEs for the employee. If you have such duplicates
and want the last two rows only, then you could e.g. do:

SELECT s.*
FROM SALARIES s
LEFT JOIN SALARIES s2 on s.EMPID = s2.EMPID and ( s.EFFDATE < s2.EFFDATE or
( s.EFFDATE = s2.EFFDATE and s.ID < s2.ID ) )
LEFT JOIN SALARIES s3 on s2.EMPID = s3.EMPID and ( s2.EFFDATE < s3.EFFDATE
or ( s2.EFFDATE = s3.EFFDATE and s2.ID < s3.ID ) )
WHERE s3.EMPID IS NULL

HTH,
Set

ons. 7. aug. 2019 kl. 15:53 skrev Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] :

> 07.08.2019 14:26, pg...@mccolls.co.uk [firebird-support] wrote:
> > Is this possible in a single SELECT query
>
>I would try WHERE ROW_NUMBER() OVER (PARTITION BY empid, ORDER BY
> effdate) <= 2.
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>