SQL script issue

[Former Member]

hello experts,

I am new to BOIS and wrote below statement in the rule:

$aa = SQL('connection', 'SELECT count(*) FROM table 1 where field1 = \'$parameter0\''' );      $parameter0 is varchar

if ($aa > 0 )

    return true;

else

    return false;

....

The issue is if I use a constant instead of  the parameter like (where field1 = \'1000\') the result is correct.

But if I use parameter like above, the result is always false.

Could you please let me know where is my fault.  thank you in advance.

[denise_meyer · Accepted solution]

Hi Gavin,

So I believe you are running into the issue where the information steward SQL() function does not support the use of a $parameter within it - please see the below KBA:

1726964 - Error with rule using $parameter in SQL() function - Information Steward Data Insight

Cause

Information Steward's SQL() function does not support the use of a parameter in the select statement in a Rule's expression at this time.

Resolution

It does accept declared variables from the expression syntax (e.g. in syntax below, to be referenced and therefore allows the following to work with using the replace_substr() function:

DECLARE
$temp_char varchar(10);
$selectString varchar(240);
BEGIN
$selectString = replace_substr('select count(*) from TABLE1 where id = \'XXX\'' ,'XXX', $Id);
$temp_char = SQL('connection_name', $selectString);
RETURN $temp_char = '0';
END

(e.g. Where TABLE1 is replaced with the desired table name and connection_name is replaced with the correct connection from CMC.  Also, replace the syntax in the SQL statement to perform the needed function. $Id is an input parameter and is acceptable to be used in functions other than SQL().)

Thanks,

Denise

[Former Member · Accepted solution]

hello Denise,

According to your guidance, my code now is working. Thank you so much!

[Former Member · Accepted solution]

Hi ,

I have created a job in Data Services for the data which fails the requirement of the users as it was difficult to prepare it in information steward and then I have extracted that output table in Information Steward as they wanted to see this output in this tool.

Can You please help me know if it is possible to set a trend for the count of the data

For example ,If today the output has 1000 data and tomorrow it is 5000  ,is it possible to get a trend for this.

I have written a rule as below but I am getting the trend as either '0.00' score or '10.00' and the failed and passed data are either equal or failed is '0'

declare

$aa int;

BEGIN

$aa = SQL('conn_name', 'SELECT count(*)  FROM  table_name' );

if ($aa = 1000)

    RETURN TRUE;

ELSE

    RETURN FALSE;

END

Please let me know the changes to make in this rule to get a correct trend if the count is varying.

Any help to understand this would be appreciated .

Thanks

Nitika