CODEWITHSUNDEEP
u-sql
Pravin Dingore
Pravin Dingore

Reputation: 127

Convert Rowset variables to scalar value

Is it possible to convert rowset variables to scalar value for eg.

@maxKnownId =
    SELECT MAX(Id) AS maxID
    FROM @PrevDayLog;

DECLARE @max int = @maxKnownId;

Upvotes: 4

Views: 3274

Answers (2)

Pravin Dingore
Pravin Dingore

Reputation: 127

Thanks for input, below is the business cases -

We have catalog data coming from source for which we need to generate unique ids. With ROW_NUMBER() OVER() AS Id method we can generate unique id. But while merging new records it changes ids of existing records also and causes issues with relational data

Below is simple solutions 

//get max id from existing catalog

@maxId =
    SELECT (int)MAX(Id) AS lastId
    FROM @ExistingCat;

//because @maxId is not scalar, we will do CROSS JOIN so that maxId is repeated for every record.
//ROW_NUMBER() always starts from 1, we can generate next Id with maxId+ROW_NUMBER()

@newRecordsWithId =
    SELECT (int)lastId + (int)ROW_NUMBER() OVER() AS Id,
           CatalogItemName
    FROM @newRecords CROSS JOIN @maxId;

Upvotes: 1

Michael Rys
Michael Rys

Reputation: 6684

There is no implicit conversion of a single-cell rowset to a scalar value in U-SQL (yet).

What are you interested in using the value for?

Most of the time you can write your U-SQL expression in a way that you do not need the scalar variable. E.g., if you want to use the value in a condition in another query, you could just use the single value rowset in a join with the other query (and with the right statistics, I am pretty sure that the optimizer would turn it into a broadcast join).

If you feel you cannot easily write the expression without the rowset to a scalar, please let us know via http://aka.ms/adlfeedback by providing your scenario.

Upvotes: 3

Related Questions