RE: Newbie Question: listing open issues regardless of project
Shawn, Glen, and everyone else, Thank you very much! I do believe that I have enough material to work on it now! I appreciate your help very much! Joseph "Tito" Kraer Business Systems Analyst Taylor, Bean & Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 11:03 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. So it is clear now, that you should have 16 columns and to build dynamically the column headings. The usual way to do such things in SQL is prepared statements. See: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html And good example of how to use them (though it is an article about stored routines) here: http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html You will need to use user variables as well: http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html CONCAT function: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Control flow functions: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Correlated subqueries: http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html Hope that'll be enough to make your query work. Kraer, Joseph wrote: > Gleb, > > What I need to have is a total of 16 columns: issue ID, priority, > assigned (to), project name, category, status, last update date, > summary, product name, project name (not the same as before), change > requester, change type, requested completion date, lead, developer, > impacted dept. > > I have no problems generating the first eight or the last columns. The > seven in between are the issue as they are not columns per se. They are > cells in different tables. I need to extract the contents of certain > cells, based on certain IDs, to be used as the column headings in the > output to my query. Then, I need to look into other tables to fill > those columns. This is what I need help with: how do I generate those > columns? I guess they could be generated separately and then I could > put both of my outputs together. Unfortunately, time is running out. > > TIA, > > Joseph "Tito" Kraer > Business Systems Analyst > Taylor, Bean & Whitaker Mortgage Corp > > -Original Message- > From: Gleb Paharenko [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 06, 2005 6:52 AM > To: mysql@lists.mysql.com > Subject: Re: Newbie Question: listing open issues regardless of project -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question: listing open issues regardless of project
Hello. So it is clear now, that you should have 16 columns and to build dynamically the column headings. The usual way to do such things in SQL is prepared statements. See: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html And good example of how to use them (though it is an article about stored routines) here: http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html You will need to use user variables as well: http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html CONCAT function: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Control flow functions: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Correlated subqueries: http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html Hope that'll be enough to make your query work. Kraer, Joseph wrote: > Gleb, > > What I need to have is a total of 16 columns: issue ID, priority, > assigned (to), project name, category, status, last update date, > summary, product name, project name (not the same as before), change > requester, change type, requested completion date, lead, developer, > impacted dept. > > I have no problems generating the first eight or the last columns. The > seven in between are the issue as they are not columns per se. They are > cells in different tables. I need to extract the contents of certain > cells, based on certain IDs, to be used as the column headings in the > output to my query. Then, I need to look into other tables to fill > those columns. This is what I need help with: how do I generate those > columns? I guess they could be generated separately and then I could > put both of my outputs together. Unfortunately, time is running out. > > TIA, > > Joseph "Tito" Kraer > Business Systems Analyst > Taylor, Bean & Whitaker Mortgage Corp > > -Original Message- > From: Gleb Paharenko [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 06, 2005 6:52 AM > To: mysql@lists.mysql.com > Subject: Re: Newbie Question: listing open issues regardless of project -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Question: listing open issues regardless of project
I see it! Tito, you have to "pivot" those fields out of your "custom fields/custom field values" table(s). One of the best places to do that is into a temporary table so that you can join your pivoted rows into the rest of the report. I am assuming that the `eventum_custom_field` table has a field something like `issue_id` that associates a field with an issue. I am also assuming that there is only one custom field of any one type per issue. CREATE TEMPORARY TABLE tmpCustFields SELECT cf.issue_id , MAX(if(fld_id=47,cfo.cfo_value,NULL)) as ProductName , MAX(if(fld_id=59,cfo.cfo_value,NULL)) as ProjectName , MAX(if(fld_id=4,cfo.cfo_value,NULL)) as ChangeRequester , MAX(if(fld_id=1,cfo.cfo_value,NULL)) as ChangeType , MAX(if(fld_id=2,cfo.cfo_value,NULL)) as ReqCompletionDate , MAX(if(fld_id=46,cfo.cfo_value,NULL)) as BSALead , MAX(if(fld_id=37,cfo.cfo_value,NULL)) as Developer FROM eventum_custom_fields cf LEFT JOIN eventum_custom_field_options cfo ON cf.cf_id = cfo.cfo_custom_field_id GROUP BY cf.issue_id; You will need to modify the column names in the ON clause to match the actual names of the columns that you need to relate an option to a field or an issue (whichever works). This query builds your middle columns into a table of their own. I hope that once you get them this far, JOINing this temp table to the rest of the tables you need to build your query will look pretty straight-forward. Look at the data to see what we did SELECT * from tmpCustFields limit 100; Hope that helps! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Kraer, Joseph" <[EMAIL PROTECTED]> wrote on 12/06/2005 10:39:01 AM: > Gleb, > > What I need to have is a total of 16 columns: issue ID, priority, > assigned (to), project name, category, status, last update date, > summary, product name, project name (not the same as before), change > requester, change type, requested completion date, lead, developer, > impacted dept. > > I have no problems generating the first eight or the last columns. The > seven in between are the issue as they are not columns per se. They are > cells in different tables. I need to extract the contents of certain > cells, based on certain IDs, to be used as the column headings in the > output to my query. Then, I need to look into other tables to fill > those columns. This is what I need help with: how do I generate those > columns? I guess they could be generated separately and then I could > put both of my outputs together. Unfortunately, time is running out. > > TIA, > > Joseph "Tito" Kraer > Business Systems Analyst > Taylor, Bean & Whitaker Mortgage Corp > > -Original Message- > From: Gleb Paharenko [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 06, 2005 6:52 AM > To: mysql@lists.mysql.com > Subject: Re: Newbie Question: listing open issues regardless of project > > Hello. > > > > > Please, I don't need comments telling me that I'm missing the > semicolon > > or that comments such as "doesn't work" are worthless. I know there's > > something inherently wrong with this query (obviously . . . since it > > doesn't give me the results that I am looking for). Does anyone have > a > > constructive comment as to how to do this? > > Please, could you answer what are you going to get in the last columns. > You've said that you had problems with the last seven columns, but from > the logic of your query I see that you just want a column, which changes > its value depending on the value in other field. Am I correct? Please > provide a sample output (what you want to see in the results) in case > I'm wrong. Now I'm not talking about syntax, but rather about sense > of the query. > > Kraer, Joseph wrote: > > > I am sending this message to both Eventum and MySQL support lists. > > > > I am trying to write a select statement in the MySQL Query Browser (v. > > 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP > is > > 4.3.10). My goal is to list certain data from all open issues, across > > projects, as well as listing some issue details contained in custom > > fields. I don't have a problem getting the data for the first eight > > columns of my query. My problem lies with listing the next seven > > columns, which come from custom fields. I thought of using a PHP > script > > but this is a one-time deal and my knowledge of PHP is as poor as that > > of MySQL. > > > > As Eventum users may know, headings for custom fields are extracted > from > > cells in the eventum_custom_field_option table. I thought that IF >
RE: Newbie Question: listing open issues regardless of project
Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph "Tito" Kraer Business Systems Analyst Taylor, Bean & Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. > Please, I don't need comments telling me that I'm missing the semicolon > or that comments such as "doesn't work" are worthless. I know there's > something inherently wrong with this query (obviously . . . since it > doesn't give me the results that I am looking for). Does anyone have a > constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: > I am sending this message to both Eventum and MySQL support lists. > > I am trying to write a select statement in the MySQL Query Browser (v. > 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is > 4.3.10). My goal is to list certain data from all open issues, across > projects, as well as listing some issue details contained in custom > fields. I don't have a problem getting the data for the first eight > columns of my query. My problem lies with listing the next seven > columns, which come from custom fields. I thought of using a PHP script > but this is a one-time deal and my knowledge of PHP is as poor as that > of MySQL. > > As Eventum users may know, headings for custom fields are extracted from > cells in the eventum_custom_field_option table. I thought that IF > statements would do the job, but I get a syntax error (1064). > Obviously, they are not the way to go. Nevertheless, here's the > complete query so you can get an idea of where I want to go: > > SELECT DISTINCT > eventum_issue.iss_id AS "Issue ID", > eventum_project_priority.pri_title AS "Priority", > eventum_user.usr_full_name AS "Assigned", > eventum_project.prj_title AS "Project Name", > eventum_project_category.prc_title AS "Category", > eventum_status.sta_title AS "Status", > eventum_issue.iss_updated_date AS "Last Update Date", > eventum_issue.iss_summary AS "Summary", > > IF eventum_custom_field.fld_id =3D "47" > THEN eventum_custom_field_option.cfo_value AS "Product > Name" > ELSE IF eventum_custom_field.fld_id =3D "59" > THEN eventum_custom_field_option.cfo_value AS "Project > Name" > ELSE IF eventum_custom_field.fld_id =3D "4" > THEN eventum_custom_field_option.cfo_value AS "Change > Requester" > ELSE IF eventum_custom_field.fld_id =3D "1" > THEN eventum_custom_field_option.cfo_value AS "Change > Type" > ELSE IF eventum_custom_field.fld_id =3D "2" > THEN eventum_custom_field_option.cfo_value AS "Requested > Completion Date" > ELSE IF eventum_custom_field.fld_id =3D "46" > THEN eventum_custom_field_option.cfo_value AS "BSA Lead" > ELSE IF eventum_custom_field.fld_id =3D "37" > THEN eventum_custom_field_option.cfo_value AS > "Developer" > > FROM eventum_issue, eventum_custom_field, eventum_custom_field_option > INNER JOIN > eventum_project_priority, > eventum_issue_user, > eventum_u
Re: Newbie Question: listing open issues regardless of project
Hello. > Please, I don't need comments telling me that I'm missing the semicolon > or that comments such as "doesn't work" are worthless. I know there's > something inherently wrong with this query (obviously . . . since it > doesn't give me the results that I am looking for). Does anyone havea > constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: > I am sending this message to both Eventum and MySQL support lists. > > I am trying to write a select statement in the MySQL Query Browser (v. > 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is > 4.3.10). My goal is to list certain data from all open issues, across > projects, as well as listing some issue details contained in custom > fields. I don't have a problem getting the data for the first eight > columns of my query. My problem lies with listing the next seven > columns, which come from custom fields. I thought of using a PHP script > but this is a one-time deal and my knowledge of PHP is as poor as that > of MySQL. > > As Eventum users may know, headings for custom fields are extracted from > cells in the eventum_custom_field_option table. I thought that IF > statements would do the job, but I get a syntax error (1064). > Obviously, they are not the way to go. Nevertheless, here's the > complete query so you can get an idea of where I want to go: > > SELECT DISTINCT > eventum_issue.iss_id AS "Issue ID", > eventum_project_priority.pri_title AS "Priority", > eventum_user.usr_full_name AS "Assigned", > eventum_project.prj_title AS "Project Name", > eventum_project_category.prc_title AS "Category", > eventum_status.sta_title AS "Status", > eventum_issue.iss_updated_date AS "Last Update Date", > eventum_issue.iss_summary AS "Summary", > > IF eventum_custom_field.fld_id =3D "47" > THEN eventum_custom_field_option.cfo_value AS "Product > Name" > ELSE IF eventum_custom_field.fld_id =3D "59" > THEN eventum_custom_field_option.cfo_value AS "Project > Name" > ELSE IF eventum_custom_field.fld_id =3D "4" > THEN eventum_custom_field_option.cfo_value AS "Change > Requester" > ELSE IF eventum_custom_field.fld_id =3D "1" > THEN eventum_custom_field_option.cfo_value AS "Change > Type" > ELSE IF eventum_custom_field.fld_id =3D "2" > THEN eventum_custom_field_option.cfo_value AS "Requested > Completion Date" > ELSE IF eventum_custom_field.fld_id =3D "46" > THEN eventum_custom_field_option.cfo_value AS "BSA Lead" > ELSE IF eventum_custom_field.fld_id =3D "37" > THEN eventum_custom_field_option.cfo_value AS > "Developer" > > FROM eventum_issue, eventum_custom_field, eventum_custom_field_option > INNER JOIN > eventum_project_priority, > eventum_issue_user, > eventum_user, > eventum_project, > eventum_project_category, > eventum_status > eventum_issue_custom_field > WHERE eventum_issue.iss_pri_id =3D eventum_project_priority.pri_id > AND eventum_issue.iss_id =3D eventum_issue_user.isu_iss_id > AND eventum_issue_user.isu_usr_id =3D eventum_user.usr_id > AND eventum_issue.iss_prj_id =3D eventum_project.prj_id > AND eventum_issue.iss_prc_id =3D eventum_project_category.prc_id > AND eventum_issue.iss_sta_id =3D eventum_status.sta_id > AND (eventum_issue.iss_closed_date IS NULL > OR (eventum_issue.iss_closed_date IS NOT NULL > AND (eventum_issue.iss_sta_id !=3D "5" > OR eventum_issue.iss_sta_id !=3D "6" > OR eventum_issue.iss_sta_id !=3D "9"))) > ORDER BY eventum_issue.iss_id > > Please, I don't need comments telling me that I'm missing the semicolon > or that comments such as "doesn't work" are worthless. I know there's > something inherently wrong with this query (obviously . . . since it > doesn't give me the results that I am looking for). Does anyone have a > constructive comment as to how to do this? > > Thank you very much in advance, > > Joseph "Tito" Kraer > Business Systems Analyst > Taylor, Bean & Whitaker Mortgage Corp > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\__
