4

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

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

DECLARE @max int = @maxKnownId;
frictionlesspulley
  • 11,070
  • 14
  • 66
  • 115
Pravin Dingore
  • 127
  • 1
  • 8

2 Answers2

3

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.

Michael Rys
  • 6,684
  • 15
  • 23
  • Michael, in my case I want to regenerate an obsolete partition. I expect to find the partition to drop using a query (SELECT MAX ..) and then specify that value to ALTER TABLE DROP PARTITION. Does that make sense? Is there a better way? – Iain Mar 22 '17 at 05:37
  • 1
    In this case you will have to write a script to get the value and then submit another script that contains the value. You can even use the first script to generate the second script. – Michael Rys Mar 23 '17 at 01:56
1

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;
Erki M.
  • 5,022
  • 1
  • 48
  • 74
Pravin Dingore
  • 127
  • 1
  • 8
  • Thanks Pravin... Note that you could use DateTime.Now.Ticks instead of the Max(Id) value if just need to disambiguate. DateTime.Now will be evaluated at script compile time. That way you get a most likely unique seed that is guaranteed to be bigger than the previous job. – Michael Rys Oct 17 '16 at 22:32
  • Time based id generation might not be ideal. please refer my earlier post http://stackoverflow.com/questions/38964160/guid-newguid-always-return-same-guid-for-all-rows – Pravin Dingore Oct 20 '16 at 17:22
  • In general you are correct, however, as I point out in my reply to your post, DateTime.Now gets snapshotted once per script at compile time thus giving you a script specific basis to add your row numbers too (assuming that the intervals are large enough for the added row numbers not to generate overlapping ranges). – Michael Rys Oct 20 '16 at 19:33