can non-key columns looked up?

tom · Post by **tom** » Mon Jun 25, 2007 2:57 am

can non-key columns looked up?Pls give the reason for yes or no

ArndW · Post by **ArndW** » Mon Jun 25, 2007 3:18 am

In server jobs, non-key columns in hashed file cannot be looked up. Nonkey columns in database stage can used for lookups.

The reasons are based on the underlying access methods. When using SQL you can select on non-key columns but the database might have to go and do a full table scan.

Hashed files are only keyed on their {unique} key and the way that hashed file stages are set up does not allow nonkeys to be used for selection criteria. You can always use an ODBC stage to access a hashed file and select on nonkey columns, but don't hold your breath when waiting for queries on large files to come back

chulett · Post by **chulett** » Mon Jun 25, 2007 6:35 am

You might want to expand a little bit on your question, Tom.

Hashed File stage? Database stage? What does the phrase 'non-key columns' mean to you? Sometimes people confuse columns marked as 'key' columns with 'indexed' or 'primary key' columns, but it doesn't mean that at all, hence the question. And by 'looked up' you mean what? In one sense, the 'non key' columns are exactly what you are looking up.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 25, 2007 7:08 am

The short answer is "yes", except if using a Hashed File stage.

The next question is probably "how?". Think of "Key" in the metadata meaning "search key" rather than "primary key" in this context. Post a specific requirement to get a specific answer.