42

I am wondering how you would do something simple like this in SOQL.

select first_name as name from tableA

I can not find any documentation on Aliasing a column which is very common in all forms of SQL, does anyone know if this is possible?

3
43

SOQL has very limited aliasing support. You can alias the table, e.g. SELECT a.name FROM Account a, but that's of very little use. In most contexts you cannot alias fields.

The one exception is for queries that use aggregations, like SELECT COUNT(Id) recordCount FROM Account allows you to alias the result of the aggregation from it's default expr0 to recordCount.

This probobly doesn't do what you're looking for, and you'll have to use some sort of transform in apex, middleware, or on the API client (depending on what and how you're building) to alias columns in non-aggregate use cases.

13

You can use object aliasing in any SOQL query

From the SOQL reference guide.....

Alias Notation

You can use alias notation in SELECT queries: SELECT count() FROM Contact c, c.Account a WHERE a.name = 'MyriadPubs'

To establish the alias, first identify the object, in this example a contact, and then specify the alias, in this case “c.” For the rest of the SELECT statement, you can use the alias in place of the object or field name.

The following are SOQL keywords that can’t be used as alias names: AND, ASC, DESC, EXCLUDES, FIRST, FROM, GROUP, HAVING, IN, INCLUDES, LAST, LIKE, LIMIT, NOT, NULL, NULLS, OR, SELECT, WHERE, WITH

See page 2 of the reference guide here

You can only use field aliasing with aggregate expressions. For more info on aliasing with aggregates, take a look here

http://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/sforce_api_calls_soql_select_groupby_alias.htm

3
  • 3
    Note that this is an object alias, not a field alias. Salesforce only allows field aliasing in aggregate expressions. i.e. GROUP BY – Mark Pond Jun 2 '14 at 21:55
  • 3
    Indeed. I didn't make that clear, will edit the answer to reflect. Thanks – Chris Duncombe Jun 2 '14 at 21:56
  • 1
    @ChrisDuncombe what is the point of object aliasing? Is there any practical use case in SOQL – Eric Apr 28 '17 at 21:43
8

As Chris mentioned, you can only alias aggregated fields. The syntax to use is like this:

SELECT Status, COUNT(id) Total
FROM Case
GROUP BY Status

Notice, no "AS" keyword like in vanilla SQL

1
4

Alias notation also works with the toLabel() function in both SOQL and SOSL queries.

The toLabel function supports aliasing. In addition, aliasing is required when the query includes the same field multiple times. For example:

FIND {Joe} RETURNING Lead(company, toLabel(Recordtype.Name) AliasName)

SOSL Reference: toLabel(fields)

The above excerpt, shows the use of the alias notation with toLabel function in a SOSL query, however, the same syntax works for SOQL queries.

For example, if we have the following Account records in Salesforce:

+--------------+--------------------+
| Name         | BillingCountryCode | 
+--------------+--------------------+
| A            | US                 |
| B            | IE                 |
| C            | CA                 |
+--------------+--------------------+

Without the toLabel alias we would only be able to select one of BillingCountryCode or toLabel(BillingCountryCode). If we tried to select both without an alias we would run into an error:

duplicate field selected: BillingCountryCode

Now, if we use alias notiation, we can query for both the Billing Country Code as well as the label as follows:

SELECT
    Name,
    BillingCountryCode,
    toLabel(BillingCountryCode) BillingCountryLabel
FROM Account

Then the above query would produce a query result as follows:

+--------------+--------------------+---------------------+
| Name         | BillingCountryCode | BillingCountryLabel | 
+--------------+--------------------+---------------------+
| A            | US                 | United States       |
| B            | IE                 | Ireland             |
| C            | CA                 | Canada              |
+--------------+--------------------+---------------------+
4
  • 1
    Interestingly the toLabel(field) alias syntax still allows you to return the usual SObject type back instead of AggregateQuery. The label data is only accessible via .get('fieldName') though of course. – Charles T May 3 '18 at 11:58
  • Only picklist fields, field history, or platform entity fields (or Record Type Name, Description in the SELECT list) support translation. =( – Jeremy A. West Sep 16 '19 at 21:01
  • I did the same querry but I am not able to access to Opportunity.BillingCountryLabel – Raphael D Dec 16 '19 at 12:28
  • 1
    @RaphaelD You need to use SObject.get() to access the aliased label. e.g String label = (String) opp.get('BillingCountryLabel') – Alan Morey Dec 16 '19 at 20:02
4

Aliasing like this:

select first_name as name from tableA

isn't possible in Salesforce SOQL.

2

Directly write the alias name for example: select AccountID account ,count(id) countid from Account

you can also retrieve using get() function by passing the alias name in ' ' quotes example: object_reference.get(;

1

I think the answer is yes you can. Kind of.

The OP is asking whether you can do this like you can in SQL:

select first_name as name from tableA

So whilst SOQL doesn't support the above, you can achieve the same effect like this:

AggregateResult[] people = [select Firstname Name from tableA group by Id, Firstname];

I can see many others have mentioned aggregate queries but nobody has made it clear that you can take advantage of the aliasing possible with the group by query - the trick is to group by something unique like the Id. All the other fields you want to select out must be in the group by clause as well.

Access the result like so:

for(AggregateResult person:people) { 
  String theirName = person.get('Name');
}

Not sure why this isn't used more widely - it's what I do when I need to alias like you do in regular SQL.

2
  • I think the reason to avoid this, when not needed, is performance, cause this will require extra database effort to group by Id and Name. – Jeferson Chaves Feb 20 at 19:02
  • agreed Jeferson! – edralph Feb 21 at 20:15
0

Finally we have found decision how to "rename" column in SOQL without using standart Aggregate function that requires Group by clause in the end, and unfortunately doesn`t allow to export more that 2000 rows in query.

The best way is using Format(Field) Custom_Name. It could be tricky way to use this with some date values or etc. cause function changes format of values in field.

The name of Hero who advice it - Roman Bielei

New contributor
user96143 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.