I’m having some trouble understanding the correct behaviour for SQL++ in CB Analytics.
I was trying to run a query where I aggregate and group things together. Two of the fields were the same as it was a self join type thing. An example of the type of query I was trying to run is below. This threw an error (“msg”: “Duplicate alias definitions: description”). I tried it in N1QL, however, and it worked as is.
SELECT g.description AS account_name
, p.description AS parent_account_name
FROM `bucketname` g
LEFT JOIN `bucketname` p
ON g.parent_account_name = p.name
AND p.`type` = 'group'
WHERE g.`type` = 'group'
GROUP BY g.description
, p.description
I tried a few things, including the below query which turned out to work in Analytics without throwing the error seen before. It didn’t work in N1QL as the following error was raised (“msg”: “syntax error - at AS”), which is expected.
SELECT g.description AS account_name
, p.description AS parent_account_name
FROM `bucketname` g
LEFT JOIN `bucketname` p
ON g.parent_account_name = p.name
AND p.`type` = 'group'
WHERE g.`type` = 'group'
GROUP BY g.description AS account_name
, p.description AS parent_account_name
The behaviour in N1QL is what I’d expect for both but Analytics seems to be different. Is there a reason for this or is it a possible bug?
Thanks!