cancel
Showing results for 
Search instead for 
Did you mean: 

sql() function help

Former Member
0 Kudos

I'm trying to implement a sql function in a while loop and I am running into some errors.  No matter what combonation I try Data services doesn't like the syntax.

Please see below for an example of what I'm using:

sql('TABLENAME','select count(*) from TABLENAME where TABLENAME.status = \'U\'')

I always get a "Found erroneous expressions"  error.  I've check spelling etc... and it seems to be ok, am I missing something with syntax?  Doesn anyone have any hints for working with while loops or using the sql() function?

edit: 

got this to work

sql('SAP_TST', 'select count(*) from TABLENAME where STATUS = \'P\'')

Thanks!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Shaun,

Instead of table name try using datastore name in the beginning.

sql('datastorename','...............');

Thanks,

Arun

Former Member
0 Kudos

thanks for the suggestion, unfortunately I'm still getting this error.  Could it be the count(*) not being allowed in the SQL() function?

error message:

VAL-030159 3/26/2012 2:26:59 PM Found erroneous expression <sql('SAP_TST','select count(*) from TABLE where TABLE.STATUS = \'U\'')>. Check its syntax and fix

Former Member
0 Kudos

Count can be used in the SQL, no problem with that. Have you used semi colon (;) after SQL syntax? Whats the desired output?

Former Member
0 Kudos

I'm not using a semi-colon anywhere, the formatting is the same as what i typed above except the table name is different.

What I'm trying to do is execute a while loop until the record count = 0.  So I'm trying to include the sql statement in my while loop condition so that I can check the record count of a table.

So something like

while( sql( select count(*) from table)) != 0 )

     Perform data flow tasks

Former Member
0 Kudos

I think you need number of values in the specified column called 'STATUS'. In that case your SQL function should be select count(STATUS) as xyz from tablename where  STATUS = \'U\'

Is that what you are looking at?

Former Member
0 Kudos

Ok. To check the record count of the table  the sql syntax should be select count(*) from tablename.

sql ('datastorename','select count(*) from tablename').

Arun

Former Member
0 Kudos

Just FYI.

One more thing. If sql doesn't work, you can use total_rows() function to get the count of rows in the table.

Answers (0)