Clarification on Newbie Question - Kraer, Joseph - com.mysql.lists.eventum-users

6 messages in com.mysql.lists.eventum-usersClarification on Newbie Question

From	Sent On	Attachments
Daniel Kasak	30 Nov 2005 15:38
Kraer, Joseph	05 Dec 2005 08:22
Kraer, Joseph	05 Dec 2005 08:42
Kraer, Joseph	05 Dec 2005 10:39
Spahr, Bryan	05 Dec 2005 10:58
Kraer, Joseph	06 Dec 2005 08:26

Subject:	Clarification on Newbie Question
From:	Kraer, Joseph (jkr...@taylorbean.com)
Date:	12/05/2005 10:39:40 AM
List:	com.mysql.lists.eventum-users

My main issue is with the IF statements. Perhaps, I should've given a statement of what I wanted to do, such as this:

When the eventum_custom_field.fld_id reads as "47", then take the column name/heading from eventum_custom_field_option.cfo_value cell that matches the previous fld_id and list it as "Product Name." When, on the other hand, the eventum_custom_field.fld_id reads as "59", then . . ., etc., etc.

The key thing here is that I need to grab some entries (cells) of one table and use them as column names/headings and then, based upon these cells, get the data that I need from another table and list these values under the other headings. Is it possible to do something like this? Perhaps, this should've been the question to be asked.

Does anyone have the Eventum/MySQL expertise to help me with this? (See below for original message.)

Thank you,

Joseph "Tito" Kraer Business Systems Analyst Taylor, Bean & Whitaker Mortgage Corp

-----Original Message----- From: Kraer, Joseph [mailto:jkr...@taylorbean.com] Sent: Monday, December 05, 2005 11:43 AM To: even...@lists.mysql.com Subject: Newbie Question: listing open issues regardless of project

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 = "47" THEN eventum_custom_field_option.cfo_value AS "Product Name" ELSE IF eventum_custom_field.fld_id = "59" THEN eventum_custom_field_option.cfo_value AS "Project Name" ELSE IF eventum_custom_field.fld_id = "4" THEN eventum_custom_field_option.cfo_value AS "Change Requester" ELSE IF eventum_custom_field.fld_id = "1" THEN eventum_custom_field_option.cfo_value AS "Change Type" ELSE IF eventum_custom_field.fld_id = "2" THEN eventum_custom_field_option.cfo_value AS "Requested Completion Date" ELSE IF eventum_custom_field.fld_id = "46" THEN eventum_custom_field_option.cfo_value AS "BSA Lead" ELSE IF eventum_custom_field.fld_id = "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 = eventum_project_priority.pri_id AND eventum_issue.iss_id = eventum_issue_user.isu_iss_id AND eventum_issue_user.isu_usr_id = eventum_user.usr_id AND eventum_issue.iss_prj_id = eventum_project.prj_id AND eventum_issue.iss_prc_id = eventum_project_category.prc_id AND eventum_issue.iss_sta_id = 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 != "5" OR eventum_issue.iss_sta_id != "6" OR eventum_issue.iss_sta_id != "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