SQL output in Print function

[narayanan_kuppuraj]

Hi Experts,

I have a table with one columne with few rows. I want to print all the rows in BODS using print function. I used as below , but its printing only the first row from the the oracle table and does not print the rest of the rows. What settings i need to change in order to print all the rows.

print(sql('DSSTAGING',  'select col from test_error '));

Appreiate your help..

Thanks,

narayanan

[Former Member]

Hi Narayanan,

You won't be able to use one print function to list multiple rows, but there is a way to work around this problem that will help you achieve what you want. The following method or similar may not be practical if you have many rows .

If you have one column with 5  rows containg random data for example you can use TOP is sql in multiple print within a script, for example:

$GlobalVariable0=Print (sql('DSSTAGING', 'SELECT TOP 1 Col FROM test_error ORDER BY ID'));

$GlobalVariable0=Print (sql('DSSTAGING', 'SELECT TOP 1 Col FROM test_error WHERE ID NOT IN ( SELECT TOP 1 ID FROM test_error ORDER BY ID )ORDER BY ID'));

$GlobalVariable0=Print (sql('DSSTAGING', 'SELECT TOP 1 Col FROM test_error WHERE ID NOT IN ( SELECT TOP 2 ID FROM test_error ORDER BY ID )ORDER BY ID'));

$GlobalVariable0=Print (sql('DSSTAGING', 'SELECT TOP 1 Col FROM test_error WHERE ID NOT IN ( SELECT TOP 3 ID FROM test_error ORDER BY ID )ORDER BY ID'));

$GlobalVariable0=Print (sql('DSSTAGING', 'SELECT TOP 1 Col FROM test_error WHERE ID NOT IN ( SELECT TOP 4 ID FROM test_error ORDER BY ID )ORDER BY ID'));

You'll achieve this result: