+ Start a Discussion
MegachuckmcMegachuckmc 

Help with SOQL Query in Apex Data Loader

I'm not sure what is going on but I have a SOQL query that reutrns all columns when I run the Query in the Apex Explorer, but when I use the same query in an Apex DataLoader Extract process it skips some of the columns and does not return any kind of error message.

 

Here is what I have:

 

<entry key="sfdc.extractionSOQL" value="SELECT Opportunity.Name, PricebookEntry.ProductCode, Opportunity.Account.Name, Opportunity.Probability, Opportunity.ExpectedRevenue, Opportunity.Type, Opportunity.HasOpportunityLineItem, Opportunity.Fiscal_Period__c,  Quantity, UnitPrice, TotalPrice
FROM OpportunityLineItem
 WHERE Opportunity.StageName = 'Forecasted' AND Opportunity.Probability > 0"/>

 

The results ignore the: Quantity, UnitPrice, TotalPrice fields from the OpportunityLineItem object

 

But when I run in the Apex Explorer it returns all columns (!)

 

Any help would be appreciated... My head hurts!

 

Thanks in Advance!

Mike

 

Best Answer chosen by Admin (Salesforce Developers) 
MegachuckmcMegachuckmc

OK I knew if I beat my head against the wall long enough I'd finally figure it out! It turns out that the reason why I was getting different results from Apex DataLoader running GUI vs. CLI was that I had specified the Opportunity object in my process-conf.xml file but the SOQL query was calling the OpportunityLineItem object. So when I changed this:

entry key="sfdc.entity" value="Opportunity" to entry key="sfdc.entity" value="OpportunityLineItem"

I got my missing columns of data. Problem Solved!

Message Edited by Megachuckmc on 06-05-2009 03:05 PM

All Answers

BritishBoyinDCBritishBoyinDC

When I've had this issue with the GUI tool, it's because I've just pasted the query into the Apex Data Loader, which for some reason doesn't work...so I've resolved it by going through the normal export process, selecting the fields on the main target object (n your case OpportunityLineItem) using the checkboxes, and then pasting the rest of the query referring to the related objects after the generated SOQL that's created when I checked the boxes...

 

Don't ask me why, but this seems to work...so I would try doing that via the UI and see if that works...

MegachuckmcMegachuckmc

Thanks for the help - unfortunately it didn't solve my problem.

 

If I cut and paste my SOQL query into the DataLoader GUI, as you suggested, and then run the Export it does indeed work!

 

However my dilemma is that I need to be able to run this from the CLI as a batch process and this is where I run into the initial problem that I posted. 

 

When I launch the process from the CLI it runs with no error messages but the resulting Export file is missing columns of data.

 

<entry key="sfdc.extractionSOQL" value="Select Quantity, TotalPrice, UnitPrice, Opportunity.Account.Name, Opportunity.Name, PricebookEntry.ProductCode, Opportunity.Probability, Opportunity.ExpectedRevenue, Opportunity.Type, Opportunity.HasOpportunityLineItem, Opportunity.Fiscal_Period__c FROM OpportunityLineItem 
WHERE Opportunity.StageName = 'Forecasted' AND Opportunity.Probability > 0"/>

 

The fields highlighted in Red are the ones that are missing in the Export file (when run form the CLI)

 

So the basic question is why running the process (with identical SOQL query) from the GUI and the CLI would return different results?

 

It is bizarre!

 

Mike

 

 

 

Message Edited by Megachuckmc on 06-05-2009 10:25 AM
MegachuckmcMegachuckmc

OK I knew if I beat my head against the wall long enough I'd finally figure it out! It turns out that the reason why I was getting different results from Apex DataLoader running GUI vs. CLI was that I had specified the Opportunity object in my process-conf.xml file but the SOQL query was calling the OpportunityLineItem object. So when I changed this:

entry key="sfdc.entity" value="Opportunity" to entry key="sfdc.entity" value="OpportunityLineItem"

I got my missing columns of data. Problem Solved!

Message Edited by Megachuckmc on 06-05-2009 03:05 PM
This was selected as the best answer