Re: [firebird-support] Wrong sort results
On 21/08/15 14:17, 'Bogdan' bog...@mordicom.si [firebird-support] wrote: > order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) > DESCENDING > > If i run it with i = 0 it gives wrong sort order. > > SUMA A B C D E > -1,11,2 2,3 1 11 3 > -1,83,4 5,2 2 11 3 > 0 3 3 2 12 2 > 12 14 2 1 11 1 > You are using different results in the ORDER BY clause. That's not allowed. Your t.c is an INTEGER and your E is a varchar but your sum are NUMERIC. Even though the result is always the same (dependent on I), Firebird doesn't know that. For Firebird the result CAN VARY between records and that's not allowed. So you need to cast them all to the same type. For instance this will work: order by iif(:i = 1, cast(t.c as numeric), IIF(:i = 0, sum(t.a) - sum(t.b), cast(t.e as numeric))) DESCENDING But if you want to order by E (=varchar) maybe you should cast to varchar but in that case you need to make sure the ordering is done correctly (with adding spaces in front of the sum to order right)
Re: [firebird-support] Restore without constraints
Köditz, Martin martin.koed...@it-syn.de [firebird-support] wrote: > is it possible to restore a database without constraints? Or is there > another way to do this? I just want do delete each primary key in the db. Is this on a healthy DB? I'm not sure if -I disables the constraints in that case. You can use -I -N. (-N for disabling the constraints). After that you can remove the primary records. See http://www.firebirdsql.org/manual/gbak-cmdline.html But then what If you activate the indexes and constraints again you face the same problem (you can't because of the sub-records). If the DB was build correctly there should have been a ON DELETE CASCADE on the constraint. Maybe you still can change the constraints with a ON DELETE CASCADE. Removing the records from the primary table will delete all sub-records too. Either way... with this you can activate all indexes and constraints again (but only if you also deleted the sub-records with the foreign keys): EXECUTE BLOCK AS DECLARE VARIABLE stmt VARCHAR(1000); BEGIN for select 'ALTER INDEX '||rdb$index_name ||' ACTIVE;' from rdb$indices where (rdb$system_flag is null or rdb$system_flag = 0) order by rdb$foreign_key nulls first into :stmt do EXECUTE STATEMENT :stmt; END ++ 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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Temporary disable FK constraint
Scott Morgan bl...@blueyonder.co.uk [firebird-support] wrote: > In a FB2.5 system is there a way to temporarily disable foreign key > constraints? i.e. something like 'ALTER INDEX ... INACTIVE' Well, here is the way to activate indexes and foreign keys: How to activate all indexes? http://www.firebirdfaq.org/faq274/ So doing the reverse should work too: SET TERM !! ; EXECUTE BLOCK AS DECLARE VARIABLE stmt VARCHAR(1000); BEGIN for select 'ALTER INDEX '||rdb$index_name ||' INACTIVE;' from rdb$indices where (rdb$system_flag is null or rdb$system_flag = 0) order by rdb$foreign_key nulls last into :stmt do EXECUTE STATEMENT :stmt; END!! SET TERM ; !! (not tested) > Alternatively, how safe is dropping/recreating the FKs on the fly? If nobody is using the database, I see no harm (but I'm not an expert). If it's just an import of one table I would only deactivate the indexes/primary keys/foreign keys of that table (and foreign keys pointing to that table). (Could also be done with a EXECUTE BLOCK and select of the correct rows) (just don't forget to activate them again afterwards :)) Grtz, Rik
Re: [firebird-support] Temporary disable FK constraint
Scott Morgan bl...@blueyonder.co.uk [firebird-support] wrote: > You can't inactivate a FK constraint: > > SQL> ALTER INDEX FK_FOO INACTIVE; > > unsuccessful metadata update > -MODIFY RDB$INDICES failed > -action cancelled by trigger (2) to preserve data integrity > -Cannot deactivate index used by an integrity constraint O, wow. And even deactivating the trigger/PK doesn't work with that. I didn't expect that. You could drop the constraint but would need to recreate it afterwards. ALTER TABLE FOO_TABLE DROP FK_FOO; But I agree, it's not very convenient. Deactivating would be so much simpler. Grtz, Rik
Re: [firebird-support] Temporary disable FK constraint
Scott Morgan bl...@blueyonder.co.uk [firebird-support] wrote: > You can't inactivate a FK constraint: B.T.W. This is a (still open) feature request: http://tracker.firebirdsql.org/browse/CORE-1924 http://stackoverflow.com/questions/1063617/how-can-i-temporarily-disable-all-constraints-in-a-table-in-firebird-2-1 Grtz, Rik
Re: [firebird-support] Re: How write a query with a progressive sum field
Luigi Siciliano luigi...@tiscalinet.it [firebird-support] wrote: I get exception: "invalid expression in the select list (not contained in either an aggregate function or the group by clause)". I don't understand the GROUP BY clause who I need to write in sub-select because if I put the same as in select: "GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE" I get the exception: "cannot use an aggregate function in a GROUP BY clause." I'm confused :( Yeah, I wonder why that worked in the first place in your original select. You didn't group by DC.CARICO and DC.SCARICO there too. But with a (SELECT)-field you don't need to group anymore. Try this: select DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE, DC.CARICO, DC.SCARICO, (SELECT SUM(DC2.CARICO - DC2.SCARICO) FROM DOC_CORPO DC2 WHERE (DC2.DOC_TESTA_ID = DT.ID) AND (DC2.ARTICOLO_ID = DC.ARTICOLO_ID) AND (DC2.DOC_TESTA_ID <= DC.DOC_TESTA_ID) ) AS SALDO from DOC_TESTA DT JOIN DOC_CORPO DC on DC.DOC_TESTA_ID = DT.ID WHERE DC.ARTICOLO_ID = :ID ORDER BY DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE
Re: [firebird-support] What makes a database file size so big?
On 29/03/16 11:44, Małgorzata Barchańska go...@fastdata.com.pl [firebird-support] wrote: > I'm very curious - so what makes our database file so big? Sum of clear > metadata > and raw data is about 2,4GB, and what with other 12GB? Is anything we > can do to shrink our big database file (drop useless indexes or > something like that)? You could try running your database through gstat.exe (in your bin-directory of Filebird, if you installed the development tools with it). You can learn a lot about the way the database-pages are filled per table. You can find a manual for it here: http://www.firebirdsql.org/pdfmanual/Firebird-gstat.pdf Grtz, Rik
Re: [firebird-support] db corruption
'Andrew Zenz' and...@aimsoftware.com.au [firebird-support] wrote: > FYI FirstAID scanned the database and identified 2 tables as having > corruption. I attempted a data pump with DBW excluding those 2 tables > but it failed on several other tables, tables that FirstAID claims are > clean. The tables on which it failed during the data pump... did they have foreign keys pointing to the 2 corrupt tables?? If they do, that's probably the reason it failed. If not... what exactly was the error? I've fixed a few DB's in the past manually but it's tedious work (and you might loose some records due to corrupt db-pages). Before pumping over the data from the corrupt DB you need to restore that DB to a working DB (backup/restore cycle). The DB will be marked as offline in case of errors but you can reach the data with a single instance of your DB-manager of choice. Then you need to remove the records in those tables which depend on the tables you left out. Or you need to NULL the foreign keys if your DB-design allows it. After that you should be able to pump the adjusted tables. (I even repaired some DB's in place after which I could bring them online again. And after a backup/restore cycle they were ok to use) Regards, Rik ++ 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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
