Query on subquery containing JOINS in Salesforce SQL (SOQL)

Question

SELECT 
    Email_address, COUNT(Order_date)
FROM 
    (SELECT 
         cust.Email_address, COUNT(ol.Variant_name), ord.Order_date
     FROM 
         DMW_Order_Line_v3 ol
     JOIN 
         DMW_Order_v3 ord ON ol.Unique_transaction_identifier = ord.Unique_transaction_identifier
                          AND ol.Brand_country = ord.Brand_country
     JOIN 
         DMW_Customer_v3 cust ON ord.Email_address = cust.Email_address
                              AND ord.Brand_country = cust.Brand_country
     WHERE 
         ord.Brand_country = 'kiehls-emea_CZ'
         AND cust.Address_country = 'CZ'
         AND cust.Optin_email != 'False'
         AND ol.Line_status = 'SHIPPED'
         AND ol.Variant_name = 'Sample'
     GROUP BY 
         cust.Email_address, ord.Order_date
     HAVING 
         COUNT(ol.Variant_name) >= 4)
GROUP BY 
    Email_address

Please, forgive me that I'm posting the whole body of the code. But it might be helpful somehow, who knows. As you can see it is a query on subquery containing joins. I'm using Salesforce SQL. When I run the code, I get this error:

Error saving the query field. Incorrect syntax near the keyword 'GROUP'.

What am I doing wrong? Besides being a noob ;-)

You probably just need a table alias after the subquery . . . . `HAVING COUNT(ol.Variant_name) >= 4) t`. — Gordon Linoff, Nov 09 '20 at 19:13
It was my first idea. I've added some random name like 'abc' after the subquery, to SELECT statement and to GROUP BY statement, but then this shows up: Error saving the Query field.No column name was specified for column 2 of 'abc'. — Amberjack, Nov 09 '20 at 19:17

score 0 · Accepted Answer · answered Nov 09 '20 at 19:27

0

You don't need the count column in the subquery:

SELECT Email_address, COUNT(*)
FROM (SELECT cust.Email_address, ord.Order_date
      FROM DMW_Order_Line_v3 ol JOIN
           DMW_Order_v3 ord
           ON ol.Unique_transaction_identifier = ord.Unique_transaction_identifier AND
              ol.Brand_country = ord.Brand_country JOIN
           DMW_Customer_v3 cust
           ON ord.Email_address = cust.Email_address AND
              ord.Brand_country = cust.Brand_country
      WHERE ord.Brand_country = 'kiehls-emea_CZ' AND
            cust.Address_country = 'CZ' AND
            cust.Optin_email <> 'False' AND
            ol.Line_status = 'SHIPPED' AND
            ol.Variant_name = 'Sample'
      GROUP BY  cust.Email_address, ord.Order_date
      HAVING COUNT(ol.Variant_name) >= 4
     ) e
GROUP BY Email_Address

answered Nov 09 '20 at 19:27

Gordon Linoff

1,242,037
58
646
786

Oh my God! This works! Thank you! :-) Are you able to explain why removing a column from subquery helped? I'm really just a noob as I can see... – Amberjack Nov 09 '20 at 19:39
@Amberjack . . . As the error said, it didn't have a name. You either needed to give it a name or remove it. Because it's not being used, removing it makes sense. – Gordon Linoff Nov 09 '20 at 20:41
Okay. The error referred to the subquery, I see. Well, thank you a million! Now I just need to learn how to rename the columns, because SOQL is kinda odd with it... – Amberjack Nov 09 '20 at 21:08

Query on subquery containing JOINS in Salesforce SQL (SOQL)

1 Answers1