Month: July 2018

Use LISTAGG to turn rows of data into a comma separated list

  The LISTAGG function aggregates a set of string elements into one string by concatenating the strings.   Example : Select dept, LISTAGG(Lastname, ‘, ‘)  Within  Group(Order by Lastname) As Employees From Emptable Group By Dept Sample Result :   Dept              Employees Admin            Adam, […]

Get Google lat/long via SQL from IBM i

In this blog, we are going to see how to call Google maps API and parse the returned XML document via SQ using httpgetclob , Xmltable, Xmlparse functions Sample SQL : Select * From Xmltable(‘$d/GeocodeResponse/result/geometry/location’ passing Xmlparse(Document systools.httpgetclob(‘http://maps.googleapis/maps/api/geocode/xml?address=East+Evergreen+DR%2C+Palatine%2C+IL’, ”)) as “d” Columns Latitude Varchar(128) path ‘lat’, Longitude Varchar(128) path ‘lng’) as Webserviceresult ; Xmltable function […]