Get rid of entire records if found xxxxxxxx

dataservices job design
1

The table has 4 columns,
Col A, Col B, Col C, Col D and the data is like this.

12345,0123480001,999999999,19920101

I want to get rid of entire record from the table, where/if the Col C = 999999999.

How can i implement the “ifthenelse” function.

msr4 (BOB member since 2011-03-08)

2

Hi,

Will this Value ‘99999999’ appear in only column C or can it appear in any column.

Basically questioning, If you want to Ignore the entire record, IF Columns C has 99999999 or do you want to ignore the record if it has 999999999 irrespective of in which column it appears?

Thanks,
Tarun

Tarunvig :us: (BOB member since 2011-01-18)

3

Will this Value ‘99999999’ appear in only column C or can it appear in any column

Ans: It will only occur in Col C.

msr4 (BOB member since 2011-03-08)

4

As simple as that.
Why would you want to go for an IFTHENELSE

Just add COL C != ‘999999999’ in the WHERE Clause.

ganeshxp :us: (BOB member since 2008-07-17)

5

Yes,

Just add that condition in the where Clause.

Tarunvig :us: (BOB member since 2011-01-18)

6

I didn’t quite get you.

Can you please explain to me as to how can i use this WHERE clause?

msr4 (BOB member since 2011-03-08)

7

5th Tab in any Query Transformation is a Where Clause.

ganeshxp :us: (BOB member since 2008-07-17)

8

:?:

Open your Query Transformation and in the WHERE Clause type

ColumnC != ‘999999999’

Tarunvig :us: (BOB member since 2011-01-18)

9

In the mapping function, i have
TABLE_NAME.COLUMN_NAME

and

I added this in the WHERE clause.
TABLE_NAME.COLUMN_NAME ! = ‘999999999’

I am getting this error;
[Query:IVR_Query]
Invalid WHERE clause. Additional information: <Syntax error at line <1>: <>: near <!> found <’!’>>.
1 error(s), 0 warning(s).
. Please check and fix the syntax and retry the operation. (BODI-1112394)>. (BODI-1111078)

msr4 (BOB member since 2011-03-08)

10

Try This

NOT (TABLE_NAME.COLUMN_NAME in ‘9999999999’)

Tarunvig :us: (BOB member since 2011-01-18)

11

take the space off in between ! and =
col_name != ‘9999999999’

or
use
col_name <> ‘9999999999’

DSKSR (BOB member since 2011-02-04)

12

:frowning:

[Query:IVR_Query]
Invalid WHERE clause. Additional information: <Syntax error at line <1>: <>: near <’> found expecting <’(’, identifier, a quoted identifier>.
1 error(s), 0 warning(s).
. Please check and fix the syntax and retry the operation. (BODI-1112394)>. (BODI-1111078)

msr4 (BOB member since 2011-03-08)
13

This one worked.
col_name != ‘9999999999’

:+1:
Thanks guyz.
Appreciate your help.

msr4 (BOB member since 2011-03-08)