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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
