Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Passing Identity Field to DM FLow

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Passing Identity Field to DM FLow
 Login/Join
 
Member
posted
Hi

I have a two part question

As part of a flow, I need to determine the current value of an Identity field in SQL Server, store that value in a variable and pass it to DM Flow.

There is a SQL commeand - SELECT IDENT_CURRENT(TABLE_NAME) - which will return the current identity value.

In the flow, I will increment it and then concatenate that field with a field from a data source.

Is there a library of templates/fex's to look at that demonstrate how to create a fex that will call a sql command,
and store the result in a amper variable?


part 2 -

There will be 2 targets for this flow, but the same table. The soure has a 2 groups of fields - Primary and Secondary.
The target only has a Single set of fields to handle the source fields.

For example

Target A - TABLE_A

Source Target
Prim_city City
Prim_zip Zip


Target B - TABLE_A

Source Target
Sec_city City
Sec_zip Zip


Do i know which of the targets will get processed first ? i.e. Target A, or Target B. I see an issue with calling assigning the correct Identity Value when dealing with multiple targets. Or, should I do this sequentially?

Any help would be appreciated,

Thanks,

Jim
 
Posts: 5 | Registered: August 01, 2007Report This Post
Guru
posted Hide Post
You can use a Transact-SQL command in a DBMS SQL Flow. Right click on the SQL object to select Adapter MS SQL Server and the desired connection. Then select SQL Statement and enter

SELECT IDENT_CURRENT('table_name') AS curid

Note that the table name should be enclosed in quotes and that you should give the column a name of your choice, her "curid."

Add a New target and select Adapter of Formatted File and Format Alpha. Enter a synonym name of your choice, say "baseaapp/tabid."

Create a Process Flow that calls your DBMS SQL Flow and your original data flow.

In 7.7 you can simply right click on the Data FLow and check the checkobx "Get Paramaters using Synonym" and for "File Name" enter the name of the table you created, "baseapp/tabid." That will pass the a variable with the same name as the field, "curid."

Now you can run the Process Flow.

Is your target MS SQL Server? If so I don't understand your second question, there should be no need for multiple targets objects if there is just one target table.

Is your target a flat file? If so they get written in the order you added them.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
Hi Clif,

Thank you for your response,

To answer your question, based on the existense of a value in a certain field, i need to create a new record in TABLE_A.

For example, if there is a value in the 'Secondary City' field in the table, then create a new record, otherwise, just write the current record to TABLE_A. This is why i thought about this approach - i.e. 2 targets objects, but actually writing to the same table.
 
Posts: 5 | Registered: August 01, 2007Report This Post
Guru
posted Hide Post
Thanks, I understand now. I tried that and it works fine. Use the Validate tab on the second target table to only accept rows with a value in "Second City."


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Passing Identity Field to DM FLow

Copyright © 1996-2020 Information Builders