Banner DATBASE Workshop-III

1
Banner DATBASE Workshop-III

• Girija Chavala
• Northwest Oracle/Banner DBA

2
Grouping with aggregate functions

• Can be used with aggregate function
• Group by can have as many columns as you want
• Always display column in group by list
• CAUTION YOU CAN NOT INCLUDE COLUMN IN DISPLAY
  LIST IF IT IS NOT GROUP BY CLAUSE

3
Group by example

• List 1st 5 digits of zip in order and number of
  companies from each zip
• SELECT substr(nw_zip,1,5), count()
• FROM nwvend
• GROUP BY substr(nw_zip,1,5)
• ORDER BY substr(nw_zip,1,5)

4
GROUP BY EXERCISE

• Count number of companies, their average sales
  with no decimals, highest and lowest yearly sales
  with appropriate column names from NWVEND table
  by state.

5
JOIN multiple tables

• data is dispersed in several tables
• relationship is based same type data
• Join tables based keys or same type data
• Based on equality of the keys EQUIJOIN
• RIGHT join
• LEFT join

6
Example for EQUIJOIN

• List nw_ssn,
• nw_last_name,
• nw_first_name,
• nw_street_line1,
• nw_city,
• nw_state,
• nw_zip
• nw_yearly_sales

7
EQUIJOIN continues

• nw_ssn
• nw_last_name NWSSN
• nw_first_name
• nw_street_line1
• nw_city
• nw_state NWVEND
• nw_zip
• nw_yearly_sales

8
EQUIJOIN

• NWSSN -- nw_ssn
• NWVEND nw_tax_id

Contains same values
select NW_SSN, nw_tax_id, nw_company_name,
nw_last_name, nw_first_name, nw_street_line1,nw_
city,nw_state,nw_zip, nw_yearly_sales from
nwvend,nwssn where nw_ssn nw_tax_iD order by
nw_last_name
9
Left Join

• Includes all records in nwvend even though there
  is no match in nwssn table
• SELECT NW_SSN, nw_tax_id,
• nw_company_name,
• nw_last_name, nw_first_name,
• nw_street_line1,
• nw_city,nw_state,nw_zip,
• nw_yearly_sales
• FROM nwvend,nwssn
• WHERE nw_ssn() nw_tax_id
• ORDER nw_ssn desc

10
Right join

• Includes all records in nwssn table even though
  there is only match in nwvend table
• SELECT NW_SSN, nw_tax_id,
• nw_company_name,
• nw_last_name, nw_first_name,
• nw_street_line1,
• nw_city,nw_state,nw_zip,
• nw_yearly_sales
• FROM nwvend,nwssn
• WHERE nw_ssn nw_tax_id()
• ORDER nw_ssn desc

11
SELECT statement and its sequence
12
BANNER PRODUCT OWNERS

• GENERAL MODULE -- GENERAL
• FINANCE -- FIMSMGR/FIMSARC
• STUDENT -- SATURN
• AR -- TAISMGR
• POSITION CONTROL -- POSNCTL
• PAYROLL -- PAYROLL
• BANINST1 -- Owner of all views
  and other objects
• Owners are called schema owners. They own
  tables for that module.

13
FINANCE TABLES

• Common tables for all modules
• SPRIDEN -- Person/nonperson TABLE
• SPRADDR Vendor address TABLE
• SPRTELE -- Vendor telephone TABLE
• SPBPERS -- Vendor ssn/TIN table
• FTVVEND -- Vendor Validation table
• FTVVENT -- Vendor type table
• FTVITYP -- Vendor income type table
• FTVVTYP -- Vendor type validation table

14
Vendor list using Banner tables

• SPRIDEN -- name, id
• SPRADDR address
• FTVVEND -- vendor validation table
• FTVVENT -- vendor type

15
What is PIDM?

• Internal identification number
• Generated uniquely at the time of ID creation
• PIDM is replicated where ever person data is
  populated
• No one can delete pidm except DBA

16
SPRIDEN table

• DESCRIBE spriden -- repeatable table
• Always use spriden_entity_ind P for person
  search
• Always use spriden_entity_ind C for company
  search
• Always use spriden_change_ind is null

17
SPRADDR table -- repeatable table

• Search by address type. Example, BU, PR
• Always use spraddr_status_ind is null
• Inactive address
• Do not use phone fields from this table
• Finance addresses do not require from and to
  dates
• Sequence numbers are generated based on address
  type.

18
SPBPERS base table

• SSN/TIN is stored
• Not a primary key
• Warning is issued if some one already using same
  ssn
• Only one record per person

19
SPRTELE repeatable

• Telephone code
• Area code
• Phone number
• Extension
• Always use sprtele_status_ind is null
• Inactive phone numbers

20
FTVVEND validation table

• Vendor table
• Can contain persons or companies
• Spriden record has to exist before vendor is
  created