Year: 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, […]

Read More

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 […]

Read More

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 […]

Read More

Sql TRANSLATE function

The Sql TRANSLATE() function replaces a sequence of characters in a string with another sequence of characters. The function replaces a single character at a time. Example: Select TRANSLATE(‘This@is@an@example.’,  ‘ ‘,  ‘@’ ) From Sysibm/Sysdummy1 Result:    This is an example.  

Read More

Sql Varchar_Format Function

The VARCHAR_FORMAT function returns a string representation of a timestamp that has been formatted according to a specified character template Example : Select Varchar_Format(Current_Date, ‘YYYYMM’) From Sysibm/Sysdummy1 This query returns 201806

Read More