Error in use of order by in over clause

Hi,

I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,

Emil

I quote from Books Online the complete sytax related to the 3 key words :RANK, OVER and COUNT :

"

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of the rowset before the associated window function is applied.

"

so, the second is the correct SELECT

and

"

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

"

so using RANK is the right decision because you count STD_CLL_CNTR_KEYSTD_CLL_CNTR_KEY that i guess is a primary key

|||Thanks Gigi

Error in use of order by in over clause

Hi,

I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,

Emil

I quote from Books Online the complete sytax related to the 3 key words :RANK, OVER and COUNT :

"

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of the rowset before the associated window function is applied.

"

so, the second is the correct SELECT

and

"

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

"

so using RANK is the right decision because you count STD_CLL_CNTR_KEYSTD_CLL_CNTR_KEY that i guess is a primary key

|||Thanks Gigi

Error in SELECT clause: expression near '='.

When I use a construction like this in the sql designer of visual
studio 2005. i get the error that it doesnt understand this. However
the sql is just fine is there a way to make this work in visual studio?
i did set the prefix for the parameters so it does understands the @. in
other types of query.
DECLARE @.TEMP t_timestamp
this block in the designer:
SELECT @.TEMP=rprl_timestamp
FROM rap_rol
WHERE rol_id = 10Jeroen a crit :
> When I use a construction like this in the sql designer of visual
> studio 2005. i get the error that it doesnt understand this. However
> the sql is just fine is there a way to make this work in visual studio?
> i did set the prefix for the parameters so it does understands the @. in
> other types of query.
> DECLARE @.TEMP t_timestamp
^^^^^^^^^^^
what is this type '? t_timestamp ?
Do you use a CLR UDT ?

> this block in the designer:
> SELECT @.TEMP=rprl_timestamp
> FROM rap_rol
> WHERE rol_id = 10
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||its just a user defined datatype
it does not matter what type for my question. but in this case its a
datetime.
the problem is in the line SELECT @.TEMP=rprl_timestamp
where the @.temp is filled with the value of rprl_timestamp. its perfect
sql but the designer doesnt understand it.

Error in add count Expression

Dear All,
I'm creating a report in the CRM , i'm using group by clause and i need to
add count Expression in the group footer depending on the details area of
the group , i've added this code in a textbox :
= count(fieldname.value, scope)
it didn't work & it generates an error that the name of the scope that i
have entered is not valid group details area
i've entered the scope as string
could anyone help me ?
thanks in advanceIf the code you entered is literal and not an example, I would say it is a
syntax error first. I would not think that you would need to specify the
"scope" if this is simply a group footer, as it should know the count
expression is in the scope of the group it is in, but maybe I am missing
something from your description.
I would try:
=count(Fields!FieldName.Value)
Rodney Landrum
"Karim Mohamed" <k_a_r_i_m_._m_o_h_a_m_e_d@.link.net> wrote in message
news:O3pEyFaWGHA.4572@.TK2MSFTNGP03.phx.gbl...
> Dear All,
> I'm creating a report in the CRM , i'm using group by clause and i need to
> add count Expression in the group footer depending on the details area of
> the group , i've added this code in a textbox :
> = count(fieldname.value, scope)
> it didn't work & it generates an error that the name of the scope that i
> have entered is not valid group details area
> i've entered the scope as string
> could anyone help me ?
> thanks in advance
>

error in --> EXEC xp_cmdshell ''bcp "SELECT * FROM tbl a where a.flag=N" queryout

some one can tell if i can make a "where" clause inside the BCP , when i say : select * FROM tbl where flag=N , usually the where flag='N' works with the ' ' , but it gives no error when save the store procedure without it ' ' saves nicely the SP,

but

like this it saves well "SELECT * FROM tbl where flag=N" and

when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."

i dont now , any help

PS: perhaps at the end of the BCP > -c -T , must have some more or less

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

|||

Manivannan.D.Sekaran wrote:

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

yes i tryed both ways with 'N' and "N" and it says the same error message

with this

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt="N"" queryout "'

Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'N'.

with this says

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt= 'N' " queryout "'

Msg 102, Level 15, State 1, Procedure spDELTA_P1_RANGEL_STOCKS, Line 59

Incorrect syntax near ' " queryout "'.

|||

Please copy and paste the following code,

Instead of 2 single quotes you are trying with 1 double quote.

Code Snippet

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''" queryout F.txt -c -T'

|||

THANK YOU A LOT

and it was only a little thing like a '