CODEWITHSUNDEEP
type-conversionu-sql
Rahul Wagh
Rahul Wagh

Reputation: 311

U-SQL DataType conversion

I am having a column in my sample data which contains 1 or 0 or "null". Due to "null" string I have declared the type of the column as string. In next operation I am taking only 1 & 0 and doing SUM(Value) where I am getting error cannot convert from "string" to "long?"

Upvotes: 1

Views: 1478

Answers (3)

ptfaferreira
ptfaferreira

Reputation: 592

To another approach to this problem you can do something like this. I'm suggesting the column name as "amount":

@input= SELECT CASE column WHEN amount THEN "0" ELSE amount END AS Amount FROM @extractedFields

@sum= SELECT SUM(Int32.Parse(Amount)) FROM @input

By this approach the input to the sum will only have in consideration the elements with 1 and 0.

Upvotes: 1

wBob
wBob

Reputation: 14389

You can use and inline function expression as described here. Here is a simple example:

@input = 
    SELECT * FROM
        ( VALUES
            ( (string)"0" ),
            ( (string)"1" ),
          ( (string)"null")
        ) AS x( yourCol );


@output =
    SELECT y
    FROM
    (
    SELECT (
           (Func<string, int?>)
           (yourString =>
               {
               int yourInt;
               return int.TryParse(yourString, out yourInt) ? (int?) yourInt : (int?) null;
               }
           )
           ) (yourCol) AS y
    FROM @input
    ) AS x
    WHERE y IS NOT NULL;

OUTPUT @output
TO "/output/output.csv"
USING Outputters.Csv();

My results:

My results

Upvotes: 0

DraganB
DraganB

Reputation: 1138

SUM fucntion works with number type, so try something like this SUM(ToInt64(VALUE))

Upvotes: 0

Related Questions