function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Tomasz JurekTomasz Jurek 

MALFORMED_QUERY: duplicate alias: Email

Gentelmen I know you have more than free time for challenges like this
I have downloaded all metadata for reports and rep types
I have found one specific fields that is in there 
Now I need to get all creators/modifiers of these reports 
Doesn't matter that I hit SOQL character limit 20,000 which is really 4000 for WHERE CLAUSE I have more than 2000 lines and ~70000 - doesn't matter. 
All I need is to get emails from creator or last modified but if I ran this query 
select CreatedBy.Email, COUNT(CreatedById), LastModifiedById 
from Report
where DeveloperName IN
(LOTS OF REPORTS BY DEV NAME,
LOTS OF REPORTS BY DEV NAME)
group by CreatedBy.Email, LastModifiedById
Above code works but if I want both emails fields I am interested in then I get
  
select CreatedBy.Email, COUNT(CreatedById), LastModifiedBy.Email
from Report
where DeveloperName IN
(LOTS OF REPORTS BY DEV NAME,
LOTS OF REPORTS BY DEV NAME)
group by CreatedBy.Email, LastModifiedBy.Email

I will get an MALFORMED_QUERY: duplicate alias: Email

I have went through ALIAS keywordk in SOQL & SOSL guide but without success.
Best Answer chosen by Tomasz Jurek
Omar Rajab 94Omar Rajab 94
Hi Tomasz Jurek, 
try this SOQL:  
select CreatedBy.Email, COUNT(CreatedById), LastModifiedBy.Email em
from Report
group by CreatedBy.Email, LastModifiedBy.Email

please marks as the best answer, if this help you!

regards,
Omar

All Answers

Omar Rajab 94Omar Rajab 94
Hi Tomasz Jurek, 
try this SOQL:  
select CreatedBy.Email, COUNT(CreatedById), LastModifiedBy.Email em
from Report
group by CreatedBy.Email, LastModifiedBy.Email

please marks as the best answer, if this help you!

regards,
Omar
This was selected as the best answer
Tomasz JurekTomasz Jurek

True Omar I actually did something like that, on first try I actually hit some errors that is why opened this ticket.
User-added image

Would you be able to help to improve this query by somehow only have 2 COUNT_DISTINCT on both emails in same query?

Sorry if I talk jibberish but too much coffe and this is EOD for me.
Tomasz JurekTomasz Jurek
I am trying something like that: 
SELECT COUNT_DISTINCT(CreatedBy.Email) creator, LastModifiedBy.Email 
FROM Report 
WHERE
DeveloperName IN 
(......)
GROUP BY LastModifiedBy.email

 
Omar Rajab 94Omar Rajab 94
it does not make sense to use COUNT_DISTINCT for CreatedBy.Email, because it is system field, and it must be filled!
Tomasz JurekTomasz Jurek
Hey Omar for me it makes absolute sense to identify distinct values for creators of reports as they are repeating a lot.
COUNT_DISTINCT()- returns the number of unique/distinct non-null field values in a query result

What do you think? Problem is with doing both fields with count_distinct in one query
Omar Rajab 94Omar Rajab 94
could you please explain the requirements in other words?
Tomasz JurekTomasz Jurek
I want to get distinct only values from emails of CreatedBy.Email or LastModifiedBy.Email in one SOQL query - instead of exporitning this to external tool and finding values there.
Please let me know if this makes sense for you?
Omar Rajab 94Omar Rajab 94
as i understood you need something like this: 
  
select  CreatedBy.Email, COUNT_DISTINCT(LastModifiedByID) count1,
COUNT_DISTINCT(CreatedByID) count2,  LastModifiedBy.Email el
from contact
group by CreatedBy.Email, LastModifiedBy.Email

or?
Tomasz JurekTomasz Jurek
Damn you're magnificetn I swear I did just that in my commercial org and it didnt work. Now it at least worked in some playground. Let me verify once I get back to work tomorrow but you made my day!