Resolution:
Description:
=========
Database queries using equijoin , union and leftouter join.

Environment:
==========
TIBCO BusinessEvents 4.x , 5.x
Operating System(s): All
Oracle database

Resolution:
=========
// Query using two tables with equijoin
String queryText1 = "SELECT testA.NAME , testA.SEX , testA.LOC , testB.DEP FROM testA, testB WHERE testA.NAME = testB.NAME";    

// Query using union on two tables
String queryText2 = "select testA.NAME from testA where testA.NAME = 'Arjun' union select testB.DEP from testB where testB.NAME = 'Arjun' order by 1";

// Query using Left outer Join on two tables
String queryTest3 = "select testA.NAME NAME ,testB.DEP DEP from testA left outer join testB on testA.NAME = testB.NAME";


Steps:

1). Create a table A named testA in oracle database;
SQL script:
    ------------
    create table testA (NAME varchar2(10) , SEX varchar2(1), LOC varchar2(10));
    
2). Create a table B named testB in oracle database;
SQL script:
    ------------
    create table testA (NAME varchar2(10) , DEP varchar2(10));

3). Insert a record each in testA , testB using the following sql statements
SQL scripts:
    ------------
    insert into testA values ('a1' , 'M' , 'KOL') ;
    insert into testA values ('a2' , 'M' , 'KOL') ;

    insert into testB values ('a1' , 'PHY' ) ;
    insert into testB values ('a2' , 'PHY' ) ;

4). The query SELECT testA.NAME , testA.SEX , testA.LOC , testB.DEP FROM testA, testB WHERE testA.NAME = testB.NAME would return 4 rows so create a table testC with four columns .

SQL scripts:
    ------------
    create table testC (NAME varchar2(10) , SEX varchar2(1), LOC varchar2(10) , DEP varchar2(10));

5). Import the table testC  in BE using DatabaseConcept import property.
    
6). Confirm JDBC resource connectivity and that it is successful working.

7). Create a rule function on startup to see how the results are retrieved for various SQL queries . Sample code shown below .


    --------------------------RuleFunctions.Initialize-------------------------------
        ============
    // Query using two tables with equi join
    String queryText1 = "SELECT testA.NAME , testA.SEX , testA.LOC , testB.DEP FROM testA, testB WHERE testA.NAME = testB.NAME";    

    // Query using union on two tables
    String queryText2 = "select testA.NAME from testA where testA.NAME = 'Arjun' union select testB.DEP from testB where testB.NAME = 'Arjun' order by 1";

    // Query using Left outer Join on two tables
    String queryTest3 = "select testA.NAME NAME ,testB.DEP DEP from testA left outer join testB on testA.NAME = testB.NAME";
    
    
    Database.setCurrentConnection("/SharedResources/Jdbc");
    Database.beginTransaction();
    // Replace queryText  with queryText1, queryText2 , queryText3 each time to see and view the results
    String queryText = queryText1";    
    try
    {
    Concepts.TT.TESTC[] c = Database.queryUsingSQL("/Concepts/TT/TESTC",queryText,true);            
    System.debugOut(" Data Retrieved for nodes : " + c@length);
    // assert database
        for(int i=0; i < c@length; i++)
        {
                Database.assertDBInstance(c[i],true);
        }
    }    
    catch(Exception e)
    {
        // do nothing supress
        System.debugOut(e@stackTrace);
    }
    
    finally
    {
        // unset database connection
        Database.unsetConnection();
    }
    --------------------------RuleFunctions.Initialize-------------------------------

Database queries using equijoin , union and leftouter join.