CODEWITHSUNDEEP
azureu-sql
martin
martin

Reputation: 1

Convert decimal to string for comparison in U-SQL

I am trying to convert data from a loaded in CSV file to an Azure table, but I'm running into problems where one of the values is loading in a string and the equivalent value in the table is decimal.

@ProdOutput = 
    EXTRACT 
    [p_description] string, 
    [p_code] string, 
    [p_price] string, 
    [p_colour] string
FROM "testing/martin/items.csv" 
USING Extractors.Csv(skipFirstNRows:1);  


// @items has been brought in from a table in Data Lake Analytics
@holdings_query = 
    SELECT ROW_NUMBER() OVER (ORDER BY h.code) AS rn, 
       h.description AS Description, 
       h.cd AS Code,    
       h.priceGBP  AS Price,    
       h.col AS Colour
FROM   @items AS h;



@result = 
    SELECT 
    p_description == Description ? p_description : String.Concat("Mismatch", p_description, " !! ", Description)  AS [Desc], 
    p_code == Code ? p_description : String.Concat("Mismatch", p_code, " !! ", Code)  AS [Code], 
    p_price == Price ? p_price: String.Concat("Mismatch", p_price, " !! ", Price)  AS [Price]
FROM @holdings_query AS h
LEFT OUTER JOIN @prod_data AS p ON h.Code == p.p_code;

The problem is I want the EXTRACT statement to bring everything in generically as a string, as I'm not always going to know the type of each of the columns. So when I do the comparison in the final statement, I would like to convert the Price from decimal (as it is in the table) to string to compare with what is coming out of the CSV.

I have tried ToString(), ToString("G"), (string?) but they all throw up errors. what am I doing wrong?

TIA Martin

Example data:
Items.csv:
"Item1","IT1","3.65"
"Item2","IT2","4.00"
"Item3","IT3","524.2123"

@items table:
"Item1", "IT1", 3.65
"Item2", "IT2", 4.00
"Item3", "IT3", 524.2123

Upvotes: 0

Views: 1110

Answers (1)

wBob
wBob

Reputation: 14389

Try Decimal.Parse on the string type. This method "...converts the string representation of a number to its Decimal equivalent". I got the following statement to work, where p_price is the string value:

@result =
    SELECT 
            Decimal.Parse(p.p_price) == h.Price ? 1 : 0 AS isEqual
...

Upvotes: 1

Related Questions