Duplicate alias: name error while grouping

cpqgroup-byorderorderitemsoql

We are using bundle products in salesforce and I am trying to display the Order products in a PDF document which I am creating using VF page.

In the Order detail page the Bundle products appear grouped with its parent in perfect order. But when I query it and try to add it to the PDF for displaying the order in which they gets displayed get shuffled up and products from different bundles get showed next to the product which belongs to some other bundles.

As I can't use any particular field from the Product Object or Order product object, and as I am not able to think of any logic that would help me in displaying the bundle products in Order along with its parent as in the Order detail page's Order product related list. I am trying to group them by the family field in Product object and display.

But when I try to add the Group By condition, I am getting an error saying "duplicate alias: Name". Below is the query that I am trying to add.

SELECT OrderItemNumber, Product2Id, Product2.Name,Product2.Service_Type__c, Product2.Family, OrderId, Order.Name, Description, ListPrice, UnitPrice, Quantity, TotalPrice FROM OrderItem WHERE Hide_On_Document__c = FALSE AND Line_Item_Status__c != 'Cancelled' AND OrderId =: objOrder.Id GROUP BY Product2.Family

Can someone please let me know why this is happening. Also, can someone please let me know if there is any other way that this could be done by using ORDER BY Clause or any other logic.

Thanks in advance.

Best Answer

In an aggregate query result, each field is aliased so you can identify them. If you try to select them without aliasing, one is determined for you. You can see a similar error if we try:

SELECT Account.Name, Contact.Name FROM Case GROUP BY Account.Name, Contact.Name

To fix it, you have to add aliases:

SELECT Account.Name accountName, Contact.Name contactName FROM Case GROUP BY Account.Name, Contact.Name

Also, note that you need to group or aggregate every field you select. It is not valid to query fields that are not grouped or aggregated.

Related Topic