on 04-25-2013 1:03 PM
I have 1 query which is related to my requirement.
My scenario is JDbc---->PI------->Jdbc .Here i want fetch data from database through the sender jdbc and insert values in receive jdbc.
Here my doubt was i want to fetch the data from more than a table and those tables not having any relationship(Primary key and foreign key and rows data are separated ).then how can i call in single statement?
Querying two tables without any relation?
Thanks
Ramanjaneya Reddy
HI Harneesh,
I have one quick question
you mean to say ,even my tables are Independence to each other , i can write stored procedure right?
can u plz elabrate more on this?
give me some example on this ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, I guess the best way is to build up a Stored Procedure (where you select data from the different tables), and call the SP from PI with the EXECUTE command value (in the JDBC Structure)
<StatementName5>
<storedProcedureName action=” EXECUTE”>
<table>realStoredProcedureeName</table>
<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>
</storedProcedureName >
</StatementName5>
Hi Rama - Yes you can write.
Not an expert but Just execute two select queries in your procedure something like below.
CREATE/ALTER PROCEDURE <<NAME>>
AS
BEGIN
SELECT * FROM <<table1>>;
SELECT * FROM <<table2>>;
END
else you can even define a temp table in your procedure with all the coloums in two tables and move the records into that. Later you can execute select * from temp table.
*you'll have null values in the resultset and you have to handle it in the mapping.
Hi Ambrish - Are you sure??
I don't think we need to have a relationship between the tables to extract the data using a stored procedure.
i think executing two select queries independently should extract all the information from two tables and if that doesn't work then the other option is to creating a temp table with all the columns from both the tables and executing a select query on that temp table will result all the rows from two tables.
@Rama : Did yo try that??
Regards,
Hareesh
Sorry Ambrish.. don't have any data base to try but don't you think below is possible?
create a common table which has all the fields from both the tables.
insert the first table data into common table(give blank value for the second table fields)
insert the second table data into common table(give blank values for first table fields)
Regards,
Hareesh
Agreed!! Don't know why do we even need PI to transfer the data between the tables as mentioned by Raja.(a simple stored procedure does that)
i was just trying to answer his question "you mean to say ,even my tables are Independence to each other , i can write stored procedure right?"
However letz wait Rama's comment..
Regards,
Hareesh
Hi GUYS THANKS FOR QUICK REPLY.
AS-IS scenario:
Jdbc Sender (2 or more tables)-Every table have one select query( the bda guy executed those Queryies manually ) and those data there are enter in Receiver tables(Tb1,Tb2,tb3)
source tables structure's and table are diff and each table carry one select query here they want to remove the manual process they want to use PI interfaces here .
as per my knowledge ihave take data:
i will careate datatype of sender like this format:
^rows
row1table1
row2oftable1
row1oftablw2
.....
Here my question was, is there any option to combined(join) 2 or more select queries in a single??
if not how can i achive this problem
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Rama -
How can we write a join without having any relationship between two tables? If there is such a way your DBA should've used it instead of executing the queries manually.
to state in other way, If you can extract all the information(from all the tables) using a single SQL query then you can use the same query in the JDBC sender channel which is not possible in your case.
as your tables are independent(there is no foreign key relationship) then you have to write an Stored proc to transfer data from one table to other. But here comes the question why we need PI to do that(as raja sekhar mentioned) as they can schedule that stored procedure to achieve that.
for some reasons, If you want to use PI then either executing the stored procedure/create separate channels for each table
Regards,
Hareesh
Why you want to use PI here? ask DB guy to write a proceded to pull data from different tables and update it new table.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
If a complex select statement is too difficult, you can try using the JDBCLookup function in the message mapping. By using this, you can retrieve data from multiple tables regardless of their relation. You will still need a primary JDBC sender channel as a trigger, and then in the message mapping, use the JDBCLookup to retrieve data from other tables.
To use this, you'll need to create a JDBC receiver with the parameters of the database. After that, create an External Definition object with Category "dbtab" and Messages "From Table Definitions". There will be a Communication Channel field where you can choose the JDBC receiver channel created and then the table that you'll want to access. After creating the external definition object, use the JDBCLookup function in Message Mapping (under Conversions category). You'll need to select the external def object created from within this JDBCLookup function. You'll then see 3 columns with only the 2nd column populated. These are the fields available in the table (seeing this also means that you are able to successfully connect to the db). Move the fields to the left (1st column) to make these the SELECT parameters and then move fields to the right (3rd column) for the output of the lookup.
We've used this for several integrations and a watchout point is that this approach will add an extra failure point. If the lookup channel goes down, you will see a mapping error as the fields will not be produced (failed lookup).
I hope this helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
You can derive data from 2 tables using a stored procedure but I am wondering how would a SQL developer write a query(SP) without foreign key relationship.
If you pick the data from different tables through multiple sender JDBC adapters and merge the messages in PI, you will need to correlate the messages and you will also need a linkage between messages.
Hope it helps.
Ambrish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Search online to find trick to select tables that has no primary key relationships. I see some link
as below.
http://billfellows.blogspot.com/2011/04/find-tables-without-primary-key.html
http://datacharmer.blogspot.com/2011/09/finding-tables-without-primary-keys.html
You basically write the query in select field of the sender jdbc channel.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.