function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Kevin Garcia 12Kevin Garcia 12 

distinct values soql

Hello, I am trying to perform a SOQL query in Salesforce. 

currently, when I search this query: 

select Id, Account.Id, Account.WBCNumber__c from Opportunity
WHERE FiscalQuarter__c = 'Q2 2020'
and 
AccountBoxoutNumber__c IN ('1-1206799572','1-1206525485','1-1206525485')

I only receive two values because the last two AccountBoxoutNumber values are repeating in the query. is there a way to make SOQL provide me with unique values even when there are repetitions in the Query?
 
Abdul KhatriAbdul Khatri
Hi Kevin,

Little confuse about you saying receive only two values.

What distinct value you are looking for? Are you looking Distinct Values for Account.WBCNumber__c? Here is normally the way you get distinct value
 
SELECT Account.WBCNumber__c 
FROM Opportunity
WHERE FiscalQuarter__c = 'Q2 2020' AND AccountBoxoutNumber__c IN ('1-1206799572','1-1206525485','1-1206525485') 
GROUP BY Account.WBCNumber__c
Not sure if this help but may provide you some idea.
 
Kevin Garcia 12Kevin Garcia 12
Yes, I have a list of Opportunities I am trying to Query. (The example below is meant to demonstrate the fact that account numbers are duplicated in the query)

The query contains 3 numbers referring to 3 accounts. The last two are the same numbers referring to the same account.

When i run the query, SOQL only returns two values not 3.

I would like the query to return 3 values, even if the account numbers are the same.
Kevin Garcia 12Kevin Garcia 12
Hi Abdul,
The incorrect result is what I receive in the query. The Correct result below is what I am trying to achieve from my query.

Incorrect result:
"_"    "Id"    "Account"    "Account.Id"    "Account.WBCNumber__c"
"[Opportunity]"    "0063g00000Bld6nAAB"    "[Account]"    "0013g00000YpFHLAA3"    "1-1206525485"
"[Opportunity]"    "0063g00000BlfTtAAJ"    "[Account]"    "0013g00000YpFpgAAF"    "1-1206799572"

Correct result:
"_"    "Id"    "Account"    "Account.Id"    "Account.WBCNumber__c"
"[Opportunity]"    "0063g00000Bld6nAAB"    "[Account]"    "0013g00000YpFHLAA3"    "1-1206525485"
"[Opportunity]"    "0063g00000BlfTtAAJ"    "[Account]"    "0013g00000YpFpgAAF"    "1-1206799572"
"[Opportunity]"    "0063g00000BlfTtAAJ"    "[Account]"    "0013g00000YpFpgAAF"    "1-1206799572"
Abdul KhatriAbdul Khatri
Hi Kevin,

You by yourself saying the last two basically are the same AccountNumber, it doesn't matter how many time you put the same number it will only give you the amount of record it has.

How many Opportunities you have for those accounts in the system?
Abdul KhatriAbdul Khatri
Hi Kevin, 

I saw your examples. It is a very strange requirements and trying to see the perpective you are trying to achieve with this.
Kevin Garcia 12Kevin Garcia 12
I only have opportunity for this account.

however, i have a lot of orders that I am re-parenting to a new account and I need to query the opportunity id, account id, and account number because there are many orders from different customers that need to be re-parented.
Abdul KhatriAbdul Khatri
The below code may get your strange requirement achieved. Please try
 
List<String> accountWBCNumberList = New List<String>{'1-1206525485','1-1206799572','1-1206799572'};
        Map<String, Opportunity> oppMap = new Map<String, Opportunity>();
        for (Opportunity opportunity : [SELECT Id, Account.Id, Account.WBCNumber__c
                                        FROM Opportunity
                                        WHERE FiscalQuarter__c = 'Q2 2020' 
                                        AND AccountBoxoutNumber__c = :accountWBCNumberList])
        {
            oppMap.put(opportunity.Account.WBCNumber__c, opportunity);
        }
        
        if(oppMap == null || oppMap.isEmpty()) return;
        
        List<Opportunity> opportunityList = new List<Opportunity>();
        
        for(String str : accountWBCNumberList){
            
            opportunityList.add(oppMap.get(str));
            
        }
        
        system.debug(opportunityList);

 
Kevin Garcia 12Kevin Garcia 12
MALFORMED_QUERY: unexpected token: List
Kevin Garcia 12Kevin Garcia 12
Hello, I think I am trying to achieve a list in this case. However, i received the error above when trying the query in developer console and inspector.
Abdul KhatriAbdul Khatri
Hi Kevin,

it is working fine at my end.

 
Kevin Garcia 12Kevin Garcia 12
not sure why its not working on my end. I received this error in the developer console 
"The query has to start with 'FIND' or 'SELECT'."
Abdul KhatriAbdul Khatri
Hi Kevin,

If you are running in Developer Console make sure you highlight the code and then run Execute Highlighted
Kevin Garcia 12Kevin Garcia 12
do you know how to execute the same using the salesforce inspector? I need to export the values and as csv file and dont know how to in developer console
Abdul KhatriAbdul Khatri
Hi,

Were you able to execute the code successfully?

You can do export via workbench https://workbench.developerforce.com/query.php easily, just run the SOQL as shown in the attachment
User-added image
Kevin Garcia 12Kevin Garcia 12
Hello, 

Im very sorry im trying to follow your instruction but I received another error pictures in the screenshot.User-added image