×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Can we pass records from update startegy to expression & then to other

Can we pass records from update startegy to expression & then to other

Can we pass records from update startegy to expression & then to other

(OP)
I have two tables T1 and T2
T2 is having amt fields where on joining two tables i get set of records(cartesian records).
I should add amts of those cartesian records or records with same PK and get one single record for set of records.
(i have checked that condition in EXP and flag rows to update strategy to update to add amts & get one record per set of records)
Now, i should pass that to lkp to join PK with LKP.PK to check same condition i.e now i should add LKP.amt fields of LKP if there are same set of PK as a result of join with T1.

Now i can again do that with exp and should use update strategy2 again.

so, can i use UPstrategy1 to pass PK1 to LKP.T3 to join and get records which will again pass to exp.

 

RE: Can we pass records from update startegy to expression & then to other

Let's start with the first 2 lines in your post.
Why are you creating a cartesion product to begin with?

The other lines in your post are very unclear. Are you trying to create a distinct set out of the cartesian set again?

Ties Blom
 
 

RE: Can we pass records from update startegy to expression & then to other

(OP)
I have T1 and T2
T2 have enrollid and some amt fields,if T2 is having multiple records with same enrollid then add amts and get a single record.
After this join to T1

Now,after this table T3 is also having enrollid and some amt fields,if there are multiple records with same enrollid then add all amts and get single record and now join that to T2

RE: Can we pass records from update startegy to expression & then to other

No need for a lookup then. Simply aggregate where you need to consolidate to one record for an enrollid and use joiner transformations. The key is to designate one table as the master and use outer join strategy to add data from the other tables.

Ties Blom
 
 

RE: Can we pass records from update startegy to expression & then to other

(OP)
Output one record meant that one record per member

If for each member there are 2 records then add those amts from two records and drop prev,current should contain amt of current record and prev record

Enrollid       accumid     accumvalue                      

1122               C1167          100

1122                 C1169          0

1123                  C77414       20

1123                    C77414      10    In these two records enrollid and accumid are equal

 O/P should be
1122              C1167        100                  
1122               C1169           0
1123               C77414        30

After adding amts with same enrollid and accumid we should get single record.
later join that with T1 table

 

RE: Can we pass records from update startegy to expression & then to other

You can use an aggregate transformation with group by on the ports EnrollID en Accumid which will get you the results you are after in your example.
However, are you familiar with the aggregate transformation at all? You will have to think in set-based terms, not in previous/current (ETL  <> programming)

Ties Blom
 
 

RE: Can we pass records from update startegy to expression & then to other

(OP)
Hi Blom

Thanks a lot thats working

But had some problem in display of amt fields i.e.
From source i am getting length of amt as 18 and target length is 9.

In agg amt is coming this way 79.47000000000.
i have used Round function to round but as target is of length 9,when used round function no value is displayed in target including zero....

Can you please assist me   

RE: Can we pass records from update startegy to expression & then to other

For a numeric field there is no concept of length. Scale and precision are the definition of a numeric field. You need to look into the definition of the target how these are set.
Depending on the tool you use to query the target it may or may not show the trailing zero's

Ties Blom
 
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close