+ Start a Discussion
JeffStevensJeffStevens 

Finding duplicates, SOQL in force.com explorer

I'm using Fore.com Explorer to test this soql...

 

Select RL_Batch_Line_Number__c, count(Id)
    from rebate_History__c  
    Where RL_Batch_Number__c = 999
    group by RL_Batch_Line_Number__c
    Having Count(Id) > 1

 

But I get a malformed query Error at my "group by" clause.

 

Anybody know what I might be doing wrong?

Best Answer chosen by Admin (Salesforce Developers) 
JeffStevensJeffStevens

Well - it must have something to do with my custom field.  That field is USED in a formula (it is NOT a formula though - just used in a formula).  Anyway, this works...

 

Select Account__c, count(Id)
from rebate_History__c
Where RL_Batch_Number__c = 999
Group by Account__c
Having Count(id) > 1

 

I think I can work with this.  I'm going to go ahead and mark this as the solution.

All Answers

Starz26Starz26

Seems like a field name problem.

 

Copying your code and replacing the field names with ones in my org and it performs the soql

UVUV

Execute the Query in parts to find root cause...

UVUV

 Try this First--

Select RL_Batch_Line_Number__c, count(Id)
    from rebate_History__c  
    group by RL_Batch_Line_Number__c
    Having Count(Id) > 1

 

Seems problem with Where Clause...

JeffStevensJeffStevens

Ya - I can do these and they work just fine.  It's as soon as I try to group it..

 

Select RL_Batch_Line_Number__c
from rebate_History__c
Where RL_Batch_Number__c = 999
order by RL_Batch_Line_Number__c

 

(Returns an ordered list...)

 

 

Select Count(RL_Batch_Line_Number__c)
from rebate_History__c
Where RL_Batch_Number__c = 999

 

(Returns one number)

JeffStevensJeffStevens

No - I think it's the Group by clause that causing it.

 

Here is the message when I take the "having" clause out...

 

MALFORMED_QUERY:
group by RL_Batch_Line_Number__c
^
ERROR at Row:3:Column:10
field 'RL_Batch_Line_Number__c' can not be grouped in a query call

 

JeffStevensJeffStevens

Well - it must have something to do with my custom field.  That field is USED in a formula (it is NOT a formula though - just used in a formula).  Anyway, this works...

 

Select Account__c, count(Id)
from rebate_History__c
Where RL_Batch_Number__c = 999
Group by Account__c
Having Count(id) > 1

 

I think I can work with this.  I'm going to go ahead and mark this as the solution.

This was selected as the best answer