Can we have one Output Card for more than one Table

jackcool · Post by **jackcool** » Wed Apr 19, 2006 2:56 pm

Hi,
I was trying to create an OutputCard that can set data across multiple tables.Here is my scenario
My source is file
My Target is Oracle Database.Database structure is
A- No dependencies
B- Has FK reference to A
C- Has FK referebce to B

I created a type which composes of A --B --C.
Now my question is is it valid ?

janhess · Post by **janhess** » Thu Apr 20, 2006 2:02 am

You can only update one table per output card.

jackcool · Post by **jackcool** » Thu Apr 20, 2006 9:38 am

janhess wrote:You can only update one table per output card.

Thanks for the reply.If thats the case how can i pass Foreign Keys to other tables.Let me be more specfic
suppose if i have three seperate cards for table A,Table B and Table C.
Once data is inserted in table i need to pass the foreign key reference to the data to be inserted in table B.My primary keys are not businees keys they are surogate keys taken from a Database sequence.So its not available in Input Cards ,so i need a way to pass foreign keys from one Output Card to other.

jgibby · Post by **jgibby** » Thu Apr 20, 2006 10:17 am

From a given output card you can not only reference any of the input card, but also any of the preceeding output cards. So in the output card for B's FK reference to A, use the key value from the output card for A.

John

jackcool · Post by **jackcool** » Thu Apr 20, 2006 1:50 pm

jgibby wrote:From a given output card you can not only reference any of the input card, but also any of the preceeding output cards. So in the output card for B's FK reference to A, use the key value from the output card for A.

John

Thanks for the reply.
Iam new to this product i tried to drag the values from Output Card but i was not able to.Do you have sample syntax i can use ?

jgibby · Post by **jgibby** » Thu Apr 20, 2006 3:57 pm

There are a couple of ways to do this. You could right-click on the A.key column and select "Copy". That will put the item in your clipboard. Then paste it into the B.FK_A column.

The other way to accomplish this with drag-and-drop is to get the A output card on top and drag the card title bar to empty space in the From/To Frame of the Map Designer. That will put just the A output card in its own mdi window. Then, in the To MDI window, switch to the B output card and you can drag the A.Key column to the correct field on the B card.

Check out page 25 of the Map Designer Reference Guide PDF.

jackcool · Post by **jackcool** » Fri Apr 21, 2006 9:39 am

Thanks it worked!!!.But iam still not sure how this works in one to many scenarios.In otherwords suppose relationship between Table A and Table B is one to many and if my source have two rows of A and 5 rows each of B.I am not sure how tx is going to populate appropriate FK.I created my OutputCards in following way

OutputCard
Card 1 -- for Table A
Card 2 -- refers to functional map XYZ

XYZ has TWO Input Cards
XYZInCard1 -- refers to type B
XYZInCard2 -- FK of A

Out putCard
XYZOutCard1-- maps type and FK

jgibby · Post by **jgibby** » Fri Apr 21, 2006 12:54 pm

With DSTX, it is possible to produce a cartesian product just as in SQL when you do a select and join two tables incorrectly producing large number of rows returned.

I guess I don't understand fully what you are trying to produce. Plus, I have not used the DB interfaces as my situation has not allowed me to do so yet.

John

jackcool · Post by **jackcool** » Tue Apr 25, 2006 9:19 am

jgibby wrote:With DSTX, it is possible to produce a cartesian product just as in SQL when you do a select and join two tables incorrectly producing large number of rows returned.

I guess I don't understand fully what you are trying to produce. Plus, I have not used the DB interfaces as my situation has not allowed me to do so yet.

John

As you mentioned iam getting incorrect output.

my input is as follows
Line1 ---- 1XXXXXXXXXXXXXX (Type A)
Line2 ---- 3XXXXXXXXXXXXXX (Type B)
Line3 ---- 3XXXXXXXXXXXXXX (Type B)

Line4 ---- 1XXXXXXXXXXXXXX (Type A)
Line5 ---- 3XXXXXXXXXXXXXX (Type B)
Line6 ---- 3XXXXXXXXXXXXXX (Type B)

I created a one input card
Data --->
Record(s) -->
Type A --->
Type B(s)
when i run the map i see 4rows of Type B for Line 1 and 4 rows of Type B for Line 2 .

ideally i should see 2 rows of Type B for each of Type A only.Any thoughts ?

jgibby · Post by **jgibby** » Tue Apr 25, 2006 11:16 am

I think I stepped into this with good intentions, but think I am out of my really small arena of expertise. Brings back the saying of when you find yourself in a hole, stop digging.

I surrender the floor to those more experienced in Jack's issue.

jackcool · Post by **jackcool** » Tue Apr 25, 2006 4:04 pm

jgibby wrote:I think I stepped into this with good intentions, but think I am out of my really small arena of expertise. Brings back the saying of when you find yourself in a hole, stop digging.

I surrender the floor to those more experienced in Jack's issue.

Atlast i was able to get it resolved.
First of all i would like thank everyone who gave their sugesstions.
Special thanks to John.Your suggestions helped me to move forward in the right direction.

Here are my observations.previously i passed series object and foriegn key to functional map.it created duplicate rows of data.Instead of passing foreignkey as arguement i modfied my output card to derive(get from DB ) based on the inputs it has which is common between Type A and Type B and it worked.

jgibby · Post by **jgibby** » Wed Apr 26, 2006 7:18 am

Wow, there has to be some axiom for me to live by in there somewhere.

Shut my pie hole and people will be able to figure things out faster.

kudos to you for getting it to work Jack. TX is such an interesting product, sometimes you just have to get in there and keep beating on it to learn what it can, and can't, do.

If I helped, then great, I'm happy. That's what I wanted to do was help. Hopefully though, I didn't confuse the situation because it I am unfamiliar with using TX to connect to the DB.

John

janhess · Post by **janhess** » Wed Apr 26, 2006 7:25 am

Getting foreign keys can be an interesting exercise. It's not a problem for existing data but when you are creating all new data, you've got to know when this is committed to the database.
So if you create a parent row, unless you commit it imediately, you can't get the foreign key for a child row from the database, you have to get it from the card creating the parent.
However, if the parent auto creates a key when the row is created, then you have to commit the row and get the key for the child from the database as the data isn't in the card for the parent.

jackcool · Post by **jackcool** » Wed Apr 26, 2006 10:02 am

janhess wrote:Getting foreign keys can be an interesting exercise. It's not a problem for existing data but when you are creating all new data, you've got to know when this is committed to the database.
So if you create a parent row, unless you commit it imediately, you can't get the foreign key for a child row from the database, you have to get it from the card creating the parent.
However, if the parent auto creates a key when the row is created, then you have to commit the row and get the key for the child from the database as the data isn't in the card for the parent.

I defined scope of the Transaction as Map ,i think that helped my scenario.Because all the cards are executing in same Transaction context.But as a general guideline what is the prescribed(best practice) scope for transaction ? is it Map,Card or Burst?

Also are they any best practices documents available for Tx ?

janhess · Post by **janhess** » Wed Apr 26, 2006 10:10 am

It really depends on how you update the database. I have maps that update lots of tables. To be able to get all the right keys at the right time I use a Burst of 1 on the input data (1 file) but other maps that just update 1 table I use a fetch unit of S.