Other clauses box is grayed out

[Xanadu]

Hello all,
in DRS stage the "other clauses" box is grayed-out (uneditable)
But i see some existing EPM jobs with contents in that box.
Is there any work around ..
(I searched Ascential Knowledge base and found similar problem existed in Oraoci stage and was fized in 5.1.1 - Case : G36262)

any ideas how it is to be done ?
thanks
-Xan

[ray.wurlod]

When the query type is "generated SQL", the "other clauses" field is enabled in a DRS stage that is feeding a stream input, but disabled in a DRS stage that is feeding a reference input.

This is correct behaviour. The assumption is that, in a reference lookup, you're retrieving one row, so that GROUP BY, HAVING and ORDER BY are irrelevant.

[Xanadu]

Ray,
"The assumption is that, in a reference lookup, you're retrieving one row, so that GROUP BY, HAVING and ORDER BY are irrelevant."

--> Consider this query

select max(colA),colB from table A where colB=<some value>

If I have to put this query in the reference table I need a group by on colB - thats where I needed the other clauses box.

The other problem is if I write the user ddefined query Datastage is recognising the ':" ....( in DRS stage parameter markers are identified by the ':' symbol)..do i make any change to the settings of my driver ?
The query is directly passed to the database rght if its user ddefined SQL...
The problem is the same query "generated" by Datastage fails when I say user defined SQL...
any comments ?

Thanks
-Xan

[sumitgulati]

Hi Xnadu,

What is the exact requirement? Is it to be able to make a lookup to a table using the following query:
select max(colA),colB from table A where colB=<some value>

Try this:
In the same job load a hash file using the following query:
select colB, max(colA) from table A group by colB

with colB defined as the key column. Then make a lookup to this hash file joining colB.

The setup would look like this

Code: Select all

DRS-->HASH
       |
  --> TFM -->

Regards,
Sumit

[ray.wurlod]

Currently, only the ODBC and UV stages support multi-row returns from reference lookups. Read the on-line help topic Defining Multirow Lookup for Reference Inputs.

[ray.wurlod]

Ray,
"The assumption is that, in a reference lookup, you're retrieving one row, so that GROUP BY, HAVING and ORDER BY are irrelevant."

--> Consider this query

select max(colA),colB from table A where colB=<some value>

If I have to put this query in the reference table I need a group by on colB - thats where I needed the other clauses box.

The other problem is if I write the user ddefined query Datastage is recognising the ':" ....( in DRS stage parameter markers are identified by the ':' symbol)..do i make any change to the settings of my driver ?
The query is directly passed to the database rght if its user ddefined SQL...
The problem is the same query "generated" by Datastage fails when I say user defined SQL...
any comments ?

Thanks
-Xan

You don't need colB in the SELECT clause of that query, because you already have its value. Therefore you don't need GROUP BY.

Parameter markers are different depending on the database type; most of the world uses ?, but Oracle needs :1, :2 and so on.

[Xanadu]

Currently, only the ODBC and UV stages support multi-row returns from reference lookups. Read the on-line help topic Defining Multirow Lookup for Reference Inputs.

--> Thanks for ur input Ray..
I know that only Universe and ODBC stages return multiple rows..
but I just want to return a single value..consider this query..
select max(colA) frmo table A where colB=<some value>
tghat column B was added in the select stmt so that I can use it as the key during the lookup..

[Xanadu]

Hi Xnadu,

What is the exact requirement? Is it to be able to make a lookup to a table using the following query:
select max(colA),colB from table A where colB=<some value>

Try this:
In the same job load a hash file using the following query:
select colB, max(colA) from table A group by colB

with colB defined as the key column. Then make a lookup to this hash file joining colB.

The setup would look like this

Code: Select all

DRS-->HASH
       |
  --> TFM -->

Regards,
Sumit

--> Thanks sumit..wud do that..

[ray.wurlod]

Currently, only the ODBC and UV stages support multi-row returns from reference lookups. Read the on-line help topic Defining Multirow Lookup for Reference Inputs.

--> Thanks for ur input Ray..
I know that only Universe and ODBC stages return multiple rows..
but I just want to return a single value..consider this query..
select max(colA) frmo table A where colB=<some value>
tghat column B was added in the select stmt so that I can use it as the key during the lookup..

Yes, but you don't need colB in the select CLAUSE

If, in the Transformer stage, you identify colB as key, it goes into the WHERE clause - you don't need to provide any mechanism for returning it.
It's a perfectly legitimate to "lie through your teeth" to DataStage about what the key column is; it simply means the search key, whether that happens to be the primary key or some secondary key. In the latter case it helps (a lot) if the non-primary-key column is indexed!

[vmcburney]

Hi Xanadu, I just answered your other thread only to discover sumitgulati had the same answer in this thread! Well done sumitgulati it's exactly the right thing to do. Putting your aggregation results into a hash file is far more efficient, you will only run the group by statement once (when you build the hash file) instead of running it for every row as a lookup.

[Xanadu]

If, in the Transformer stage, you identify colB as key, it goes into the WHERE clause - you don't need to provide any mechanism for returning it.
It's a perfectly legitimate to "lie through your teeth" to DataStage about what the key column is; it simply means the search key, whether that happens to be the primary key or some secondary key. In the latter case it helps (a lot) if the non-primary-key column is indexed!

--> THanks Ray but if I identify colB as key in the transformer that key is automatically getting added to the column list in the DRS stage...
what I did was I removed colB from the column list and then went into the transformer added this colB and made it the key.
Then I find colB back in the column list in the DRS stage.. :-S
yeah..I already indexed the search key..

Thanks.

[ray.wurlod]

Looks like the DRS behaves differently from other stage types. Report it as a bug. Workaround could be to use explicit stage type, or ODBC, which I'm aware isn't an acceptable workaround for PeopleSoft deployment.