how to Handle not in Function

arasan · Post by **arasan** » Wed Jan 03, 2007 5:34 am

I am using NOT IN function but the result is varing when i implement the funtion through datastage where us when i try to do in sql server using NOT in function i am getting the correct result.

can any one suggest me where i went wrong

If (SRVC_TPY_CD_ID<>'N' or SRVC_TPY_CD_ID<>'A' orSRVC_TPY_CD_ID<>'Z' or SRVC_TPY_CD_ID<>'I' 0r SRVC_TPY_CD_ID<>'P' ) and len(trim(SAI_NBR_ID)>0 then NON_JOB_HRS else 0

if i try to incorporate the above derivation the result is varying what is got in sqlserver and in datastage.

jhmckeever · Post by **jhmckeever** » Wed Jan 03, 2007 5:45 am

arasan,

If I'm following your post correctly you're trying to emulate a SQL 'NOT IN' clause using a succession of OR'd clauses. To get the logical equivalent of a NOT IN using this method you must AND your clauses together.

Code: Select all

If (
   SRVC_TPY_CD_ID<>'N' AND SRVC_TPY_CD_ID<>'A' AND
   SRVC_TPY_CD_ID<>'Z' AND SRVC_TPY_CD_ID<>'I' AND
   SRVC_TPY_CD_ID<>'P' )  ...

There are also a few ways of achieving the same result using BASIC functions.

HTH

J.

arasan · Post by **arasan** » Wed Jan 03, 2007 6:13 am

Thanks for the reply but i tried with AND function also i am not getting the exact result is there any other function in datastage to do the NOT IN function

jhmckeever · Post by **jhmckeever** » Wed Jan 03, 2007 6:17 am

Perhaps you could post the SQL you are trying to emulate?

Note that ...

Code: Select all

SRVC_TPY_CD_ID<>'N' AND SRVC_TPY_CD_ID<>'A' AND 
SRVC_TPY_CD_ID<>'Z' AND SRVC_TPY_CD_ID<>'I' AND 
SRVC_TPY_CD_ID<>'P'

... is the same as

Code: Select all

WHERE SRVC_TPY_CD_ID NOT IN ('N','A','Z','I','P')

arasan · Post by **arasan** » Wed Jan 03, 2007 6:33 am

yes,as you have mentioned i used the same in the SQL.

when i fire the query it is resulting the result as 5.8 in the sql.same thing through datastage it is resulting sa 20.6

Edwink · Post by **Edwink** » Wed Jan 03, 2007 7:44 am

Arasa,
try to use count function
count(",N,A,I,P,Z,","SRVC_TPY_CD_ID")<1
If there are matches the output will be zero - which should solve your problem.

DSguru2B · Post by **DSguru2B** » Wed Jan 03, 2007 7:59 am

Try by trimming the fields first before doing your inequality check. There might be a trailing space or two. Make those columns varchar.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 03, 2007 3:55 pm

Here's how I would do it. The Index() function is very efficient. I am assuming that SRVC_TPY_CD_ID is Char(1) data type.

Code: Select all

If Index("NAPIZ",InLink.SRVC_TPY_CD_ID,1) = 0 And Trim(InLink.SAI_NBR_ID) > " " Then NON_JOB_HRS Else 0

arasan · Post by **arasan** » Thu Jan 04, 2007 3:05 am

Thanks for all your replies ,i spoke to the clinent and went for the equal to function then the result was accurate.

previous

i was using srvc_typ_cd_id<>'A' or srvc_typ_cd_id<>'Z' ......

existing

except (AZNIP) remaining srvc_typ_cd_id i took and implemented the equal to function in thje derivation.

chulett · Post by **chulett** » Thu Jan 04, 2007 7:32 am

arasan wrote:previous

i was using srvc_typ_cd_id<>'A' or srvc_typ_cd_id<>'Z' ......

This would have worked more better with an 'AND' between them, not an 'OR'. This expression gets you everything as not only are all other letters not equal to 'A' or 'Z' but 'A' is not equal to 'Z' (so it gets counted) and vice versa.