Getting Datatype From Hashed File

Andal · Post by **Andal** » Wed Apr 05, 2006 3:37 am

I am having a Situation to retrive the datatype from the Hashed File. I will get Hashed filename as a Input, and I have to do a Select from the Hashed file.

Let me give u a example

Select Col1, '|' , Col2 From HashedFile.

If Col1 is of Datatype Char and the corresponding Value is "-" and the Col2 is of Datatype Integer and the corresponding Value is "0", Then I have to Return True Else I have to Return False.

So I want to Know , how can we retrive the datatype of the column in the Hashed File. Another Info is I have to perform this task when my row count of the Hashed file is 1 or 0. This we can do that by taking Count(*) but i want to know how to get the datatype.

ArndW · Post by **ArndW** » Wed Apr 05, 2006 4:17 am

Andal,

I am confused by your requirements. A hashed file is usually read using a hashed file stage, not with an SQL query (mainly for performance reasons). The column type is not an attribute in either a table or a hashed file stage that can be read. It is possible to read the hashed file's DDL by using the DICT (ionary) definition, but this is an advanced step.

Are you certain you need to retrieve the column definition for a hashed file and that there is no way around this in your design?

Andal · Post by **Andal** » Wed Apr 05, 2006 5:03 am

Hi Arndw,

I want to do this in a routine. My input to the routine will be the Hash file name and my output would be either True or False based upon the scenario i have explained.

ArndW · Post by **ArndW** » Wed Apr 05, 2006 5:10 am

Hashed files have different data types to SQL. A hashed file column has two optional attributes to specify data representation:
1. The format/justification such as "12L" or "4R", and
2. An attribute for a conversion such as "D4/E" or "MD2".

The combination of these two attributes gives the effective data type for a column - this is very different from SQL definitions. Could you explain what you are trying to achieve and perhaps someone can come up with an alternate method of getting what you want?

Andal · Post by **Andal** » Wed Apr 05, 2006 5:46 am

Basically I want To return @TRUE or @FALSE, from the routine.

I will explain some scenarios.

Code: Select all

Hashed File Name = Hash_TableName

Col1 [Key] --> Datatype = Char
Col2 --> Datatype = Integer

The Values are like as

Code: Select all

Col1 = -
Col2 = 0

In this case I want to return True.

We may also have values like

Code: Select all

Col1 = aa (Other than Default Values)
Col2 = 0

In this Case, I want to return False from the routine.

In simple, if we are having only default values (For Char '-', For Int "0" and for date "1900-01-01 00:00:00") I want to return True , Else I want to return False as Ans from the routine.

Sunshine2323 · Post by **Sunshine2323** » Wed Apr 05, 2006 6:35 am

Hi Anand,

Can you please explain the logic behind checking the datatype?
Can't you make a comparison for '-' or 0, why do you also need to check the datatype?

Andal · Post by **Andal** » Wed Apr 05, 2006 6:39 am

At sometimes, For Char values, we will be having 0. In that case , I want to return False.

ArndW · Post by **ArndW** » Wed Apr 05, 2006 6:46 am

From my post earlier - you can't tell directly if a column is char or integer from the hashed file definition. You should work on the column's contents instead of its definition since hashed file contents are actually unstructured.

Andal · Post by **Andal** » Wed Apr 05, 2006 7:05 am

Hi Arndw,

Can You please show me , how to use this to identify the datatype of a column in the Hashed file

1. The format/justification such as "12L" or "4R", and
2. An attribute for a conversion such as "D4/E" or "MD2".

ArndW · Post by **ArndW** » Wed Apr 05, 2006 7:08 am

Andal - I've been trying to tell you several times now that these two attributes cannot with 100% accuracy determine the datatype of a column! If the justification is L instead of R it is probably text versus a number. If the conversion is MD<n> then it is most likely a number since the MD conversion stands for Masked Decimal. But even a 6R and MD0 defined column could still contain the value of "Hello World" in a hashed file.

Andal · Post by **Andal** » Wed Apr 05, 2006 7:23 am

Arndw,

Sorry for that. I read ur earlier post wrongly

you can't tell directly if a column is char or integer from the hashed file definition

I read it as You can, because i am desperately looking for the solution. Sorry for the confusion.

I will try to achive this by some other logic.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 05, 2006 3:55 pm

Hashed files do not have data types. You can store any value in any column in a hashed file (irrespective of the hashed file's table definition) if you're using a Hashed File stage or if you're using DataStage BASIC.

Therefore, your only possibility is to access the hashed file's table definition to read the metadata. This can be done in DataStage BASIC. The table definition is stored in the repository in a table called DS_METADATA; the key is the full category and hashed file name; there are multi-valued fields forming a collection of column definitions within the record structure, and the SQL data type is enumerated.