Zucker sql union error

[hawkpig]

Hi

I have created 3 SQL scripts to extract similar data that I wan tin one file. So having created and tested each script I put them together with a union and now I get an error message:

************************************************** **********************************
the script is:

SELECT
calls.date_entered 'Date Created',
l0.user_name 'User Name',
calls.name 'Call Name',
calls.date_start 'Date to Call',
calls.time_start 'Time to Call',
calls_cs_calls.calls_end_code_c 'End Code',
calls_cs_calls.calls_reason_code_c 'Reason Code',
oppty.name 'Opportunity Name',
acc.name 'Account Name',
CONCAT(cont.first_name, ' ', cont.last_name) 'Contact Name',
acc.phone_office 'Phone Number',
acc.email1 'Email Address'
FROM calls
LEFT JOIN calls_cstm calls_cs_calls ON calls.id = calls_cs_calls.id_c
INNER JOIN users l0 ON l0.id= calls.assigned_user_id AND l0.deleted=0
INNER JOIN opportunities oppty ON oppty.id= calls.parent_id AND oppty.deleted=0
INNER JOIN accounts_opportunities ao ON ao.opportunity_id = oppty.id AND ao.deleted=0
INNER JOIN accounts acc ON ao.account_id=acc.id AND acc.deleted=0
INNER JOIN accounts_contacts ac ON ac.account_id = acc.id AND ac.deleted=0
INNER JOIN contacts cont ON ac.contact_id=cont.id AND cont.deleted=0
WHERE calls.parent_type = 'Opportunities'
AND date(calls.date_entered) >= date("$START_DATE")
AND date(calls.date_entered) <= date("$STOP_DATE")
AND calls.deleted=0
UNION ALL
SELECT
calls.date_entered 'Date Created',
l0.user_name 'User Name',
calls.name 'Call Name',
calls.date_start 'Date to Call',
calls.time_start 'Time to Call',
calls_cs_calls.calls_end_code_c 'End Code',
calls_cs_calls.calls_reason_code_c 'Reason Code',
'N/A' AS 'Opportunity Name',
acc.name 'Account Name',
CONCAT(cont.first_name, ' ', cont.last_name) 'Contact Name',
acc.phone_office 'Phone Number',
acc.email1 'Email Address'
FROM calls
LEFT JOIN calls_cstm calls_cs_calls ON calls.id = calls_cs_calls.id_c
INNER JOIN users l0 ON l0.id= calls.assigned_user_id AND l0.deleted=0
INNER JOIN accounts acc ON calls.parent_id=acc.id AND acc.deleted=0
INNER JOIN accounts_contacts ac ON ac.account_id = acc.id AND ac.deleted=0
INNER JOIN contacts cont ON ac.contact_id=cont.id AND cont.deleted=0
WHERE calls.parent_type = 'Accounts'
AND date(calls.date_entered) >= date("$START_DATE")
AND date(calls.date_entered) <= date("$STOP_DATE")
AND calls.deleted=0
UNION ALL
SELECT
calls.date_entered 'Date Created',
l0.user_name 'User Name',
calls.name 'Call Name',
calls.date_start 'Date to Call',
calls.time_start 'Time to Call',
calls_cs_calls.calls_end_code_c 'End Code',
calls_cs_calls.calls_reason_code_c 'Reason Code',
'N/A' AS 'Opportunity Name',
leads.account_name 'Account Name',
CONCAT(leads.first_name, ' ', leads.last_name) 'Contact Name',
leads.phone_work 'Phone Number',
leads.email1 'Email Address'
FROM calls
LEFT JOIN calls_cstm calls_cs_calls ON calls.id = calls_cs_calls.id_c
INNER JOIN users l0 ON l0.id= calls.assigned_user_id AND l0.deleted=0
INNER JOIN leads ON calls.parent_id=leads.id AND leads.deleted=0
WHERE calls.parent_type = 'Leads'
AND date(calls.date_entered) >= date("$START_DATE")
AND date(calls.date_entered) <= date("$STOP_DATE")
AND calls.deleted=0

************************************************** **********************************

and the error message I get is:

Query: SELECT calls.date_entered 'Date Created', l0.user_name 'User Name', calls.name 'Call Name', calls.date_start 'Date to Call', calls.time_start 'Time to Call', calls_cs_calls.calls_end_code_c 'End Code', calls_cs_calls.calls_reason_code_c 'Reason Code', oppty.name 'Opportunity Name', acc.name 'Account Name', CONCAT(cont.first_name, ' ', cont.last_name) 'Contact Name', acc.ph_office 'Ph Number', acc.email1 'Email Address' FROM calls LEFT JOIN calls_cstm calls_cs_calls ON calls.id = calls_cs_calls.id_c INNER JOIN users l0 ON l0.id= calls.assigned_user_id AND l0.deleted=0 INNER JOIN opportunities oppty ON oppty.id= calls.parent_id AND oppty.deleted=0 INNER JOIN accounts_opportunities ao ON ao.opportunity_id = oppty.id AND ao.deleted=0 INNER JOIN accounts acc ON ao.account_id=acc.id AND acc.deleted=0 INNER JOIN accounts_contacts ac ON ac.account_id = acc.id AND ac.deleted=0 INNER JOIN contacts cont ON ac.contact_id=cont.id AND cont.deleted=0 WHERE calls.parent_type = 'Opportunities' AND date(calls.date_entered) >= date("2007-07-25") AND date(calls.date_entered) <= date("2008-12-31") AND calls.deleted=0 UNION ALL SELECT calls.date_entered 'Date Created', l0.user_name 'User Name', calls.name 'Call Name', calls.date_start 'Date to Call', calls.time_start 'Time to Call', calls_cs_calls.calls_end_code_c 'End Code', calls_cs_calls.calls_reason_code_c 'Reason Code', 'N/A' AS 'Opportunity Name', acc.name 'Account Name', CONCAT(cont.first_name, ' ', cont.last_name) 'Contact Name', acc.ph_office 'Ph Number', acc.email1 'Email Address' FROM calls LEFT JOIN calls_cstm calls_cs_calls ON calls.id = calls_cs_calls.id_c INNER JOIN users l0 ON l0.id= calls.assigned_user_id AND l0.deleted=0 INNER JOIN accounts acc ON calls.parent_id=acc.id AND acc.deleted=0 INNER JOIN accounts_contacts ac ON ac.account_id = acc.id AND ac.deleted=0 INNER JOIN contacts cont ON ac.contact_id=cont.id AND cont.deleted=0 WHERE calls.parent_type = 'Accounts' AND date(calls.date_entered) >= date("2007-07-25") AND date(calls.date_entered) <= date("2008-12-31") AND calls.deleted=0 UNION ALL SELECT calls.date_entered 'Date Created', l0.user_name 'User Name', calls.name 'Call Name', calls.date_start 'Date to Call', calls.time_start 'Time to Call', calls_cs_calls.calls_end_code_c 'End Code', calls_cs_calls.calls_reason_code_c 'Reason Code', 'N/A' AS 'Opportunity Name', leads.account_name 'Account Name', CONCAT(leads.first_name, ' ', leads.last_name) 'Contact Name', leads.ph_work 'Ph Number', leads.email1 'Email Address' FROM calls LEFT JOIN calls_cstm calls_cs_calls ON calls.id = calls_cs_calls.id_c INNER JOIN users l0 ON l0.id= calls.assigned_user_id AND l0.deleted=0 INNER JOIN leads ON calls.parent_id=leads.id AND leads.deleted=0 WHERE calls.parent_type = 'Leads' AND date(calls.date_entered) >= date("2007-07-25") AND date(calls.date_entered) <= date("2008-12-31") AND calls.deleted=0
MySQL error 1054: Unknown column 'acc.ph_office' in 'field list'

************************************************** **********************************

very odd any one got any ideas?

I have made sure that 'db_type' => 'mysql'

cheers amigos

[kuske]

It sounds grazy, but it looks as if each appeareance of "one" is replaced by nothing.
So do you have a variable named "one" in your script? only an idea....