how to Handle not in Function
Moderators: chulett, rschirm, roy
how to Handle not in Function
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.
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.
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
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.
There are also a few ways of achieving the same result using BASIC functions.
HTH
J.
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' ) ...
HTH
J.
Last edited by jhmckeever on Wed Jan 03, 2007 6:16 am, edited 1 time in total.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
Perhaps you could post the SQL you are trying to emulate?
Note that ...
... is the same as
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'
Code: Select all
WHERE SRVC_TPY_CD_ID NOT IN ('N','A','Z','I','P')
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
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.arasan wrote:previous
i was using srvc_typ_cd_id<>'A' or srvc_typ_cd_id<>'Z' ......
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers