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, […]
Year: 2018
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 […]
IBM i talks to WATSON
We can invoke Watson API from IBM i using DB2’s SQL function Httpgetclob. Httpgetclob retrieves a text-based resource from the specified URL through an Http Get Request.Httpgetclob returns the resource as CLOB(5M) data. Example : Calling WATSON language translate Api to convert english to french using httpgetclob. Select Char(Systools.httpgetclob(‘https://watson-api-explorer.mybluemix.net/language-translator/api/v2/translate?model_id=en-fr&text=good+night’, ‘ ‘), 256) From Sysibm/Sysdummy1 Result […]