CODEWITHSUNDEEP
sqlsql-servert-sql
My9to5
My9to5

Reputation: 93

"Incorrect syntax" and "Cannot be bound" when adding alias to Grouping Sets?

I have a common column (Name0) that I need to select from one of my tables. So, I alias it out, but then it starts throwing errors. The two columns I try to select show they could not be bound, followed by my aggregate having syntax errors. Finally, the grouping by set alias' show incorrect syntax. I am at a loss.

Select I.ARPDisplayName0,
      SR.name0 = coalesce(SR.name0,concat('Total ',sum(1)))
 
 From  v_GS_INSTALLED_SOFTWARE I
 inner join  v_R_System SR on SR.resourceID = I.ResourceID
 inner join  v_GS_OPERATING_SYSTEM OS on OS.ResourceID = I.ResourceID

 WHERE
    ARPDisplayName0 = 'Adobe Acrobat 9 Pro' or 
    ARPDisplayName0 = 'Bomgar' or
    Caption0 = 'Microsoft Windows 7 Professional'

 Group By Grouping Sets ( 
                        (I.ARPDisplayName0,SR.name0),
                        (I.ARPDisplayName0),
                        (OS.Caption0,SR.Name0),
                        (OS.Caption0)
                        ()
                        );

Specific errors:

Incorrect Syntax expecting (or Select" in coalesce(SR.name0,concat('Total ',sum(1))) and in the grouping sets alias.

The other error is:

the Multipart Identifier could not be bound in the SELECT statement "I.ARPDisplayName0" and "SR.name0"

Upvotes: 0

Views: 133

Answers (1)

Stu
Stu

Reputation: 32589

The issues arise from just from a simple misunderstanding of how to use alias.

When providing the name of an alias, you can choose to use either an = assignment or the (optional) keyword as - it's really personal preference which you use but I prefer the latter option and keep = for actually assigning values to local variables.

The line SR.name0 = coalesce(SR.name0,concat('Total ',sum(1))) is causing an error since SQL Server interprets the SR. as an alias reference itself which can't be used in this context - the intended name is just "name0"; constructing it as coalesce(SR.name0,concat('Total ',sum(1))) as name0 would make the intent clearer.

Likewise, when using brackets [] around object names, the alias dot-notation is not included, so [I.ARPDisplayName0] is just I.[ARPDisplayName0]; in this case the brackets are completely optional as they are only required if there is a clash with a reserved word eg [date] or the use of certain characters such as a space [my column name]. Again it's personal preference but I prefer to only use them where necessary to remove "noise" and improve readability.

Upvotes: 1

Related Questions