Dense_Rank Function is not Working in Query Generator

[Former Member]

Hi All Experts,

I am using DENSE_RANK Function in a Query Which is Running properly in SSMS,

But when I am running it in Query Generator it is giving Error:

[Microsoft][SQL Server Native Client 10.0][SQL Server]Must specify table to select from.[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Received Alerts' (OAIB) (s) could not be prepared.

Can Any One Please Help me To Get Problem Solved Without Using Dense_Rank Function.

Regards,

Gayatri Shukla.

[pvsbprasad · Accepted solution]

Hi,

Try to make Procedure and  call in the query manager

Regards,

Prasad

[Former Member · Accepted solution]

Hi Radek & Prasad,

Thank you For your Solution,Actually I am Using DENSE_Rank Function So that Each time I Run the Query The Ranking Should Start From 1.

As u both said to Create Function And Then Run the Query,I Did the Same But

When I am Running Query giving Date Selection,

then I Get Like this result

DoCnum    ItemCode        Docdate              Rank

1415001        A                01/06/2014             28

1415001        A                01/06/2014             28

1415002        B                03/06/2014             33

1415002        B                03/06/2014             33

1415002        B                03/06/2014             33

1415003        C                01/06/2014             50

But Actual Result Should Be

DoCnum    ItemCode        Docdate              Rank

1415001        A                01/06/2014             1

1415001        A                01/06/2014             1

1415002        B                03/06/2014             2

1415002        B                03/06/2014             2

1415002        B                03/06/2014             2

1415003        C                01/06/2014             3

Note: I Am Getting Above Result Because in Function Particular DocNum has got that Rank.

But Each time if I Run Query, Rank Should Start With 1 And So on

Hope you get My point

Thanks & Regards

Gayatri Shukla

[pvsbprasad · Accepted solution]

Hi,

Is it your Query in working in Sql  in same order with dense_rank() with out stored procudure ???

DoCnum    ItemCode        Docdate              Rank

1415001        A                01/06/2014             1

1415001        A                01/06/2014             1

1415002        B                03/06/2014             2

1415002        B                03/06/2014             2

1415002        B                03/06/2014             2

1415003        C                01/06/2014             3

Regards,

Prasad

[Former Member · Accepted solution]

Hi Prasad,

Yes it is Working Similarly in SQL without Stored Procedure

Thanks & Regards

[pvsbprasad · Accepted solution]

Hi,

Create Proc as test

as

begin

Query

End

--

the above doesn't change the order.untill orderby is given at end.can u recheck it again

Regards,

Prasad

[Former Member · Accepted solution]

Hi

I guess that the problem arises when you're using this function like this:

select * from dbo.Rank_v2() where docdate > '20070101' and docdate <= '20080101'

but instead you should modify your function to accept parameters and modify your query within the function accordingly. Something like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION dbo.Rank_v2
(
   @data1 datetime = null, @data2 datetime = null
)
RETURNS
@doksRank TABLE
(
  [dok] nvarchar(20), [item] nvarchar(50), [docdate] datetime, [Kwota] numeric(19,6), [Rank] int
)
AS
BEGIN
  -- Fill the table variable with the rows for your result set
  insert into @doksRank
  SELECT B.DocNum
   ,A.ItemCode
   ,B.DocDate
  ,A.Quantity
  ,DENSE_RANK() OVER (
  ORDER BY B.DocNum
  ) AS Rank1
  FROM INV1 A
  INNER JOIN OINV B ON A.DocEntry = B.DocEntry
  where b.DocDate >= @data1 and b.DocDate <= @data2
  RETURN
END
GO

In Query Manager you can call this function using query like this:

select * from dbo.Rank_v2('20070101', '20080101')

Kind regards,

Radek

[Former Member · Accepted solution]

Hi Prasad,

Thank you Very Much

It Got Solved

Thanks & Regards

Gayatri Shukla

[kothandaraman_nagarajan]

Hi,

What you mean by dense _rank function? Please post your complete query here to check.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Sir,

This is Just Eg.

Select B.DocNum,A.ItemCode,A.Quantity,DENSE_RANK()Over(Order By B.DocNum)As Rank From INV1 A

Inner join OINV B on A.DocEntry=B.DocEntry

In Any Query If I Use Dense_Rank Function it Does'nt Works in Query Generator

Thanks&Regards,

Gayatri Shukla

[Former Member]

Hi

As a workaround you may want to use T-SQL user defined function as a wrapper and call this function only from query generator.

Kind regards,

Radek

[Former Member]

Hi Radek,

Sorry,But I Did'nt Get you.

Can you Explain in detail.

Thanks And Regards,

Gayatri Shukla

[Former Member]

Hi

You may try to create function in SSMS and call this function from query manager.

For example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.Rank_v2
(
)
RETURNS
@doksRank TABLE
(
  [dok] nvarchar(20), [item] nvarchar(50), [Kwota] numeric(19,6), [Rank] int
)
AS
BEGIN
  -- Fill the table variable with the rows for your result set
  insert into @doksRank
  SELECT B.DocNum
  ,A.ItemCode
  ,A.Quantity
  ,DENSE_RANK() OVER (
  ORDER BY B.DocNum
  ) AS Rank1
  FROM INV1 A
  INNER JOIN OINV B ON A.DocEntry = B.DocEntry
  RETURN
END
GO

Then you can call this function in query manager like this:

select * from dbo.Rank_v2()

and you get your output.

Kind regards,

Radek