x > y join

[admin]

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users








Misters,
when =
one do a join=20
between a mainstream and a lookup, it is always an equal join. How can =
one make=20
an unequal ( greater ou less)  join?
Thk u=20
all,
Alex

[admin]

This is a multi-part message in MIME format.

------=_NextPart_000_0000_01C3E58F.32E10E80
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_0001_01C3E58F.32E8AFA0"


------=_NextPart_001_0001_01C3E58F.32E8AFA0
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Well, for the first approach, use ORAOCI stage as lookup, for instance. =
When you use user defined sql, you must be aware of the the position of =
the input parameters of lookup, In the ORAOCI stage output, only the =
columns which have got the the key attribute set to "Yes" are the =
parameters you may use in the user defined sql statment.

If you see "...generated sql shows "=3D " ", it means you have no key =
in your lookup. Then, if there is no key in the lookup, how can you join =
with your driven stage table ?

xiong
-----Original Message-----
From: Alex Martins [mailto:datastage-users-bounces@oliver.com]On =
Behalf Of Alex Martins
Sent: 2004=E5=B9=B41=E6=9C=8828=E6=97=A5 7:12
To: DataStage Users Discussion List
Subject: RES: x > y join


Xiong,
I used to do this (first solution) in version 4.1. I=C2=B4m using =
version 6.0.0.17 now, and the generated sql shows "=3D ". I tried " > =
" and "> :1", but it didn=C2=B4t work.
About the second solution, how/where do you asign value to =
MaxVal_of_lookup?
Thank you very much,
Alex
-----Mensagem original-----=20
De: Xiong Li [mailto:xj.liang@paradise.net.nz]=20
Enviada: seg 26/01/2004 20:10=20
Para: DataStage Users Discussion List=20
Cc:=20
Assunto: RE: x > y join


If the lookup stage is connected to database directly such as ODBC =
stage or ORAOCI stage. This problem is simple. just use user defined sql =
in lookup stage like :
=20
where .....
and lookup_col > :1
=20
there is another way I used before. Simply find out the Maximum =
value of the column in the original lookup database table. Then do the =
same thing as mentioned above in the mainstream user defined generated =
sql stagement like :=20

where ......
and main_col y join


Misters,
when one do a join between a mainstream and a lookup, it is always =
an equal join. How can one make an unequal ( greater ou less) join?
Thk u all,
Alex

------=_NextPart_001_0001_01C3E58F.32E8AFA0
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable






Well, for=20
the first approach, use ORAOCI stage as lookup, for instance. When =
you use=20
user defined sql, you must be aware of the the position of the input =
parameters=20
of lookup, In the ORAOCI stage output, only the columns which =
have got=20
the the key attribute set to "Yes" are the parameters you may use in the =
user=20
defined sql statment.
 
If=20
you see "...generated sql shows "=3D " ", it means you have no =
key in your=20
lookup. Then, if there is no key in the lookup, how can you join with =
your=20
driven stage table ?
 
xiong

-----Original Message-----From: Alex Martins=20
[mailto:datastage-users-bounces@oliver.com]On Behalf Of Alex=20
MartinsSent: 2004=E5=B9=B41=E6=9C=8828=E6=97=A5 =
7:12To: DataStage Users=20
Discussion ListSubject: RES: x > y =
join
Xiong,
I used to do this (first solution) in version 4.1. I=C2=B4m =
using version=20
6.0.0.17 now, and the generated sql shows "=3D ". I tried " > " =
and ">=20
:1", but it didn=C2=B4t work.
About the second solution, how/where do you asign value to MaxVal_of_lookup?
Thank you very much,
Alex

-----Mensagem original----- De: Xiong =
Li=20
[mailto:xj.liang@paradise.net.nz] Enviada: seg 26/01/2004 =
20:10=20
Para: DataStage Users Discussion List Cc:=20
Assunto: RE: x > y join
If=20
the lookup stage is connected to database directly such as ODBC =
stage or=20
ORAOCI stage. This problem is simple. just use user defined sql in =
lookup=20
stage like :
 
where .....
and=20
lookup_col > :1
 
there=20
is another way I used before. Simply find out the Maximum value =
of the=20
column in the original lookup database table. Then do the same =
thing as=20
mentioned above in the mainstream user defined generated =
sql=20
stagement like :
 
where=20
......
and=20
main_col&nbsp;< MaxVal_of_lookup
&nbsp;
And=20
you don't need the lookup stage any more.
&nbsp;
Hope=20
it would help
&nbsp;
xiong
&nbsp;
&nbsp;

-----Original Message-----From:=20
datastage-users-bounces@oliver.com=20
[mailto:datastage-users-bounces@oliver.com]On Behalf Of =
Alex=20
MartinsSent: 2004=E5=B9=B41=E6=9C=8827=E6=97=A5 =
6:23To: Datastage-Users=20
(E-mail)Subject: x > y join
Misters,
when one do a=20
join between a mainstream and a lookup, it is always an equal =
join. How=20
can one make an unequal ( greater ou less)&nbsp; =
join?
Thk u=20
all,
Alex

------=_NextPart_001_0001_01C3E58F.32E8AFA0--

------=_NextPart_000_0000_01C3E58F.32E10E80
Content-Type: application/ms-tnef;
name="winmail.dat"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="winmail.dat"

eJ8+Ih0WAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAAqAMAAAAAAACrAAEIgAcAGAAAAElQTS5NaWNy
b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEGgAMADgAAANQHAQAcAAsACQAAAAMADwEB
A5AGADgEAAAhAAAACwACAAEAAAALACMAAAAAAAMAJgAAAAAACwApAAAAAAADAC4AAAAAAAMANgAA
AAAAHgBwAAEAAAALAAAAeCA+IHkgam9pbgAAAgFxAAEAAAAgAAAAAcPkWBWc/zy4UrftS4W1adPd
sOgjdgAp11crAAUjDiACAR0MAQAAAB4AAABTTVRQOlhKLkxJQU5HQFBBUkFESVNFLk5FVC5OWgAA
AAsAAQ4AAAAAQAAGDgDmbSoi5cMBAgEKDgEAAAAYAAAAAAAAAOxARVrn08MRlb8a5YsY/pnCgAAA
HgBCEAEAAAA6AAAAPDU3QjJCNDkwRUYzN0Y2NEE5MUNENzE4RTg5Mzg1OUNGNDM1NUQ2QHd3dy5w
cm9pdC5jb20uYnI+AAAACwABgAggBgAAAAAAwAAAAAAAAEYAAAAAA4UAAAAAAAADAAOACCAGAAAA
AADAAAAAAAAARgAAAAAQhQAAAAAAAAMAB4AIIAYAAAAAAMAAAAAAAABGAAAAAFKFAAAnagEAHgAJ
gAggBgAAAAAAwAAAAAAAAEYAAAAAVIUAAAEAAAAEAAAAOS4wAB4ACoAIIAYAAAAAAMAAAAAAAABG
AAAAADaFAAABAAAAAQAAAAAAAAAeAAuACCAGAAAAAADAAAAAAAAARgAAAAA3hQAAAQAAAAEAAAAA
AAAAHgAMgAggBgAAAAAAwAAAAAAAAEYAAAAAOIUAAAEAAAABAAAAAAAAAAsADYAIIAYAAAAAAMAA
AAAAAABGAAAAAIKFAAABAAAAHgAOgAggBgAAAAAAwAAAAAAAAEYAAAAAg4UAAAEAAAATAAAANjIw
MzQyNzIwLTI3MDEyMDA0AAALADqACCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMAPIAIIAYA
AAAAAMAAAAAAAABGAAAAABGFAAAAAAAAAwA9gAggBgAAAAAAwAAAAAAAAEYAAAAAGIUAAAAAAAAL
AFWACCAGAAAAAADAAAAAAAAARgAAAAAGhQAAAAAAAAMAVoAIIAYAAAAAAMAAAAAAAABGAAAAAAGF
AAAAAAAAAgH4DwEAAAAQAAAA7EBFWufTwxGVvxrlixj+mQIB+g8BAAAAEAAAAOxARVrn08MRlb8a
5YsY/pkCAfsPAQAAAIIAAAAAAAAAOKG7EAXlEBqhuwgAKypWwgAAUFNUUFJYLkRMTAAAAAAAAAAA
TklUQfm/uAEAqgA32W4AAABDOlxXSU5ET1dTXExvY2FsIFNldHRpbmdzXEFwcGxpY2F0aW9uIERh
dGFcTWljcm9zb2Z0XE91dGxvb2tcb3V0bG9vay5wc3QAAAADAP4PBQAAAAMADTT9NwAAAgF/AAEA
AAA4AAAAPE1EQkJKRkxQUEdLRk1PRUNCSkVLT0VESkNBQUEueGoubGlhbmdAcGFyYWRpc2UubmV0
Lm56PgDVtw==

------=_NextPart_000_0000_01C3E58F.32E10E80
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

------=_NextPart_000_0000_01C3E58F.32E10E80--