Re: [firebird-support] Cannot restore a database because of failed unique key index
Your query needs an aggregate field. Count. Alan On Oct 24, 2012 1:49 PM, "sir_wally_lewis" wrote: > Hi, > > We use Firebird 2.5.1 on Linux 64 bit > > Firebird fails to restore because of a unique constraint( see below ). > However when interrogating the data, no duplicate is found. ( see below > SQL, unique key is on the grouped by fields ) > > How can this be? > > Kind Regards, > > Robert. > > > > SELECT ID_VN, CD_ITM_SPR > FROM AS_ITM_SPR > GROUP BY ID_VN, CD_ITM_SPR > HAVING COUNT(*) > 1 > > > > > gbak:activating and creating deferred index AK_AS_ITM_SPR > gbak:cannot commit index AK_AS_ITM_SPR > gbak: ERROR:attempt to store duplicate value (visible to active > transactions) in unique index "AK_AS_ITM_SPR" > gbak: ERROR:action cancelled by trigger (3) to preserve data integrity > gbak: ERROR:Cannot deactivate index used by a PRIMARY/UNIQUE constraint > gbak:Exiting before completion due to errors > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++ > Yahoo! Groups Links > > > > [Non-text portions of this message have been removed]
[firebird-support] Cannot connect to server under heavy load
Hi! Cannot connect to server under heavy load. I get "connection rejected by remote interface" (335544421). Is this a bug, ot there is an option in firebird.conft to avoid this? Firebird.log (FB20 Classic Server) L3S-4 Wed Oct 24 14:04:28 2012 I/O error for file "C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB" L3S-4 Wed Oct 24 14:04:28 2012 lock conflict on no wait transaction L3S-4 Wed Oct 24 14:04:32 2012 Error while trying to access file L3S-4 Wed Oct 24 14:04:32 2012 no permission for read-write access to database C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB L3S-4 Wed Oct 24 14:04:36 2012 The process cannot access the file because it is being used by another process
Re: [firebird-support] Cannot connect to server under heavy load
On Wed, 24 Oct 2012 12:18:01 -, "ma_golyo" wrote: > Hi! > > Cannot connect to server under heavy load. > I get "connection rejected by remote interface" (335544421). > > Is this a bug, ot there is an option in firebird.conft to avoid this? > > > Firebird.log (FB20 Classic Server) What is the exact version of your Firebird? Mark
[firebird-support] Re: Cannot connect to server under heavy load
Hi! 2.0.7.13318 --- In firebird-support@yahoogroups.com, Mark Rotteveel wrote: > > On Wed, 24 Oct 2012 12:18:01 -, "ma_golyo" wrote: > > Hi! > > > > Cannot connect to server under heavy load. > > I get "connection rejected by remote interface" (335544421). > > > > Is this a bug, ot there is an option in firebird.conft to avoid this? > > > > > > Firebird.log (FB20 Classic Server) > > What is the exact version of your Firebird? > > Mark >
[firebird-support] Re: Cannot connect to server under heavy load
Sometimes I get "Unable to complete network request to host "l3s-4"" (335544721) error not 335544421. In this case nothing apperars in firebird.log. --- In firebird-support@yahoogroups.com, "ma_golyo" wrote: > > Hi! > > Cannot connect to server under heavy load. > I get "connection rejected by remote interface" (335544421). > > Is this a bug, ot there is an option in firebird.conft to avoid this? > > > Firebird.log (FB20 Classic Server) > L3S-4 Wed Oct 24 14:04:28 2012 > I/O error for file "C:\PROGRAM > FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB" > > > L3S-4 Wed Oct 24 14:04:28 2012 > lock conflict on no wait transaction > > > L3S-4 Wed Oct 24 14:04:32 2012 > Error while trying to access file > > > L3S-4 Wed Oct 24 14:04:32 2012 > no permission for read-write access to database C:\PROGRAM > FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB > > > L3S-4 Wed Oct 24 14:04:36 2012 > The process cannot access the file because it is being used by another > process >
[firebird-support] Re: Cannot connect to server under heavy load
Now I get "cannot attach to password database" (335544653) error. Same log as first time. --- In firebird-support@yahoogroups.com, "ma_golyo" wrote: > > Hi! > > Cannot connect to server under heavy load. > I get "connection rejected by remote interface" (335544421). > > Is this a bug, ot there is an option in firebird.conft to avoid this? > > > Firebird.log (FB20 Classic Server) > L3S-4 Wed Oct 24 14:04:28 2012 > I/O error for file "C:\PROGRAM > FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB" > > > L3S-4 Wed Oct 24 14:04:28 2012 > lock conflict on no wait transaction > > > L3S-4 Wed Oct 24 14:04:32 2012 > Error while trying to access file > > > L3S-4 Wed Oct 24 14:04:32 2012 > no permission for read-write access to database C:\PROGRAM > FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB > > > L3S-4 Wed Oct 24 14:04:36 2012 > The process cannot access the file because it is being used by another > process >
[firebird-support] Compound foreign key & null value bug?
I'm modifying a foreign key from one column to two. I dropped the original foreign key from the child table, populated the new columns, and then created the new key. I forgot to populate the new column in the child table, so all the relationships looked like this: ChildParent --- Col1:null -> Col1:A Col2:B-> Col2:B The new foreign keys were created without any errors. Since (null,B) doesn't match anything, shouldn't the creation have failed?
[firebird-support] Re: Cannot connect to server under heavy load
Another strange fact : Running 1 application with 250 thread where each thread connects to 2 database and runs 2 select works perfectly, BUT Running 50 application with 5 thread where each thread connects to 2 database and runs 2 select throw errors mentioned eralier. --- In firebird-support@yahoogroups.com, "ma_golyo" wrote: > > Hi! > > Cannot connect to server under heavy load. > I get "connection rejected by remote interface" (335544421). > > Is this a bug, ot there is an option in firebird.conft to avoid this? > > > Firebird.log (FB20 Classic Server) > L3S-4 Wed Oct 24 14:04:28 2012 > I/O error for file "C:\PROGRAM > FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB" > > > L3S-4 Wed Oct 24 14:04:28 2012 > lock conflict on no wait transaction > > > L3S-4 Wed Oct 24 14:04:32 2012 > Error while trying to access file > > > L3S-4 Wed Oct 24 14:04:32 2012 > no permission for read-write access to database C:\PROGRAM > FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB > > > L3S-4 Wed Oct 24 14:04:36 2012 > The process cannot access the file because it is being used by another > process >
RE: [firebird-support] Cannot connect to server under heavy load
> Cannot connect to server under heavy load. > I get "connection rejected by remote interface" (335544421). > > Is this a bug, ot there is an option in firebird.conft to avoid this? > > > Firebird.log (FB20 Classic Server) > L3S-4 Wed Oct 24 14:04:28 2012 > I/O error for file "C:\PROGRAM > FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB" > > > L3S-4 Wed Oct 24 14:04:28 2012 > lock conflict on no wait transaction > > > L3S-4 Wed Oct 24 14:04:32 2012 > Error while trying to access file > > > L3S-4 Wed Oct 24 14:04:32 2012 > no permission for read-write access to database C:\PROGRAM > FILES\FIREBIRD\FIREBIRD_2_0\SECURITY2.FDB > > > L3S-4 Wed Oct 24 14:04:36 2012 > The process cannot access the file because it is being used by > another process These errors suggest that there is some other process which is accessing the files... You need to find that other process, the problem may not exist in Firebird at all. Sean
[firebird-support] Solution for a redundant join?
The following simple query produces the results below: SELECT SCHEDULE.DATE_TIME, TEAMS.TEAM FROM SCHEDULE LEFT JOIN TEAMS ON SCHEDULE.HOME_TEAMID=TEAMS.PRIMARYKEY ***QUERY RESULTS (Showing 'Home Team' Column)*** 12/01/2012 TeamA 12/21/2012 TeamB 12/25/2012 TeamC How do I include the corresponding 'Away Team' column? Please see tables below: ***SCHEDULE TABLE*** PRIMARYKEY HOME_TEAMID AWAY_TEAMID DATE_TIME 1 1212/01/2012 2 2312/21/2012 3 3112/25/2012 ***TEAMS TABLE*** PRIMARYKEY TEAM_NAME 1 TeamA 2 TeamB 3 TeamC Please advise, thank you!
RE: [firebird-support] Solution for a redundant join?
> The following simple query produces the results below: > > SELECT SCHEDULE.DATE_TIME, TEAMS.TEAM FROM SCHEDULE LEFT JOIN > TEAMS ON SCHEDULE.HOME_TEAMID=TEAMS.PRIMARYKEY > > ***QUERY RESULTS (Showing 'Home Team' Column)*** > 12/01/2012 TeamA > 12/21/2012 TeamB > 12/25/2012 TeamC > > How do I include the corresponding 'Away Team' column? Use tables aliases to include multiple references to a single table. SELECT SCHEDULE.DATE_TIME, HomeTeam.TEAM, AwayTeam.Team FROM SCHEDULE LEFT JOIN TEAMS HomeTeam ON HomeTeam.PRIMARYKEY = SCHEDULE.HOME_TEAMID LEFT JOIN TEAMS AwayTeam ON AwayTeam.PRIMARYKEY = SCHEDULE.AWAY_TEAMID Sean
Re: [firebird-support] Solution for a redundant join?
Try using aliases: SELECT SCHEDULE.DATE_TIME, HOME_TEAM.TEAM as HomeTeamName, AWAY_TEAM.TEAM as AwayTeamName FROM SCHEDULE LEFT JOIN TEAMS HOME_TEAM ON SCHEDULE.HOME_TEAMID=HOME_TEAM.PRIMARYKEY LEFT JOIN TEAMS AWAY_TEAM ON SCHEDULE.AWAY_TEAMID=AWAY_TEAM.PRIMARYKEY On Wed, Oct 24, 2012 at 1:42 PM, Jeff wrote: > ** > > > The following simple query produces the results below: > > SELECT SCHEDULE.DATE_TIME, TEAMS.TEAM FROM SCHEDULE > LEFT JOIN TEAMS ON SCHEDULE.HOME_TEAMID=TEAMS.PRIMARYKEY > > ***QUERY RESULTS (Showing 'Home Team' Column)*** > 12/01/2012 TeamA > 12/21/2012 TeamB > 12/25/2012 TeamC > > How do I include the corresponding 'Away Team' column? Please see tables > below: > > ***SCHEDULE TABLE*** > PRIMARYKEY HOME_TEAMID AWAY_TEAMID DATE_TIME > 1 1 2 12/01/2012 > 2 2 3 12/21/2012 > 3 3 1 12/25/2012 > > ***TEAMS TABLE*** > PRIMARYKEY TEAM_NAME > 1 TeamA > 2 TeamB > 3 TeamC > > Please advise, thank you! > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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: http://docs.yahoo.com/info/terms/
[firebird-support] Re: Solution for a redundant join?
--- In firebird-support@yahoogroups.com, "Leyne, Sean" wrote: > Use tables aliases to include multiple references to a single table. > > > SELECT > SCHEDULE.DATE_TIME, HomeTeam.TEAM, AwayTeam.Team > FROM SCHEDULE > LEFT JOIN TEAMS HomeTeam ON HomeTeam.PRIMARYKEY = SCHEDULE.HOME_TEAMID > LEFT JOIN TEAMS AwayTeam ON AwayTeam.PRIMARYKEY = SCHEDULE.AWAY_TEAMID > > > Sean > Sean, aliases did the trick, thank you kindly!
[firebird-support] How do you 0/null results to a GROUP BY query
Hi All I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 etc. Any help will be appreciated! Cornie van Schoor InfoStar Software
RE: [firebird-support] How do you 0/null results to a GROUP BY query
> I have query which extract the sales per day from a table > > Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS > DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. > > It works 100%, but only returns the dates with sales, I need it to also > include > the dates with zero sales, for example: > > YEARNO DAYNO SUM > 2012 015000 > 2012 026000 > 2012 030 (or null will be fine) > 2012 047000 Use COALESCE(), as in: Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(COALESCE( DUE, 0)) from CLIENT_INVOICES group by YEARNO, WEEKNO. Sean
Re: [firebird-support] How do you 0/null results to a GROUP BY query
try something like this:
with recursive
Datas (Data) as
(select
Cast('2009-01-01' as Date)
From
rdb$database
union all
Select
Data + 1
From
Datas
Where
Data + 1 <= Cast('2009-01-31' as Date))
select
D.Data, sum(PVI.QuantidadeOriginal)
from
Datas D left join
PedidoVendaItem PVI on (D.Data = PVI.DataEntrega)
group by
D.Data
change the field/table names to fit you needs
HTH
Em 24/10/2012 19:54, cornievs escreveu:
> Hi All
>
> I have query which extract the sales per day from a table
>
> Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO,
> SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO.
>
> It works 100%, but only returns the dates with sales, I need it to also
> include the dates with zero sales, for example:
>
> YEARNO DAYNO SUM
> 2012 015000
> 2012 026000
> 2012 030 (or null will be fine)
> 2012 047000
>
> etc.
>
> Any help will be appreciated!
>
> Cornie van Schoor
> InfoStar Software
>
>
>
>
>
> ++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++
> Yahoo! Groups Links
>
>
>
>
Re: [firebird-support] How do you 0/null results to a GROUP BY query
But this won't return rows for dates where there is no record in the source table. On Wed, Oct 24, 2012 at 6:02 PM, Leyne, Sean wrote: > ** > > > > > I have query which extract the sales per day from a table > > > > Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS > > DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. > > > > It works 100%, but only returns the dates with sales, I need it to also > include > > the dates with zero sales, for example: > > > > YEARNO DAYNO SUM > > 2012 01 5000 > > 2012 02 6000 > > 2012 03 0 (or null will be fine) > > 2012 04 7000 > > Use COALESCE(), as in: > > > Select > EXTRACT(YEAR from DT) as YEARNO, > EXTRACT(YEARDAY from DT) AS DAYNO, > SUM(COALESCE( DUE, 0)) > from CLIENT_INVOICES > group by YEARNO, WEEKNO. > > Sean > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How do you 0/null results to a GROUP BY query
On 10/24/12 5:54 PM, cornievs wrote: > I have query which extract the sales per day from a table > > Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, > SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. > > It works 100%, but only returns the dates with sales, I need it to also > include the dates with zero sales, for example: > > YEARNO DAYNO SUM > 2012 015000 > 2012 026000 > 2012 030 (or null will be fine) > 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it.
Re: [firebird-support] How do you 0/null results to a GROUP BY query
Em 24/10/2012 20:21, Doug Chamberlin escreveu: > On 10/24/12 5:54 PM, cornievs wrote: >> I have query which extract the sales per day from a table >> >> Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, >> SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. >> >> It works 100%, but only returns the dates with sales, I need it to also >> include the dates with zero sales, for example: >> >> YEARNO DAYNO SUM >> 2012 015000 >> 2012 026000 >> 2012 030 (or null will be fine) >> 2012 047000 > I would crate a reference table that contains all the years and days > that you want to report on. Then join the data you have to that table > using an outer join so that all dates in the reference table are in the > result set and those that have data from the client invoices will show it. > I used to do this way, but with CTE you create that "table" dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days
Re: [firebird-support] Re: Solution for a redundant join?
On Wed, Oct 24, 2012 at 4:34 PM, Jeff wrote: > > > Use tables aliases to include multiple references to a single table. > > > > > > SELECT > > SCHEDULE.DATE_TIME, HomeTeam.TEAM, AwayTeam.Team > > FROM SCHEDULE > > LEFT JOIN TEAMS HomeTeam ON HomeTeam.PRIMARYKEY = SCHEDULE.HOME_TEAMID > > LEFT JOIN TEAMS AwayTeam ON AwayTeam.PRIMARYKEY = SCHEDULE.AWAY_TEAMID > > Sean, aliases did the trick, thank you kindly! > > Just out of curiosity, do you have any schedule records for which there are not matching home teams and away teams? If not, I would use two inner joins rather than two outer joins. That lets the optimizer do a better job of planning the execution path. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] How do you 0/null results to a GROUP BY query
On 10/24/12 6:25 PM, Alexandre Benson Smith wrote: > Em 24/10/2012 20:21, Doug Chamberlin escreveu: >> On 10/24/12 5:54 PM, cornievs wrote: >>> I have query which extract the sales per day from a table >>> >>> Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, >>> SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. >>> >>> It works 100%, but only returns the dates with sales, I need it to also >>> include the dates with zero sales, for example: >>> >>> YEARNO DAYNO SUM >>> 2012 015000 >>> 2012 026000 >>> 2012 030 (or null will be fine) >>> 2012 047000 >> I would crate a reference table that contains all the years and days >> that you want to report on. Then join the data you have to that table >> using an outer join so that all dates in the reference table are in the >> result set and those that have data from the client invoices will show it. >> > I used to do this way, but with CTE you create that "table" dynamically, > another option is using EXECUTE STATEMENT or a SP to loop trough the days I saw that. However, I don't see the advantage. Using a reference table is simpler and clearer than using CTE or other code to generate the reference values.
Re: [firebird-support] How do you 0/null results to a GROUP BY query
Em 24/10/2012 20:37, Doug Chamberlin escreveu: > On 10/24/12 6:25 PM, Alexandre Benson Smith wrote: >> Em 24/10/2012 20:21, Doug Chamberlin escreveu: >>> On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 >>> I would crate a reference table that contains all the years and days >>> that you want to report on. Then join the data you have to that table >>> using an outer join so that all dates in the reference table are in the >>> result set and those that have data from the client invoices will show it. >>> >> I used to do this way, but with CTE you create that "table" dynamically, >> another option is using EXECUTE STATEMENT or a SP to loop trough the days > I saw that. However, I don't see the advantage. Using a reference table > is simpler and clearer than using CTE or other code to generate the > reference values. > > > > One don't need to "refill" the values, like at the beginning of each year... of course you could pre-fill i with a century of days... :)
Re: [firebird-support] How do you 0/null results to a GROUP BY query
>>> I have query which extract the sales per day from a table > > >>> > >>> Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) > AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. > >>> > >>> It works 100%, but only returns the dates with sales, I need it to > also include the dates with zero sales, for example: > >>> > >>> YEARNO DAYNO SUM > >>> 2012 01 5000 > >>> 2012 02 6000 > >>> 2012 03 0 (or null will be fine) > >>> 2012 04 7000 > >> I would crate a reference table that contains all the years and days > >> that you want to report on. Then join the data you have to that table > >> using an outer join so that all dates in the reference table are in the > >> result set and those that have data from the client invoices will > show it. > >> > > I used to do this way, but with CTE you create that "table" > dynamically, > > another option is using EXECUTE STATEMENT or a SP to loop trough the > days > I saw that. However, I don't see the advantage. Using a reference table > is simpler and clearer than using CTE or other code to generate the > reference values. > Thanks! I will most likely go with the reference table(s) option, but I will test both. Regards Cornie [Non-text portions of this message have been removed]
