Hi all,
This is my first posting to the list. Pardon me if
this has been discussed.
I am loading the target table with one main source
table and few reference lookups.
I am looking for a surrugate key in one of the lookup
table that can give me null values. I am defining my
constraint so that if the ref. surrogate value is null
then reject it. (in1.lkupsuurkey ).
I am getting the error while running the job.
How is this type of situation handled otherwise in
datastage?
TIA
__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1
constraints
Use the ISNULL function, as documented in the Start
Button --> Ardent
DataStage --> Online Manuals --> Universe
Documentation.
You cant use a straight comparison with a NULL value!
You should realize
that SQL has a function dedicated to the NULL value,
which is different
than a BLANK value. DataStage BASIC is exactly the
same.
Your logic should be: ISNULL(column) which returns
TRUE if the column is
NULL, and FALSE otherwise. Use a NOT(ISNULL(column))
if what you want is
the reverse condition. Word of warning....Any
comparison to a column that
is NULL will always be FALSE, even though logic would
tell you otherwise.
You compare to a NULL, you get an unknown, which is
FALSE. If you
concatenate with a NULL, you get a NULL.
Good luck!
-Ken
--- John Thomas wrote:
> Hi all,
>
> This is my first posting to the list. Pardon me if
> this has been discussed.
> I am loading the target table with one main source
> table and few reference lookups.
> I am looking for a surrugate key in one of the
> lookup
> table that can give me null values. I am defining my
> constraint so that if the ref. surrogate value is
> null
> then reject it. (in1.lkupsuurkey ).
> I am getting the error while running the job.
>
> How is this type of situation handled otherwise in
> datastage?
>
> TIA
>
>
> __________________________________________________
> Do You Yahoo!?
> NEW from Yahoo! GeoCities - quick and easy web site
> hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1
__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1
Button --> Ardent
DataStage --> Online Manuals --> Universe
Documentation.
You cant use a straight comparison with a NULL value!
You should realize
that SQL has a function dedicated to the NULL value,
which is different
than a BLANK value. DataStage BASIC is exactly the
same.
Your logic should be: ISNULL(column) which returns
TRUE if the column is
NULL, and FALSE otherwise. Use a NOT(ISNULL(column))
if what you want is
the reverse condition. Word of warning....Any
comparison to a column that
is NULL will always be FALSE, even though logic would
tell you otherwise.
You compare to a NULL, you get an unknown, which is
FALSE. If you
concatenate with a NULL, you get a NULL.
Good luck!
-Ken
--- John Thomas wrote:
> Hi all,
>
> This is my first posting to the list. Pardon me if
> this has been discussed.
> I am loading the target table with one main source
> table and few reference lookups.
> I am looking for a surrugate key in one of the
> lookup
> table that can give me null values. I am defining my
> constraint so that if the ref. surrogate value is
> null
> then reject it. (in1.lkupsuurkey ).
> I am getting the error while running the job.
>
> How is this type of situation handled otherwise in
> datastage?
>
> TIA
>
>
> __________________________________________________
> Do You Yahoo!?
> NEW from Yahoo! GeoCities - quick and easy web site
> hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1
__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1
Without seeing the actual error message it is not possible to diagnose. Certainly your constraint expression appears to be valid. Beware of calling a zero length string "null", though - in other DBMSs the terms are quite distinct.
-----Original Message-----
From: John Thomas [mailto:datastagehelp@yahoo.com]
Sent: Tuesday, 09 October 2001 05:18
To: datastage-users@oliver.com
Subject: constraints
Hi all,
This is my first posting to the list. Pardon me if
this has been discussed.
I am loading the target table with one main source
table and few reference lookups.
I am looking for a surrugate key in one of the lookup
table that can give me null values. I am defining my
constraint so that if the ref. surrogate value is null
then reject it. (in1.lkupsuurkey ).
I am getting the error while running the job.
How is this type of situation handled otherwise in
datastage?
TIA
__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1
-----Original Message-----
From: John Thomas [mailto:datastagehelp@yahoo.com]
Sent: Tuesday, 09 October 2001 05:18
To: datastage-users@oliver.com
Subject: constraints
Hi all,
This is my first posting to the list. Pardon me if
this has been discussed.
I am loading the target table with one main source
table and few reference lookups.
I am looking for a surrugate key in one of the lookup
table that can give me null values. I am defining my
constraint so that if the ref. surrogate value is null
then reject it. (in1.lkupsuurkey ).
I am getting the error while running the job.
How is this type of situation handled otherwise in
datastage?
TIA
__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1