Adapt MS SQL script to TDV SQL

[Tamir Basin]

Hi

For the full details of the question and sample code to generate the data please see this link:

https://stackoverflow.com/questions/70812718/how-to-return-an-array-in-a...

The following code

works in MS SQL and we need to use it (or get the same result) in TDV 8.3:

select

o.FirstName,

o.ID,

json_query((

select i.CarLicense, i.CarType, i.CarSystem as SourceSystem

from dbo.cars as i

where i.ID = o.ID

for json path

), case count(*) when 1 then '$[0]' else '$' end) as cars

from dbo.cars o

group by

o.firstname,

o.id

order by

o.firstname

for json path;

[Daniel OConnor1705579774]

One method you could use is to simply pass the SQL code directly to MSSQL using a Packaged Query. There are 2 tutorials listed here:

https://www.youtube.com/playlistlist=PLnmoGGHHJldi2P0wbKPi4dYhl--lNZquA

TDV also has it's own json functions listed here

https://docs.tibco.com/pub/tdv/8.5.1/doc/html/StudioHelp/index.html#page...