CODEWITHSUNDEEP
sql-servert-sql
Aparanjit
Aparanjit

Reputation: 41

I want to generate multiple NEXT VALUE for the sequence object I have created in the single select

We have a Sequence object created in our database, we are trying to pull multiple NEXT VALUEs from the single select statement, but it is not working as such. Here is the below query I am trying.

Select
   1 as PracticeId,
   NAME_1 as LocationN1Text,
   NAME_2 as LocationN2Text,
   federal_tax_id as FTaxId,
   NULL as NameAbb,

   NEXT VALUE for UPS.Seq_Communication AS CommunicationId1,

   NEXT VALUE for UPS.Seq_Communication AS CommunicationId2,

   NEXT VALUE for UPS.Seq_Communication AS CommunicationId3,

 FROM 
 [Staging_ETR] A

But the above query is getting the same sequence number for CommunicationId1, CommunicationId2 and communicationId3. But we need different sequential numbers for them. Can anyone help in this? How can I generate distinct sequence numbers in the same select query out of same Sequence object?

Upvotes: 0

Views: 1211

Answers (1)

GSerg
GSerg

Reputation: 78185

You are querying the same sequence several times within the same row.

It is documented that:

When the NEXT VALUE FOR function is used in a query or default constraint, if the same sequence object is used more than once, or if the same sequence object is used both in the statement supplying the values, and in a default constraint being executed, the same value will be returned for all columns referencing the same sequence within a row in the result set.

To generate distinct numbers, you need to query each number on its own row. If you want them in three columns, you can then apply the pivot clause.

You cannot just move NEXT VALUE FOR to a subquery, a CTE or an outer apply to trick it into generating distinct values. It is not supported there.

Upvotes: 2

Related Questions