[firebird-support] about system table RDB$TRIGGERS
Dear friends, Opening the table RDB$TRIGGERS, I saw many rows where the column RDB$TRIGGER_SOURCE is empty (null or an empty string). And the related tables doesn´t present these triggers. I ask you: -Are these rows at RDB$TRIGGERS useless ? -May I delete these rows ? -If I delete these rows, may I cause some any kind of trouble to my database ? Will this delection be helpful for the DB ? This way, will this delection affect the DB operation in any way, positive or negatively ? Thanks a lot, Roberto Camargo [Non-text portions of this message have been removed]
Re: [firebird-support] about system table RDB$TRIGGERS
Dear Mark, Thanks a lot for your quick answer. I suspected this, specially because the column rdb$system_flag is different of 0 (zero). But as I don't know deeply the bd structure (the system tables), I asked this for to know if this case could mean useless triggers, and then this could mean trash for the db. I understood your information very clearly and I thank you a lot for that. My best wishes for you, Roberto Camargo. From: Mark Rotteveel m...@lawinegevaar.nl To: firebird-support@yahoogroups.com Sent: Saturday, June 15, 2013 10:27 AM Subject: Re: [firebird-support] about system table RDB$TRIGGERS On 15-6-2013 15:20, Tupy... nambá wrote: Dear friends, Opening the table RDB$TRIGGERS, I saw many rows where the column RDB$TRIGGER_SOURCE is empty (null or an empty string). And the related tables doesn´t present these triggers. I ask you: -Are these rows at RDB$TRIGGERS useless ? -May I delete these rows ? -If I delete these rows, may I cause some any kind of trouble to my database ? Will this delection be helpful for the DB ? This way, will this delection affect the DB operation in any way, positive or negatively ? Never, ever directly manipulate the system tables. Most of those triggers are system triggers which are important for the correct working of the Firebird database. A new Firebird database has 35 system triggers which have no source (because they weren't declared using SQL). The RDB$TRIGGER_SOURCE is just informational, the actual (compiled) trigger is stored in RDB$TRIGGER_BLR. So no, don't delete them, you will ruin your database. Mark -- Mark Rotteveel ++ 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] Help on this SQL statment
Hi everyone, Can someone please advise me as to what is happing with this SQL Select statement I am trying to use SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 I am getting the following error -2147467259 - [ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) If someone could explain that to me I would be most greatful. [Non-text portions of this message have been removed]
Re: [firebird-support] Help on this SQL statment
GROUP BY PRODUCTDESCRIPTION there is PRODUCTCODE missing here if you use aggregate functions u have to add all fields to GROUP BY that are not used with aggregate functions From: Andrew gable andrew.ga...@tesco.net To: firebird-support@yahoogroups.com Sent: Saturday, June 15, 2013 7:54 PM Subject: [firebird-support] Help on this SQL statment Hi everyone, Can someone please advise me as to what is happing with this SQL Select statement I am trying to use SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 I am getting the following error -2147467259 - [ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) If someone could explain that to me I would be most greatful. [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
RE: [firebird-support] Help on this SQL statment
I am sorry I Really do not understand :( -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Nagy Szilveszter Sent: 15 June 2013 5:57 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Help on this SQL statment GROUP BY PRODUCTDESCRIPTION there is PRODUCTCODE missing here if you use aggregate functions u have to add all fields to GROUP BY that are not used with aggregate functions From: Andrew gable andrew.ga...@tesco.net To: firebird-support@yahoogroups.com Sent: Saturday, June 15, 2013 7:54 PM Subject: [firebird-support] Help on this SQL statment Hi everyone, Can someone please advise me as to what is happing with this SQL Select statement I am trying to use SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 I am getting the following error -2147467259 - [ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) If someone could explain that to me I would be most greatful. [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 - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3345 / Virus Database: 3199/6413 - Release Date: 06/15/13
Re: [firebird-support] Help on this SQL statment
The problem is that you have asked to summarize the data by grouping on product description but you have also asked for a product code. The server does not know if there is a one-to-one relationship between product description and product code. Therefore, it does not know what product code to use for each product description and,therefore, for each row in the resulting data set. The solution is to add product code to the GROUP BY clause. That way you will get a resulting record for every unique product code-product description combination and the server will know which product code to return because it is the one being paired with product description in that specific grouping. The rule you violated is the one that requires all requested fields in the output result set to be either the result of an aggregation function, such as SUM(), or it must appear in the GROUP BY clause (so the server will know what value to return). On Sat, Jun 15, 2013 at 3:03 PM, Andrew gable andrew.ga...@tesco.netwrote: I am sorry I Really do not understand :( -Original Message- From: firebird-support@yahoogroups.com [mailto: firebird-support@yahoogroups.com] On Behalf Of Nagy Szilveszter Sent: 15 June 2013 5:57 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Help on this SQL statment GROUP BY PRODUCTDESCRIPTION there is PRODUCTCODE missing here if you use aggregate functions u have to add all fields to GROUP BY that are not used with aggregate functions From: Andrew gable andrew.ga...@tesco.net To: firebird-support@yahoogroups.com Sent: Saturday, June 15, 2013 7:54 PM Subject: [firebird-support] Help on this SQL statment Hi everyone, Can someone please advise me as to what is happing with this SQL Select statement I am trying to use SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 I am getting the following error -2147467259 - [ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) If someone could explain that to me I would be most greatful. [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 - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3345 / Virus Database: 3199/6413 - Release Date: 06/15/13 ++ 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]
Re: [firebird-support] Help on this SQL statment
Change it into this syntax: SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTODE, PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 On Sun, Jun 16, 2013 at 2:51 AM, Doug Chamberlin chamberlin.d...@gmail.comwrote: ** The problem is that you have asked to summarize the data by grouping on product description but you have also asked for a product code. The server does not know if there is a one-to-one relationship between product description and product code. Therefore, it does not know what product code to use for each product description and,therefore, for each row in the resulting data set. The solution is to add product code to the GROUP BY clause. That way you will get a resulting record for every unique product code-product description combination and the server will know which product code to return because it is the one being paired with product description in that specific grouping. The rule you violated is the one that requires all requested fields in the output result set to be either the result of an aggregation function, such as SUM(), or it must appear in the GROUP BY clause (so the server will know what value to return). On Sat, Jun 15, 2013 at 3:03 PM, Andrew gable andrew.ga...@tesco.net wrote: I am sorry I Really do not understand :( -Original Message- From: firebird-support@yahoogroups.com [mailto: firebird-support@yahoogroups.com] On Behalf Of Nagy Szilveszter Sent: 15 June 2013 5:57 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Help on this SQL statment GROUP BY PRODUCTDESCRIPTION there is PRODUCTCODE missing here if you use aggregate functions u have to add all fields to GROUP BY that are not used with aggregate functions From: Andrew gable andrew.ga...@tesco.net To: firebird-support@yahoogroups.com Sent: Saturday, June 15, 2013 7:54 PM Subject: [firebird-support] Help on this SQL statment Hi everyone, Can someone please advise me as to what is happing with this SQL Select statement I am trying to use SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 I am getting the following error -2147467259 - [ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) If someone could explain that to me I would be most greatful. [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 - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3345 / Virus Database: 3199/6413 - Release Date: 06/15/13 ++ 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] -- Iwan Cahyadi Sugeng Interaktif Cipta Lestari [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
Re: [firebird-support] Help on this SQL statment
Something more, generally it is a better practice to have a view and call that view. So, you would write: SELECT * FROM MyView WHERE MyCondition GROUP BY MyColumns ORDER BY MyOrder Greetings. Walter. On Sat, Jun 15, 2013 at 3:54 PM, Iwan Cahyadi Sugeng iwan.c.sug...@gmail.com wrote: Change it into this syntax: SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTODE, PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 On Sun, Jun 16, 2013 at 2:51 AM, Doug Chamberlin chamberlin.d...@gmail.comwrote: ** The problem is that you have asked to summarize the data by grouping on product description but you have also asked for a product code. The server does not know if there is a one-to-one relationship between product description and product code. Therefore, it does not know what product code to use for each product description and,therefore, for each row in the resulting data set. The solution is to add product code to the GROUP BY clause. That way you will get a resulting record for every unique product code-product description combination and the server will know which product code to return because it is the one being paired with product description in that specific grouping. The rule you violated is the one that requires all requested fields in the output result set to be either the result of an aggregation function, such as SUM(), or it must appear in the GROUP BY clause (so the server will know what value to return). On Sat, Jun 15, 2013 at 3:03 PM, Andrew gable andrew.ga...@tesco.net wrote: I am sorry I Really do not understand :( -Original Message- From: firebird-support@yahoogroups.com [mailto: firebird-support@yahoogroups.com] On Behalf Of Nagy Szilveszter Sent: 15 June 2013 5:57 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Help on this SQL statment GROUP BY PRODUCTDESCRIPTION there is PRODUCTCODE missing here if you use aggregate functions u have to add all fields to GROUP BY that are not used with aggregate functions From: Andrew gable andrew.ga...@tesco.net To: firebird-support@yahoogroups.com Sent: Saturday, June 15, 2013 7:54 PM Subject: [firebird-support] Help on this SQL statment Hi everyone, Can someone please advise me as to what is happing with this SQL Select statement I am trying to use SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 I am getting the following error -2147467259 - [ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) If someone could explain that to me I would be most greatful. [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 - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3345 / Virus Database: 3199/6413 - Release Date: 06/15/13 ++ 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]
Re: [firebird-support] Help on this SQL statment
Em 15/6/2013 16:54, Iwan Cahyadi Sugeng escreveu: Change it into this syntax: SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTODE, PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 The error will be corrected by the addition of ProductCode to the GROUP BY clause, as the others have said, but I think the ORDER clause should be: SQLCommand = SQLCommand ORDER BY 3 DESC That way the result would be order by the Sum of ProductQty see you !