NOT IN in datastage

qutesanju · Post by **qutesanju** » Fri Dec 21, 2012 11:01 am

there is a SQL query

select * from table1
where DIR ='IN'
AND CD_FLAG ='D'
AND CLASS NOT IN ( 'ARC','PPD','CCD','CTX','COR','DNE')

so i'm trying to implement above query in datastage
and i implemented it in transformer as

lnk_xfmr_target.DIR ='IN' AND lnk_xfmr_target.CD_FLAG ='D'
AND (lnk_xfmr_target.CLASS <> 'ARC'
and lnk_xfmr_target.CLASS <> 'PPD'
and lnk_xfmr_target.CLASS <> 'CCD'
and lnk_xfmr_target.CLASS <> 'CTX'
and lnk_xfmr_target.CLASS <> 'COR'
and lnk_xfmr_target.CLASS <> 'DNE')

i know we can implement NOT IN in datastage using <>
but seeking experts advise to check did i implemeted it correctly?

jwiles · Post by **jwiles** » Fri Dec 21, 2012 11:54 am

The logic looks good. An alternative, which may be more efficient especially if the list of codes grows, would be

Code: Select all

lnk_xfmr_target.DIR ='IN' AND lnk_xfmr_target.CD_FLAG ='D' 
AND Index('|ARC|PPD|CCD|CTX|COR|DNE|','|':lnk_xfmr_target.CLASS:'|') < 1

Regards,

qutesanju · Post by **qutesanju** » Fri Dec 21, 2012 12:14 pm

perfect james

i m thinking which one is correct in below two

1)
lnk_xfmr_target.DIR ='IN' AND lnk_xfmr_target.CD_FLAG ='D'
AND (lnk_xfmr_target.CLASS <> 'ARC'
and lnk_xfmr_target.CLASS <> 'PPD'
and lnk_xfmr_target.CLASS <> 'CCD'
and lnk_xfmr_target.CLASS <> 'CTX'
and lnk_xfmr_target.CLASS <> 'COR'
and lnk_xfmr_target.CLASS <> 'DNE')

2)

lnk_xfmr_target.DIR ='IN' AND lnk_xfmr_target.CD_FLAG ='D'
AND (lnk_xfmr_target.CLASS <> 'ARC'
OR lnk_xfmr_target.CLASS <> 'PPD'
OR lnk_xfmr_target.CLASS <> 'CCD'
OR lnk_xfmr_target.CLASS <> 'CTX'
OR lnk_xfmr_target.CLASS <> 'COR'
OR lnk_xfmr_target.CLASS <> 'DNE')

And i believe my option 1 corrosponds your code

lnk_xfmr_target.DIR ='IN' AND lnk_xfmr_target.CD_FLAG ='D'
AND Index('|ARC|PPD|CCD|CTX|COR|DNE|','|':lnk_xfmr_target.CLASS:'|') < 1

so what is the equivalent condition of option2 with your code?

chulett · Post by **chulett** » Fri Dec 21, 2012 12:32 pm

#1 is valid, #2 is not as the 'OR's make it always evaluate to TRUE.

jwiles · Post by **jwiles** » Fri Dec 21, 2012 3:53 pm

As Craig states, #2 is incorrect. You are guaranteed that at least one of your conditions within the parenthesis will always be true. You might as well have "AND 1=1" instead!

The equivalent Index function would be

Code: Select all

AND Index('|ARC|PPD|CCD|CTX|COR|DNE|','|':lnk_xfmr_target.CLASS:'|') >= 0

which is always true.

Regards,

vamsi.4a6 · Post by **vamsi.4a6** » Sat Dec 22, 2012 10:20 am

jwiles wrote:The logic looks good. An alternative, which may be more efficient especially if the list of codes grows, would be
Code: Select all
lnk_xfmr_target.DIR ='IN' AND lnk_xfmr_target.CD_FLAG ='D' 
AND Index('|ARC|PPD|CCD|CTX|COR|DNE|','|':lnk_xfmr_target.CLASS:'|') < 1

could anybody explain what is the need for | and concatenation.I think following will also work.please correct me if i am wrong?

lnk_xfmr_target.DIR ='IN' AND lnk_xfmr_target.CD_FLAG ='D'
AND Index('ARCPPDCCDCTXCORDNE',lnk_xfmr_target.CLASS) < 1

chulett · Post by **chulett** » Sat Dec 22, 2012 11:17 am

It prevents "false positives" on a substring match. For example, yours would return true for "CPP" which is not in the valid set.

qutesanju · Post by **qutesanju** » Mon Dec 24, 2012 1:57 pm

I tried alternative approach which is

lnk_xfmr_target.DIR ='IN' AND lnk_xfmr_target.CD_FLAG ='D'
AND Index('|ARC|PPD|CCD|CTX|COR|DNE|','|':lnk_xfmr_target.CLASS:'|') < 1

but it shows
not enough arguments to function index

ray.wurlod · Post by **ray.wurlod** » Mon Dec 24, 2012 2:11 pm

Index() needs three arguments - if it's any consolation, James missed this also. The third argument is the occurrence number you're searching for - this is almost always 1. But the function does, for example, allow you to look for the nth occurrence of the search substring.

qutesanju · Post by **qutesanju** » Mon Dec 24, 2012 2:20 pm

so can someone shows correct function with arguments?

jwiles · Post by **jwiles** » Mon Dec 24, 2012 2:39 pm

My mistake...typing it in from memory

Index('|ARC|PPD|CCD|CTX|COR|DNE|','|':lnk_xfmr_target.CLASS:'|',1)

The ,1 is the third argument (missing from my original). You can also just click the [...] button in the derivation editor and search for Index in the String functions. Selecting it will provide you the correct template for the function. Also documentation is available through the Help button and online docs.

Regards,

harishkumar.upadrasta · Tue Dec 25, 2012 11:27 pm

Hi,

I think there needs a small correction as the string in which we need to search should be the first argument, please correct me if i'm wrong.

Earlier posted:

Index('|ARC|PPD|CCD|CTX|COR|DNE|','|':lnk_xfmr_target.CLASS:'|' ,1 )

Corrected:

Index('|':lnk_xfmr_target.CLASS:'|','|ARC|PPD|CCD|CTX|COR|DNE|' ,1 )

Example:

MyString = "P1234XXOO1299XX00P1"
Position = Index(MyString, 1, 2)
* The above returns the index of the second "1" character (10).
Position = Index(MyString, "XX", 2)
* The above returns the start index of the second "XX"
* substring (14).
Position = Index(MyString, "xx", 2)
* The above returns 0 since the substring "xx" does not occur.
Position = Index(MyString, "XX", 3)
* The above returns 0 since the third occurrence of
* substring "XX" * cannot be found.

chulett · Post by **chulett** » Wed Dec 26, 2012 6:58 am

harishkumar.upadrasta wrote:Corrected:

Index('|':lnk_xfmr_target.CLASS:'|','|ARC|PPD|CCD|CTX|COR|DNE|' ,1 )

Your correction is... correct! From the docs:

Input: string (string) substring (string) occurrence (int32)

harishkumar.upadrasta · Wed Dec 26, 2012 10:40 am

Thank u craig

jwiles · Post by **jwiles** » Fri Dec 28, 2012 1:10 pm

Yes, thank you indeed!

Regards,