Re: [firebird-support] Select statement
Do you mean... SELECT T.* FROM Tenders T WHERE (T.Date_Closing IS NULL) or (T.Value IS NULL) or ((T.Date_Closing BETWEEN DateA and DateB) and (T.Value IS NULL)) On Wed, Oct 17, 2012 at 7:47 AM, Nols Smit wrote: > ** > > > Hi, > > I have a table, let's call it TENDERS. It has the following fields: ID, > Date_Closing, Value (ID is the pimary key). > > How do I select the the records in TENDERS where either Date_Closing or > Value or both is null but also including those records where Date_Closing > is not null and Date_Closing is between DateA and DateB and Value is null ? > > Regards, > > Nols Smit > > [Non-text portions of this message have been removed] > > > [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] 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/
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] Pre and Post snapshot script that quiesces the Firebird Database
I believe that the basic assumption that you have to put the database into a quiescent state to get a consistent backup is incorrect. I always thought that Firebird backups ran in a single long transaction meaning that nothing committed after the backup starts can be seen by the backup. Louis Kleiman On Thu, Feb 14, 2013 at 8:16 AM, noelfrain wrote: > ** > > > In order to guarantee a 'live' DB is backed up in a consistant state, we > need to make sure it is quiesced at backup time. > We need to configure the Storagecraft ShadowProtect backup job to run a > pre-snapshot script that quiesces the Firebird Database, followed by a > post-snapshot script that brings the DB backup out of this state. > Can anyone provide these scripts or tell us where they could be downloaded. > The version of Firebird we are running is 1.5.2.4731 > > > [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] LEFT JOIN much faster than JOIN
But LEFT OUTER JOIN may cause the optimizer to scan through the table for which all rows are being included in physical order while the INNER JOIN might cause a plan where rows are read based on a scan through an index. If rows aren't being filtered out, I can see how this might speed up the query execution. Louis Kleiman On Fri, Aug 2, 2013 at 8:04 AM, Josef Kokeš wrote: > ** > > > On 2.8.2013 13:59, Alexis Diel wrote: > > > > > > but LEFT JOIN is diferente of JOIN... > > - LEFT JOIN brings all the data filtered from the FROM table, and > > - JOIN brings only the data that have the ON condition in the JOINED > table. > > Which should make JOIN faster than LEFT JOIN, if anything, because it > can (potentionally) filter out some records. > > Josef > > > [Non-text portions of this message have been removed]
