passage parameters between stages

[fabio]

Hi,

how I can pass parameters between stage oracle?
the value of a field of Output Link of a first one stage Rracle must be the value of where condition of a second stage Oracle (input Link).

Thanks,
Fabio

[sachin1]

job design

oci stage---------->transformer---------->oci stage(check key columns for where condition)

[chulett]

Simply mark the output field as a Key field and it will automatically be used in any generated update 'where' clause.

[fabio]

Hi, I explain myself better.

I must use the field ID and DAY, result of query of first oci stage
select x.ID , x.DAY from tab x,
into the where condition of the second oci stage:
select y.ID , y.DAY from tab y where x.ID=y.ID and y.DAY between x.DAY-59 and x.DAY

The condition into the where is not equality condition.

Thanks
Fabio

[kcbland]

Is your second Oracle stage a TARGET or a REFERENCE? Please draw a diagram of your design, I think we're confused....

[sachin1]

need to write user defined sql using ":" for input columns for example

select deptno,case when deptno = :1 then dname else 'null' end from deptsb

[chulett]

As noted, confusion reigns. With two selects, the 'second' OCI stage must be a reference lookup, but it would be best if you could be more explicit and do a better job of describing the job design.

Assuming a direct OCI reference lookup, you'll need two key columns - ID and DAY - and then user-defined sql to get the 'between' in there. Suggest you start off letting the stage generate your sql and then 'adjust' it from there so you get the basic syntax correct.

[fabio]

Hi,

I must resolve this problem: output data from table and insert into second oci TARGET stage:

SOURCE query:
Select id, day, sum(qta1), sum_qta60
from tab
group by id, day

My problem is the output "sum_qta60" because this is the qta of the previous 60 days (from the day):

I' have try whit reference from the same table but i have only equal lookup condition for the day

OCI REF (select id, day from tab)
|
| It is not possible this Lookup condition:
| id, day between Souce.DAY-60
| Source.DAY
|
oci --------------> TRASFORMER ----- oci TARGET
Select id, day, sum(qta1)
from tab
group by id, day

Thanks
Fabio

[chulett]

Assuming a direct OCI reference lookup, you'll need two key columns - ID and DAY - and then user-defined sql to get the 'between' in there. Suggest you start off letting the stage generate your sql and then 'adjust' it from there so you get the basic syntax correct.

[fabio]

Hi,

I have designed the job like from you suggested.

The SOURCE oci stage has the query:
Select id, day, sum(qta1)
from tab
group by id, day

The key columns is DAY and ID

The REFRENCE oci stage has the query (Custom):
Select id, day
from tab
where
TO_DATE(DAY,'YYYYMMDD') BETWEEN (TO_DATE(:1,'YYYYMMDD')-59) AND TO_DATE(:1,'YYYYMMDD') AND
ID=:2
ORDER BY
DAY,
ID

The key columns is DAY and ID

I have implement Lookup among ID and DAY into the TRASFORMER
and I have implemente the constraint: NOT(REFERENCE.NOTFOUND)


The result in OUTPUT si only 1 record but this is not correct because into the reference i have more record for the id and into the range of 60 previus day

Thanks
Fabio

[chulett]

You can't use the NOTFOUND constraint with database lookups, it just doesn't work - it only seems to be valid for Hashed based ones. You need to check for null returns or whatever your query passes back when no rows are found.

[fabio]

OK, I' have change the contraint :

Not(IsNull( REFERENCE.ID))

but the result is the same and not corret : only 1 record .. ??

[kcbland]

Your reference SQL returns more than one result row, therefore, you have a multi-row return set (one-to-many join). There is functionality in certain stages to support this, but in general the OCI stages do NOT.

[chulett]

ODBC and UV are the only two stages that support a 'multi-row result set' - otherwise all you ever get is one record back.