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_member275696]

Hi Guys,

Here is the description of the function sql() from DS reference guide.

sql()

Runs a SQL operation against tables in the specified database.

Syntax

sql(datastore, sql_command)

Return value

varchar(1020)

Returns the first 1020 characters from the query's output. Typically, if sql_command is a SELECT

statement, the return value is the first row value of the first column. If sql_command is not a SELECT

statement, the return value is typically NULL. You must remember this if you assign the value returned to a variable.

[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:

[Former Member]

Hi Narayanan,

I doubt it can be done in single print statement. If you need to bring in all the rows for processing in DS, then it's best to use the table itself or SQL transform if you need to use the result set of a query.

May be other people in the forum can suggest options.

Thanks, Arun.