none
Outputting arrays with a variable number of indexes

    Question

  • How can I output arrays with a variable number of indexes? Can I input nulls when the index does not exist?

    @rows  =

        SELECT *

        FROM

            (VALUES 

                ("a1/b1/c2"),

                ("a2/b2"),

                ("a3/b3/c3"),

                ("a4/b4/c4")

            ) AS  rows( pathstr );

     

    @splitpath =

        SELECT pathstr,

                  new SqlArray<string>(pathstr.Split('/')) AS  PathArray

        FROM @rows;

     

    @pathlevels =

        SELECT

            pathstr,

            PathArray[0] AS PathLevel1,

            PathArray[1] AS PathLevel2,

            PathArray[2] AS PathLevel3

        FROM @splitpath ;

     

    OUTPUT @pathlevels

    TO "/PathLevels.csv"

    USING Outputters.Csv();

    Inner exception from user expression: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index Current row dump: LevelsPath: "ATT/Illinois/Q12850/OaHumidity" TimeStamp: "2015-09-01 22:15:00" EquipementName: "" GeoLoc: "Q12850" SensorType: "" Floor: "OaHumidity" Value: "75.900000000000006"


    Garrett Edmondson

    Saturday, March 4, 2017 7:11 PM

Answers

  • One technique is the use of a helper function (placed in a separate .NET assembly - or using the "code behind" feature of ADL Tools for Visual studio)

    using Microsoft.Analytics.Interfaces;
    using Microsoft.Analytics.Types.Sql;

    namespace Demo
    {

        public static class Helpers
        {
            public static string GetValueAtIndexOrNull(SqlArray<string> array, int index)
            {
                if (index < array.Count)
                {
                    return array[index];
                }
                else
                {
                    return null;
                }
            }
        }
    }

    And then use it in the script like this:

    @rows  = 
        SELECT * 
        FROM
            (VALUES  
                ("a1/b1/c2"),
                ("a2/b2"),
                ("a3/b3/c3"),
                ("a4/b4/c4")
            ) AS  rows( pathstr );
     
    @splitpath =
        SELECT pathstr,
                  new SqlArray<string>(pathstr.Split('/')) AS  PathArray
        FROM @rows;
     
    @pathlevels =
        SELECT 
            pathstr,
            Demo.Helpers.GetValueAtIndexOrNull(PathArray,0) AS PathLevel1,
            Demo.Helpers.GetValueAtIndexOrNull(PathArray,1) AS PathLevel2,
            Demo.Helpers.GetValueAtIndexOrNull(PathArray,2) AS PathLevel3
        FROM @splitpath ;
     
    OUTPUT @pathlevels
    TO "/PathLevels.csv" 
    USING Outputters.Csv();

    • Marked as answer by Garrett E Saturday, March 4, 2017 11:15 PM
    Saturday, March 4, 2017 7:46 PM
    Moderator

All replies

  • One technique is the use of a helper function (placed in a separate .NET assembly - or using the "code behind" feature of ADL Tools for Visual studio)

    using Microsoft.Analytics.Interfaces;
    using Microsoft.Analytics.Types.Sql;

    namespace Demo
    {

        public static class Helpers
        {
            public static string GetValueAtIndexOrNull(SqlArray<string> array, int index)
            {
                if (index < array.Count)
                {
                    return array[index];
                }
                else
                {
                    return null;
                }
            }
        }
    }

    And then use it in the script like this:

    @rows  = 
        SELECT * 
        FROM
            (VALUES  
                ("a1/b1/c2"),
                ("a2/b2"),
                ("a3/b3/c3"),
                ("a4/b4/c4")
            ) AS  rows( pathstr );
     
    @splitpath =
        SELECT pathstr,
                  new SqlArray<string>(pathstr.Split('/')) AS  PathArray
        FROM @rows;
     
    @pathlevels =
        SELECT 
            pathstr,
            Demo.Helpers.GetValueAtIndexOrNull(PathArray,0) AS PathLevel1,
            Demo.Helpers.GetValueAtIndexOrNull(PathArray,1) AS PathLevel2,
            Demo.Helpers.GetValueAtIndexOrNull(PathArray,2) AS PathLevel3
        FROM @splitpath ;
     
    OUTPUT @pathlevels
    TO "/PathLevels.csv" 
    USING Outputters.Csv();

    • Marked as answer by Garrett E Saturday, March 4, 2017 11:15 PM
    Saturday, March 4, 2017 7:46 PM
    Moderator
  • Saveen, provides a great answer!  The following provides a simple one-line change that will also work based on the provided dataset.  Just another option..........

    @pathlevels =
        SELECT
            pathstr,
            PathArray[0] AS PathLevel1,
            PathArray[1] AS PathLevel2,
            PathArray.Count > 2 ? PathArray[2] : null AS PathLevel3
        FROM @splitpath ;

    Monday, March 6, 2017 6:06 PM