Compare with non-key columns

[anu123]

Helo gurus,

I want to compare non-key columns of an Hashed file with incoming data and apply some business rules.
I have cols. A,B(both are not keys in hash/source) of current month.I want to compare A/B with A1/B1 of an Hashed File (being created from same source table, but contains entire data inculding current month's).So I want to compare A/Bs of current month with A/Bs of previous period(s).

source = Oracle Table
Target= Oracle Table


thanks in advance.
anu123

[rasi]

Anu

Could you provide example for us to understand what you want. This will help in understand the requirement

Siva

[anu123]

Anu

Could you provide example for us to understand what you want. This will help in understand the requirement

Siva

Thanks for the reply Siva.

I have an open TICKET from a particular Domain/Region (A/B) in current month.Iam trying to find out whether there is any TICKET(s) with same Domain/Region (A/B) opened/closed in previous months.


Thanks in advance,
Anu123

[rasi]

Apply your business rule while creating the hash reference file for the previous month to be compared with the current month records. You need define key column/s in your hash file. In your case I am guessing it will be ticket number. Your hash file will have the ticket number key plus domain, region and other columns. Do a lookup to with your source which will give the answers your want.

[anu123]

Apply your business rule while creating the hash reference file for the previous month to be compared with the current month records. You need define key column/s in your hash file. In your case I am guessing it will be ticket number. Your hash file will have the ticket number key plus domain, region and other columns. Do a lookup to with your source which will give the answers your want.

Thanks again,

you are right. I have Ticket_Num(key),Domain,Region...in HASHed file. But, How can I do a lookup on Domain/Region which are non-key colunms. And Ticket_Num is unique to every ticket. The current month ticket has a different Ticket_Num w.r.t the previous tickets from same Domain/Region. Sorry,may be it sounds silly to you, gurus.

[rasi]

If Tickect is not the key column then while creating the hash file create Domain and Region as your key column. Give me some sample values you have in your source and in hash and the result you expected. May be that can give more idea towards solution

[ray.wurlod]

Hashed File stage can only do key-based "=" lookups. If you really do need to lookup against non-key columns in a hashed (not "hash") file, you can use a UV stage. It will be slow unless you index the search columns. Search the forum for more information.

[sun rays]

you are right. I have Ticket_Num(key),Domain,Region...in HASHed file. But, How can I do a lookup on Domain/Region which are non-key colunms. And Ticket_Num is unique to every ticket. The current month ticket has a different Ticket_Num w.r.t the previous tickets from same Domain/Region. Sorry,may be it sounds silly to you, gurus.

if you are running this job only once a month, then I assume your target table has the Tickets, Domains/Regions corresponding to the prior months.
In this case just extract domains and regions from your table and write to a hash. Make the Domain and Region as key, and do a lookup with your main stream ( current months data). This would let you know if the Domains and Regions already existed in your list.
However if you want to get the ticket number of the previous months data, then this is not the approach.

[anu123]

Hashed File stage can only do key-based "=" lookups. If you really do need to lookup against non-key columns in a hashed (not "hash") file, you can use a UV stage. It will be slow unless you index the search columns. Search the forum for more information.

Thank you all for the valuable inputs. Iam working in that direction.Once I finish the job, I will post my approach.

[anu123]

Hashed File stage can only do key-based "=" lookups. If you really do need to lookup against non-key columns in a hashed (not "hash") file, you can use a UV stage. It will be slow unless you index the search columns. Search the forum for more information.

Hi Ray,

I tried to use UV Stage, but when Iam trying to view the data from UV Stage, its giving me error.

Error is :

[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected symbol. Token was "CASE_TYPE". Scanned command was FROM "DSLink10" SELECT CASE_TYPE "

CASE_TYPE is 1st colunm in select list. Do I need to set up any thing?

Thanks in advance

[ray.wurlod]

This error indicates that CASE_TYPE is not defined in the metadata (file dictionary) for the UV table (hashed file). Verify either by importing the UV table definition (click on Detail - you don't need to complete the Import process). Beware that column and table names are case sensitive. If it contains lower case letters, try enclosing it in double quotes.

[anu123]

This error indicates that CASE_TYPE is not defined in the metadata (file dictionary) for the UV table (hashed file). Verify either by importing the UV table definition (click on Detail - you don't need to complete the Import process). Beware that column and table names are case sensitive. If it contains lower case letters, try enclosing it in double quotes.

Hi Ray,

Thanks a lot for quick reply.
I think this is metadata ssue.

I was trying to use the same table definitions (same col. order, case-sensitive ) in UV-Stage that I used to create the Hashed file.While creating hashed file, CASE_TYPE was the 1st field and my KEY field is 8th field.So my colunm list in UV-Stage was in the same order( i.e CASE_TYPE as 1st and KEY col. as 8th ...).

Later, I imported table definitions from "UNIVERSE FILE" and loaded into UV_Stage and viewed the Hashed file data successfuly.
In Imported table definitions, I found, Cols order is not same as of mine when creating the hashed file.


My doubt is, In hashed file, how the data is stored? In any chance, the col. order (col1,col2,col3,...) would be changed to make the KEY col. as Col1.(In case my KEY col is some where in the middle).

[ray.wurlod]

Hashed file navigation is by ordinal number of the field within the record, so that order is vitally important. In a Hashed File stage you have access to the field number in the Columns grid (if completed they are used in preference to the column names); in a UV stage you do not, so must use the exact column names.

[anu123]

Hashed file navigation is by ordinal number of the field within the record, so that order is vitally important. In a Hashed File stage you have access to the field number in the Columns grid (if completed they are used in preference to the column names); in a UV stage you do not, so must use the exact column names.

thanks ray.

I could understand "access to the field number ". Could you please explain with an example..

[rasi]

Hi Anu

If you are using hash file then the column name doesn't really matter. It is just a name you give to read or write. It is same like creating a sequential file and using it in your job. The field order in which you write to a hash file is how you read it back. You can give any name to the field but it will still bring you in the same order in which you wrote it.

Whereas for UV it is same like accessing your database table in which you have to say the right column name.

Try a simple job to create a hash file and another job to read that hash file with different column name or swap all your columns. The result will be same if you view the hash file in both the job.