05-16-2012 09:06 AM
Hi All !
Can anyone please suggest me on operation of WHERE clause format in FILE READER Transformation -> Advanced Options -> WHERE condition.
I have an i/p column == CLIENT_ID
and i have to pass only those CLIENT_ID which are not null.
So, how should i frame it in the WHERE clause Box
eg. tried but didnot work
CLIENT_ID ^= .
CLIENT_ID IS NOT NULL
05-16-2012 01:33 PM
where condition in file reader transformation only works for sas data sets.
So you can use an extract transformation/data validation transformation after file reader transformation to remove missing values.
05-16-2012 02:26 PM
Thanks for the reply.. Actually i had tried with extract initially but the requirement was of usin FILE READER ... So i was finding it tough to explore....Thanks a lot!
06-14-2012 10:50 AM
The WHERE works but you have to define the target table of the transformation as a table, not a view. That's because the WHERE clause ends up in the data statement of the generated data step:
(WHERE = (rectype='01'))
If the target is a view (default!) then this is generated, which throws a syntax error:
|134||data work.W4SBCVT / view = work.W4SBCVT|
|135||(WHERE = (rectype='01'))|
ERROR 22-322: Syntax error, expecting one of the following: ALTER, EXECUTE, PROTECT, PW, READ, SOURCE, SRC, WRITE.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
ERROR 76-322: Syntax error, statement will be ignored.