VBA error while using SQL "with rollup"
 

Hi,

It would be so grateful if anyone can help me on the below issue.

I am using an SQL string to extract the data from SQL server, here I
have to sort the data in excel exactly like when we are using "with
rollup" in SQL (or like pivot table in excel, but this is very
difficult for my project).

My query look like this:
SqlStr = "Select Distinct LOB,THIRDPARTY_FLAG,LANGUAGE,CENTRE_NAME
From scorecard" & _
" Where LOB = 'BILLING'" & _
" group By LOB,THIRDPARTY_FLAG,LANGUAGE,CENTRE_NAME
with rollup"

but when I'm using this, I am getting below error:

Run-time error '-2147217900 (80040e14)':
Incorrect syntax near the keyword 'with'. If this statement is a
common table expresssion or an xmlnamesspaces clause, the previous
statement must be terminated with a semicolon.



Many thanks in advance.
Ratheesh

VBA error while using SQL "with rollup"
 

I haven't used the Rollup - so, apologies if there is no value in my
post but:
- do you need the DISTINCT in there?
- which field is supposed to be totalled up with the Rollup if in you
sql it seems that every field is being grouped by...? Don't you need a
field that has an aggregate function?
Also, when googling i found this:
ROLLUP doesn't support DISTINCT in aggregate functions or the GROUP BY
ALL clause.




On Jan 11, 2:04*pm, Ratheesh wrote:
Hi,

It would be so grateful if anyone can help me on the below issue.

I am using an SQL string to extract the data from SQL server, here I
have to sort the data in excel exactly like when we are using "with
rollup" in SQL (or like pivot table in excel, but this is very
difficult for my project).

My query look like this:
SqlStr = "Select Distinct LOB,THIRDPARTY_FLAG,LANGUAGE,CENTRE_NAME
From scorecard" & _
* * * * * * * " Where LOB = 'BILLING'" & _
* * * * * * * * " group By LOB,THIRDPARTY_FLAG,LANGUAGE,CENTRE_NAME
with rollup"

but when I'm using this, I am getting below error:

Run-time error '-2147217900 (80040e14)':
Incorrect syntax near the keyword 'with'. If this statement is a
common table expresssion or an xmlnamesspaces clause, the previous
statement must be terminated with a semicolon.

Many thanks in advance.
Ratheesh

VBA error while using SQL "with rollup"
 

On Jan 11, 7:41*pm, AB wrote:
I haven't used the Rollup - so, apologies if there is no value in my
post but:
- do you need the DISTINCT in there?
- which field is supposed to be totalled up with the Rollup if in you
sql it seems that every field is being grouped by...? Don't you need a
field that has an aggregate function?
Also, when googling i found this:
ROLLUP doesn't support DISTINCT in aggregate functions or the GROUP BY
ALL clause.

On Jan 11, 2:04*pm, Ratheesh wrote:







Hi,

It would be so grateful if anyone can help me on the below issue.

I am using an SQL string to extract the data from SQL server, here I
have to sort the data in excel exactly like when we are using "with
rollup" in SQL (or like pivot table in excel, but this is very
difficult for my project).

My query look like this:
SqlStr = "Select Distinct LOB,THIRDPARTY_FLAG,LANGUAGE,CENTRE_NAME
From scorecard" & _
* * * * * * * " Where LOB = 'BILLING'" & _
* * * * * * * * " group By LOB,THIRDPARTY_FLAG,LANGUAGE,CENTRE_NAME
with rollup"

but when I'm using this, I am getting below error:

Run-time error '-2147217900 (80040e14)':
Incorrect syntax near the keyword 'with'. If this statement is a
common table expresssion or an xmlnamesspaces clause, the previous
statement must be terminated with a semicolon.

Many thanks in advance.
Ratheesh

Hi AB,

Thanks for your reply.
DISTINCT is mandatory here for my data, else it would repeat so many
times.
Actually there is no issues with distinct while using group by or
rollup in SQL. The only issue when I'm using the same string in VBA.
I'm not able to identify what could be the reason....!

Can anyone experience this issue.. please help...

Many thanks
Ratheesh