U-SQL Compare Rowset Data in scalar expression

Question

I have gone through some articles that conversion is not possible however I have come across an issue where a value is been fetched in the rowset and needs to be used in the scalar expression.

ColumnA is a string value and ColumnB is an Int..

@RequiredData = SELECT [ColumnA] from @Input ORDER BY [ColumnB] ASC
      FETCH 1 ROWS;

IF((@RequiredData == "Something")) THEN
//DO SOMETHING 
END;

This fails in ADLA with an issue Rowset variable @RequiredData is not a scalar variable.

Answer 1

U-SQL is a declarative language and has limited control flow. You can convert your logic to set-based and that will work, eg

@someOtherData = 
    SELECT * FROM 
        ( VALUES
        (1),(2),(3),(4),(5),(6),(7),(8),(9)
        ) AS x(y);


@Input = 
    SELECT * FROM 
        ( VALUES
        ("Something",  1),
        ("Nothing",   2)
        ) AS x(ColumnA, ColumnB);


@RequiredData = SELECT [ColumnA] FROM @Input ORDER BY [ColumnB] ASC
      FETCH 1 ROWS;


// IF((@RequiredData == "Something")) THEN
@output =
    SELECT d.*
    FROM @someOtherData AS d
         CROSS JOIN
             @RequiredData AS rd
    WHERE rd.ColumnA == "Something";


OUTPUT @output
TO "/output/output.txt"
USING Outputters.Tsv();

This will output an empty file if the value is not "Something".