Export Error in Opportunities

[bobbundy]

Trying to Export Opportunities from the list of opportunities on the Opportunities:Home screen.

Get this error message:

SQL Error : Incorrect syntax near the keyword 'LEFT'.

It doesn't matter if I select 1 opportunity from the list, all the opportunities on a single page, or all opportunities. The same error message appears.

Please help. Thank you.

Bob

[andopes]

Hi Bob

Some questions:

1. Did you installed any package into your sugar installation?
2. Did you customize the Opportunities module?
3. Is there any error message at the tail of sugarcrm.log in the root of sugar installation?
4. Can you provide us the whole query string which caused the error?

Cheers

[bobbundy]

Andres -

Thank you for your reply. Here are the replies to your comments:

1. No. This is a straight Sugar Community edition - no installations
2. We customized it to add some fields and change some labels but no code customizations in the php code
3. No. There is no error message at the end of the log.
4. When we click the "Search" button, this is the only message. There is no other text to the messasge: there is no full query statement to display. If there is a way to find the entire SQL statement, please point it to us. In the opportunity.php file, there are six places where the "LEFT" keyword is used in a query. However, the error statement does not give a line number or any other indication of which query area to review.

I hope this helps. Thank you.

[andopes]

Hi Bob

Nice to hear from you again.
There is the steps to get the query string:

Edit the file modules/Opportunities/Opportunity.php and add the line:

 die("<LI>$query"); 
  

Just before the line:

 return $query; 
  

Then save the file and try to export again. The query for exporting Opportunities will be displayed in the screen.

Cheers

[bobbundy]

Hi Andres -

We did as you suggested. It output the following query before the displaying the error message:

# SELECT opportunities.*, accounts.name as account_name,
users.user_name as assigned_user_name
,opportunities_cstm.account_id_cFROM opportunities LEFT JOIN users ON
opportunities.assigned_user_id=users.idLEFT JOIN
accounts_opportunities ON
opportunities.id=accounts_opportunities.opportunit y_id LEFT JOIN
accounts ON accounts_opportunities.account_id=accounts.id LEFT JOIN
opportunities_cstm ON opportunities.id = opportunities_cstm.id_c where
opportunities.deleted=0 AND (accounts_opportunities.deleted is null OR
accounts_opportunities.deleted=0) AND (accounts.deleted is null OR
accounts.deleted=0) AND opportunities.deleted=0 ORDER BY
opportunities.name

We see several places where there are apparent missing spaces. Should we put some spaces in the source and then re-run? That would make sense to us but I'll look to see your reply.

Thank you again for all of your help.

Bob

[andopes]

Hi Bob

The error: there is no space in the following piece of the query

LEFT JOIN users ON opportunities.assigned_user_id = users.idLEFT
JOIN accounts_opportunities ON opportunities.id = accounts_opportunities.opportunit y_id

I don´t know why it is happening.
Which sugar version are you using?
Are you able to reproduce it in a clean installation, the same version?
Can you attach the content of the modules/Opportunities/Opportunity.php file?

Cheers