Symantec IGA

Hi all,

We have IM 12.6 SP6.

We try to use SQL Query to obtain data from SQL database in PXP policy.

Query looks like

"select field1,field2,...,fieldN from <dbname> where <some filter>".

As a result we expect something like "value1,value2,...,valueN", but get only "value1".

Is it the expected behavior?

On the other hand, how can we get all required values in one SQL query ?

Best regards,

Andrej

Hi Andrej,

You need to concatenate the multi columns in one field as following (tested with MSSQL):

"select field1 + ',' + field2 + ',' + ... + ',' + fieldN from <dbname> where <some filter>".

e.g.:

SELECT [loginid] + ',' + [lastname] + ',' + [firstname] + ',' + CAST ([org] as nvarchar) + CAST ([startdate] as nvarchar)

  FROM [Issues].[dbo].[tblUsers]  WHERE [id] = '1';

With this sample [org] is INT and [startdate] is DATETIME, so they needed to be "casted" to make this concatenation possible.

You need to adapt if the SQL engine is not MS SQL server.

Regards,

Philippe.

Hi Philippe,

thank you for response. Great solution. Moreover, I suggest to format SELECT result in JSON format for easy further processing.

Regards,

Andrej