How to Achieve this Logic?

laknar · Post by **laknar** » Wed Dec 03, 2008 4:36 am

Hi All,

I have two tables one is code and another is Customer.

Number Code CustID
123456 12 123
123456 22 234

CustID Address
123 laknar
234 standard

I have to write a logic if code starts with 1 the populate corresponding Address column based on CustID
strucked with the second logic
else if code starts with 2 then look back the address column which has come for code starts with 1 using CustID(i.e laknar).

Can anyone advice this logic .

battaliou · Post by **battaliou** » Wed Dec 03, 2008 6:16 am

You can use a stage variable to capture previous addresses, so you can have something like:

if CustID[1,1] = '1' then DSLink1.Address else StageVar1

simply populate your output column with StageVar1

This assumes data is sorted in the correct order of processing.

laknar · Post by **laknar** » Wed Dec 03, 2008 8:46 am

Hi,

i believe the stage variable will only hold current processing data and it will not go back to previous data.
loop will run when in sequential mode.
f CustID[1,1] = '1' then we have to go for CustID[1,1] = '2' address values.
if not correct please correct me.

or shall i have corresponding code 2 values lookup to link the address.

battaliou · Post by **battaliou** » Wed Dec 03, 2008 9:10 am

Dont worry about variables beginning with 2, the logic reads:

StageVar1 = if CustID[1,1] = '1' then DSLink1.Address else StageVar1

StageVar1 will be persistent so you can access previous values.

laknar · Post by **laknar** » Wed Dec 03, 2008 9:19 am

Hi,

.
if CustID[1,1] = '2' then Address
Else if CustID[1,1] = '1' then StageVar1
else setnull

for above logic what sort is required.

order by number,code desc

shall i enable the sort in the transformer stage.

kandyshandy · Post by **kandyshandy** » Wed Dec 03, 2008 9:56 am

It depends on your requirements. If you want to process the records in the order it comes, don't do sort. If you could explain with more example of codes, it would be better.

code cust
12 A
31 B
22 C
21 D

So what should be the ouput, if the input is like shown above..

laknar · Post by **laknar** » Wed Dec 03, 2008 10:24 am

Hi,

I want to sort some columns and order by descending.
number,code
and i want to keep the same sort in following stages.

kandyshandy · Post by **kandyshandy** » Wed Dec 03, 2008 10:35 am

if you want to sort on number and code (both DESC), then the stage variable logic which was discussed earlier will not work. Requirements should be clear so that people here can give their thoughts.

laknar · Post by **laknar** » Wed Dec 03, 2008 10:29 pm

I have two tables one is code and another is Customer.

Number Code CustID
123456 12 123
123456 22 234

CustID Address
123 laknar
234 standard

if code starts with 2 then corresponding address from customer table.
if code starts with 1 then address column of code[1,1]='2'.

this is my logic.please advice me.

laknar · Post by **laknar** » Wed Dec 03, 2008 10:32 pm

I have two tables one is code and another is Customer.

Number Code CustID
123456 12 123
123456 22 234

CustID Address
123 laknar
234 standard

if code starts with 2 then corresponding address from customer table.
if code starts with 1 then address column of code[1,1]='2'.

this is my logic.please advice me.

laknar · Post by **laknar** » Wed Dec 03, 2008 10:34 pm

I have two tables one is code and another is Customer.

Number Code CustID
123456 12 123
123456 22 234

CustID Address
123 laknar
234 standard

if code starts with 2 then corresponding address from customer table.
if code starts with 1 then address column of code[1,1]='2'.

this is my logic.please advice me.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 03, 2008 11:48 pm

You have been given a solution. Did you try it?

laknar · Post by **laknar** » Thu Dec 04, 2008 1:30 am

hi ray,

I have tried.but im not getting the expected result.
bit of confusion with the sort.

db2stage------lookup-----TX------Dataset

sorting the code by descending.
how can i keep the same in next stages.

laknar · Post by **laknar** » Thu Dec 04, 2008 7:02 am

Hi,

can any one advice me regarding this

Mike · Post by **Mike** » Thu Dec 04, 2008 7:14 am

The sort order is maintained as long as you don't repartition or resort the data.

I suspect that you may not have managed your partitioning strategy correctly.

A simple test... use a single node configuration file... if your results are correct running on a single node, then you have a partitioning logic flaw. If your results remain incorrect, then you have a transformation logic flaw.

Mike