'' in HASH file

DeepakCorning · Post by **DeepakCorning** » Wed Sep 14, 2005 8:55 am

Value for a column in the source table is ''. If I load it in a hash file what value it will have in the hash file.

Will it be '' or @NULL .

Also if it is ' ' and not '' then what will it be in HASH file.

I have to put a lookup on this field and thats why I need to know that shold i compare this with '' or @NULL or doesn't make a difference.

Thanks

RodBarnes · Post by **RodBarnes** » Wed Sep 14, 2005 4:01 pm

Under Universe (the underlying system below DataStage) a NULL is nothing, not a value. So '' will be an emtpy string not null.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 14, 2005 4:15 pm

In earlier versions (DataStage 4.1 and earlier, IIRC) a zero-length string was not permitted as a key value in a hashed file.

Since then it has been, and can be looked up successfully. "" and " " are different and would be separate key values.

It remains the case that @NULL is illegal as a primary key value in a hashed file.

DeepakCorning · Post by **DeepakCorning** » Wed Sep 14, 2005 5:39 pm

Correct me If I am wrong - You mean that if a value in the database is null and i put it in HASH file it will be '' , right??

I need this because I have a if else statement while comparing. It says that if XYZ value is 'S' then compare it with table.field and if it is not 'S' then compare it with ''.

The problem which I am facing is that when I say compare it with '' the lookup fails but when i say @null it passes.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 14, 2005 7:21 pm

It (a Transformer stage) won't even attempt a lookup if the reference key value is null, so there's no way you could claim that it succeeds. OK, it returns NULL, but that's the documented behaviour when the lookup fails or is not attempted.

DeepakCorning · Post by **DeepakCorning** » Wed Sep 14, 2005 7:32 pm

So you mean to say that I cannt compare the @null value(reference key) with a value in HASH file?? Should it throw a warning if I do so?

ray.wurlod wrote:It (a Transformer stage) won't even attempt a lookup if the reference key value is null, so there's no way you could claim that it succeeds. OK, it returns NULL, but that's the documented behaviour when the lookup fails or is not attempted.

chulett · Post by **chulett** » Wed Sep 14, 2005 8:22 pm

You can't "compare" a null value to anything. By definition null is the unknown value and all you can do is ask if it is null or not null. A compare will always take the "false" path, not throw an error.

And you do recognize the fact that '' (or quote-quote) is an empty string and not at all the same as a null, yes?

DeepakCorning · Post by **DeepakCorning** » Wed Sep 14, 2005 8:38 pm

Hmmmm......

Ok I get that..Let me keep the requirement/situation in front of you may be I will be able to explain better. There is a source table and five fields are getting compared with the help of HASH file lookup.
The compare condition is a if else compare. If field1 is "S" then i know that Field2 should be comared to some value and if field1 is not "S" it should be compared to nothing/null. Its not the only field which is getting compared there are 4 other fields as well. So 4 with values and 1 without value should get compared and hence pass/fail the lookup. So the question is simple when I am keeping the condition on that field shoul I say Field1 is not "S" then '' or @NULL. Because I have tried with '' and the lookup fails.
The '' value in HASH means @null in DB or '' in DB or theres no diff?? if there is no diff then why my lookup is failing?? ANy other reason of it failing?? P>S> the other four fields are good , they pass the lookup so they are not faulty.

chulett wrote:You can't "compare" a null value to anything. By definition null is the unknown value and all you can do is ask if it is null or not null. A compare will always take the "false" path, not throw an error.

And you do recognize the fact that '' (or quote-quote) is an empty string and not at all the same as a null, yes?

chulett · Post by **chulett** » Wed Sep 14, 2005 8:48 pm

Ok... clear as mud now.

These "five fields" are in your input link or the reference hashed file? What does this hashed file look like, by the way - what data fields, key fields, data types, etc - and how was it populated? Give us a better picture of your job and how you are handling the key expression(s) for this lookup and then maybe someone will be able to actually help you.

PhilHibbs · Post by **PhilHibbs** » Thu Sep 15, 2005 2:57 am

ray.wurlod wrote:In earlier versions (DataStage 4.1 and earlier, IIRC) a zero-length string was not permitted as a key value in a hashed file.

Since then it has been, and can be looked up successfully. "" and " " are different and would be separate key values.

It remains the case that @NULL is illegal as a primary key value in a hashed file.

We are using @NULLs in hashes, but the first key field is never @NULL. It seems to be working. Is this OK, or should we convert @NULLs to empty strings first?

DeepakCorning · Post by **DeepakCorning** » Thu Sep 15, 2005 11:09 am

Let me once again give a try to remove the mud

)

select
table1.field7
from
table1,table2
where
(if table1.field6 = 's' then table1.field1 = table2.field1
else ''=table2.field1)
and table1.field2 = table2.field2
and table1.field3 = table2.field3
and table1.field4 = table2.field4
and table1.field5 = table2.field5

Everythng is char type.
Table 2 is the HASH file. Table1 is DRS.

I hope it is clear now

mdan · Post by **mdan** » Thu Sep 15, 2005 3:44 pm

If the problem is the fact that you cannot compare NULLs, why don't you replace NULL with the text 'NULL'. Use NVL for the database to get word NULL instead of null value, and make sure that you have the word NULL in your hash file. If the field is a char (1 char) you may choose any unused letter instead of the text NULL.
Idea: any time when you have a null that make sense replace it with a default value in the source (use user defined SQL or an extra transformer stage).

DeepakCorning wrote:Let me once again give a try to remove the mud )

select
table1.field7
from
table1,table2
where
(if table1.field6 = 's' then table1.field1 = table2.field1
else ''=table2.field1)
and table1.field2 = table2.field2
and table1.field3 = table2.field3
and table1.field4 = table2.field4
and table1.field5 = table2.field5

Everythng is char type.
Table 2 is the HASH file. Table1 is DRS.

I hope it is clear now

Bala R · Post by **Bala R** » Thu Sep 15, 2005 8:36 pm

Following mdan's suggestion you could replace '' with ' ' or some default value using CASE expression in the column derivation. Apparently your DB is not oracle (blank is ' ' and not '' in oracle) and you can't use NVL.
I have a job where the first value in hash file is ' ' and I had tried using @NULL to lookup and it had failed. It runs correctly when I used ' '.
IHTH.