SQL Server Dummy Objects and Group By

building semantic layer
1

I created a dummy object in sql server. The select is ‘A’. It works great when I use it with a detail object. When I include a properly designed :wink: measure…one with a function such as sum() around the table.column, it won’t work. It warns me that…

GROUP BY expressions must refer to column names that appear in the select list

The ‘A’ DOES appear in the select and the GROUP BY. Shaun (my local top notch DBA :-)) tricked it into working by having me put a max() around my ‘A’ to get it OUT of the GROUP BY. It worked! Why did I get the error in the first place? :confused:

Cindy Clayton :us: (BOB member since 2002-06-11)

2

The fact that the ‘A’ appeared in the Group By should indicate that it was not recognized as an aggregate, but it was originally. But how did you create the dummy in the first place as ‘A’ as a Sum() of a field? Very strange. :roll_eyes: In any case SQL will not allow you to use an alias in the group by, you have to refer to the actual definition…

blom0344 :netherlands: (BOB member since 2002-09-04)

3

Cindy,

I assume it fails if it’s the ONLY other object that is being selected. HARD-CODED values should not appear in GROUP-BY clauses, as they are un-needed, but they don’t harm anything. Normally you just want the other dimensions to appear in the group-by.

With at least one valid group-by object, I think you’d be ok.

-RM

digpen :us: (BOB member since 2002-08-15)

4

I am currently on Oracle so cannot say for sure about SQL Server but Oracle has no problems running the following…

select 
 'A',  'B',  unit_id,  max(employee_number)
from  x.unit
group by  
 'A',  'B',  unit_id

and logically the above is similar to

select 
 'A',  'B',  unit_id,  max(employee_number)
from  x.unit
group by  
 unit_id

edit: digpen, in Oracle it runs fine even if I use only one constant with the aggregated field!!

avaksi :us: (BOB member since 2002-08-22)

5

I was sure ORACLE runs fine with only one item in the group by… in fact, it won’t complain if you don’t have the hard-coded values in the group by at all.

My inexperience with SqlServer syntax shows here, since I don’t know how it will respond.

digpen :us: (BOB member since 2002-08-15)

6

It works like this…

select ‘a’, other_column1, other_column2
from table

It fails like this…

select ‘a’, other_column1, sum(other_column2)
from table
group by ‘a’, other_column1

and gives me the error that sounds like it isn’t happy that ‘a’ doesn’t appear in the group by…but it does! :confused:

Yes, I’d have no problem with Oracle either! I’m also inexperienced with SQLServer :yesnod:

Cindy Clayton :us: (BOB member since 2002-06-11)

7

:yesnod:

It is simply ‘a’ in the select box of the dimension object. To trick it Shaun suggested…

sum(‘a’)

Cindy Clayton :us: (BOB member since 2002-06-11)

8

I did this sample query on sql server and it worked well.

Select ‘a’, custnumber, count(custname) test from customer
group by custnumber.

Initially, I included ‘a’ in the group by clause and reproduced the error that you indicated. I removed ‘a’ from the group by clause and it worked :confused: . did you try that?

Ravi.

Ravi_Pazhani (BOB member since 2002-08-20)

9

That was my workaround. I changed ‘a’ to sum(‘a’) to get the ‘a’ out of the group by. The error complained that the ‘a’ wasn’t in the group by but it WAS :yesnod: . The error went away when I made sure it WASN’T in the group by. Very confusing! :confused:

Cindy Clayton :us: (BOB member since 2002-06-11)

10

SQL Server doesn’t allow use of Alias name in the group by expression and ‘A’ is being treated as alias name rather than a column name. Another SQL SERVER special…

dcdas :us: (BOB member since 2002-06-17)

11

It is the same in Sybase. Basically ‘A’ doesn’t have a column name because it isn’t defined in a table

Pauline :australia: (BOB member since 2002-08-20)

12

Just one more reason to use Oracle :wink: .

Thanks everyone!

Cindy Clayton :us: (BOB member since 2002-06-11)

13

… AND it is the same with DB2. DB2 does not allow for Group by clauses which are either constants (numbers, character strings, or expressions) so you have the problem there as well.

Even if you use ORACLE you might encounter this problem. We had a 3 machine parallel Sever Instance (really huge database on it) running at a customer site and for some bug (as I recall) in combination with the hardware product this query either crashed the server or returned random results and similar things… But this was in one of the earlier V8 versions, so don’t panic.

For the DB2, btw, there are additional parameters in the .PRM file, which control the behaviour of SQL generation:
GROUPBY_EXCLUDE_COMPLEX=Y
GROUPBY_WITHOUT_CONSTANT=Y
The manuals originally said, that these are for DB2 only, in my V5.1.5 it says you can use the latter for other DBs as well by copying it into the [RDBMS] (GENERAL) section of the .PRM file.

HTH, Walter

Walter Muellner :austria: (BOB member since 2002-10-21)

14

I am just curious which one is handling it ANSI92 compliant? Oracle or the other DB’s?

Andreas :de: (BOB member since 2002-06-20)