How to retrive the data from two or more database tables with the help of alias and joins

Here i am retriving the data from 3 tables. there must be a one column which is comman in all table that is called as foreign key.

in our table "EMP_NO" is foreign key for "TABLE1 & TABLE2" and "EMP_DEPARTMENT_ID " is foreign key  for "TABLE2 & TABLE3"

TABLE1

--------------------------------------

EMP_NO       EMP_NAME

1            MUNNI

2            SHEELA

3            SHALU

4                  JILEBIBAI

-------------------------------------- 

TABLE2

-------------------------------------------------------------------

EMP_NO   EMP_DEPARTMENT_ID     EMP_DEPT

1        10                    IT

2      20                   COMP

3        30                   MECH

4                    50                  BOLLYWOOD

----------------------------------------------------------------------------------

TABLE3

-----------------------------------------------------------------------------------------------

EMP_DEPARTMENT_ID       EMP_ADDRESS

      10         DAHIWADI 1

      20         DAHIWADI 2

      30         DAHIWADI 3

      40                               DAHIWADI 4

----------------------------------------------------------------------------------------------

Query->

 SELECT      a.emp_name

           , b.emp_dept

                , c.emp_address

          

 FROM        table1 a

               , table2 b

               , table3 c

 WHERE  

                 a.emp_id=b.emp_id

     and      b.emp_departmet_id=c.emp_departmet_id;

----------------------------------------------------------------------------------------------    

In above query a, b, c are the alias.

and conditons given in where clause are the simple joins.

Actualy there are main 3 types of join in oracle.

1 LEFT Join.

2 RIGHT Join.

3 NATURAL Join

In oracle joins are declared using (+) sign.

for ex--

Left Join---

 WHERE  

                 a.emp_id (+)=b.emp_id

     and      b.emp_departmet_id(+)=c.emp_departmet_id;

RIGHT Join---

 WHERE  

                 a.emp_id=b.emp_id(+)

     and      b.emp_departmet_id=c.emp_departmet_id(+);

***When u use (+) sign it means that query retrive all records from that table and only saimilar records from other  table**

NOTE---Joins syntax in SQL and ORACLE are diffrent.

to see JOINS of SQL u can VISIT THIS site....

http://www.w3schools.com/sql/sql_join_left.asp

I hope u will get more info from there.

i also refer that site.

Thank you............................

VIEW's in sql /oracle

Basic Info of VIEWè

·         A view is a virtual table.

·         It is based on the result-set of an SQL statement.

·         A view contains rows and columns, just like a  table.

·         View’s are created from table or another view.

·         The fields in a view are fields from one or more l tables in the database.

·         You can add SQL functions, WHERE, and JOIN statements to a view.

Difference Between TABLE and VIEWè

·         A table has a set of definition, and it physically stores the data.

·         A view also has a set of definitions, which is build from  table or other view,  

and it does not physically store the data.

SYNTAX->

CREATE or replace VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

EXAMPLE->

CREATE or replace VIEW  test1_v

AS

Select empno , ename ,salary

From emp

Where salary>5000;

- The above example create a view(test1_v) from table (emp).

-  Like table you can select view using

  “Select * from view_name”

Ex- select * from test1_v;

Result=>

EMPNO          ENAME          SALARY

1                      AMOL             15000

2                      ROHIT             10000

3                      VEENA           12000

Assign the "responsibility" to user using plsql code.(this blog is refered from another blog ORACLE GURU")

-----------------------------------------------------------------------------------------------------
DECLARE
  l_user_id NUMBER;
CURSOR cur_rec
IS
SELECT * FROM fnd_responsibility_vl
                           WHERE UPPER (responsibility_name) = UPPER ('AE HRMS Manager');
BEGIN
    SELECT user_id INTO  l_user_id   FROM fnd_user
    WHERE user_name like '%ROHIDAS%123';

    FOR rec_cur IN cur_rec
    LOOP
    fnd_user_resp_groups_api.insert_assignment (
                                                  user_id                                => l_user_id,
                                                  responsibility_id                   => rec_cur.responsibility_id,
                                                  responsibility_application_id => rec_cur.application_id,
                                                  security_group_id                => 0,
                                                  start_date                            => SYSDATE - 1,
                                                  end_date                             => NULL,
                                                  description                           => NULL
                                                 );
                                            COMMIT;
    END LOOP;
END;

------------------------------------------------------------------------------------------------------------

How to register your own table in oracle apps...database

Hi frnd the following code(plsql procedure) is useful to you to register your own table in oracle apps.(Mostly it is usful to you when you work in alert management in oracle and want to creat alert on your own table)
---------------------------------------------------------------------------------------------------------

STEP--1-first you create your table. and add data in table.
Ex---
create table my_alert(item varchar2(10), qty number);


------------------------------------------------------------------------------
STEP--2-then run the following code..it will creat the procedure  "Proc_ad_ad".

CREATE OR REPLACE PROCEDURE proc_ad_ad (ap_sname VARCHAR2, ptable_name VARCHAR2)
  IS
     CURSOR c1  IS
      SELECT ROWNUM, column_name, data_type, data_length
      FROM all_tab_columns
      WHERE table_name = upper(ptable_name);

  va c1%ROWTYPE;
  BEGIN
      ad_dd.register_table (ap_sname, ptable_name, 'T');

    OPEN c1;

      LOOP
          FETCH c1  INTO va;

      EXIT WHEN c1%NOTFOUND;
         ad_dd.register_column(ap_sname,ptable_name,va.column_name,va.ROWNUM,va.data_type,va.data_length,'Y','N');
DBMS_OUTPUT.put_line ( va.ROWNUM ||va.column_name||va.data_type||va.data_length);
      END LOOP;

    CLOSE c1;

COMMIT;
END proc_ad_ad;
/
show error;
------------------------------------------------------------------------------------------------------------------
STEP-3
register table using following code.

begin
proc_ad_ad('PO', 'my_alert');
end;
--------------------------------------------------------

we provide 2 parameter to this procedure
1.Application Name (Ex.PO,AP,etc)
2.Table Name.(Ex-My_aalert).
-------------------------------------------------------

How to find Suppliers/vendors Bank name(and other bank related info). ..in PAYABLE MANGER responsibility

Required Tables  =>
        1) ap_bank_account_uses_all
        2) ap_bank_accounts_all

        3) ap_bank_branches 

        4) po_vendors 

        5) po_vendor_sites_all

-----------------------------------------------------------------
SELECT
       pv.vendor_name
       ,pvs.vendor_site_code
       ,abb.bank_name bank_name


FROM
        ap_bank_account_uses_all   abau
       ,ap_bank_accounts_all          aba
       ,ap_bank_branches               abb
       ,po_vendors                          pv
       ,po_vendor_sites_all              pvs


WHERE
            abau.external_bank_account_id = aba.bank_account_id
AND    aba.bank_branch_id        = abb.bank_branch_id
AND    abau.vendor_id            = pv.vendor_id
AND    abau.vendor_id            = pvs.vendor_id
AND    abau.vendor_site_id       = pvs.vendor_site_id

Find Concurrent Program information in ORACLE APPS using concurrent "program id"

 --------------------------------------------------------------

You will get the follwing inforamtion.=>>

 

execution_file_name
executable_id

concurrent_program_id
concurrent_program_name

responsibility_name

Useful Tables:--

1-FND_CONCURRENT_PROGRAMS 

2-,FND_EXECUTABLES 

3-fnd_request_group_units

4-fnd_responsibility_tl 

-------------------------------------------------------------------
select distinct
        b.executable_name
       ,b.execution_file_name
       ,b.executable_id
       ,a.concurrent_program_id
       ,a.concurrent_program_name
       ,d.responsibility_name
from
       FND_CONCURRENT_PROGRAMS  a
      ,FND_EXECUTABLES b
      ,fnd_request_group_units c
      ,fnd_responsibility_tl d
where
           a.executable_id=b.executable_id
    and c.application_id=d.application_id
    and a.concurrent_program_id=198703;