Problem in retreving Data from two different table

, ,
1

Hi,
I am facing problem in retreiving the data from two tables and the scenario is

Table1:
SNO CLASS
1 ABC
2 XYZ

Table 2:
ID SNO CLASS SNAME
1 1 ABC PAVAN
2 1 ABC KIRAN
3 1 ABC XYZ
4 2 XYZ RAMU

   I have to select  SNAMES from table2 by using CLASS in table1. Sname will difer from class to class

Can u pls provide me the input.

Thanks & Regards,
Hari

2

Hari,

Knowledge of SQL (structured query language) is an absolute necessity if you are going to continue to work in the integration area.

The Select adapter service template in the JDBC Adapter will do what you need. Instructions on its use are in the JDBC Adapter User’s Guide.

However, without understanding how relational databases and SQL work, you won’t get very far.

Google “SQL primer” and spend some quality time understanding databaes and SQL, then this will make more sense.

Mark

3

u need two select adapters…
step-1
in the first one select class from table 1.if u are getting a result set.from this step
step 2.
loop over the result set from above .and for every class get the corresponding SNames… and perform what ever logic u are supposed to …

if u are really good at sql …u can even create just one stored procedure which does all the above logic and returns the result…

HTH
sri

4

Be careful with your terminology. For people new to IS and the JDBC adapter, the difference between “adapter” and “service” is critical. The solution may indeed need two adapters, if the tables are in different databases. But based on the description, it’s more likely that only one adapter is needed and just one adapter service which does a join is needed.

5

rob,
right i was too casual in saying adapters… i actually meant adapter services…
so , i guess even doing a join should work.But if he is really novice in sql ,then using two different adapter services whould do the trick,
no matter which approach he takes ,there shouldnt be any problem .
thanks
sri

6

Thanks for ur help

7

Hi all, i have a small problem.
I wrote an Adapter service for selecting an ID depending on some conditions.It works for few inputs , but doesnt work for few inputs.
What would the reason be and where its going wrong ?
The connection is right, pointing to right database. If anyone would have passes through this situation before plz tell me the solution.
Thankyou

8

Not enough to go on…

9

Create a custom SQL Adapter Service and enter the following sql

Select SName from Table2 where table1.sno=table2.sno and table1.class=‘?’

and create an input parameter called Class of type String. Now Invoke the adapter service in yr flow and pass the value for the class.
This should solve your problem.

10

As the columns SNO and CLASS are present on both tables, a simple

Select SName from Table2 where table2.class=‘?’

would be sufficient.

An other useful statement would be
Select SName From Table2 where table2.sno = (select table1.sno from table1 where table1.class = ‘?’)

This will work even if class will not be present on table2.

Regards,
Holger

11

Thanx

12

hi
how to execute that query in developer

13

Hi Waseem,
Create a JDBC Connection. Create a Adapter Service, using this connection and DynamicSQL template with sql as ${sql}. Set the output variable as SName, OutputJDBCType VARCHAR and OutputFieldType as java.lang.String. run this service and when asked, supply the SQL posted above.

HTH,
Bhawesh.

14

hi all,
please do the following
iam sure u will get result

create a adapter connection, create a adapter service(custom SQL adapter service)
In the input JDBC type give varchar, in input field type give java.lang.string and in input field class(column in table)
In the output JDBC type give varchar, in output field type give java.lang.string and in output field SName(column in table)
in SQL Select SName From Table2 where table2.sno = (select sno from table1 where table1.class = ?)

regards
waseem

15

Waseem,

Please read the thread before posting. Whatever you have mentioned I have already posted.