Customized GIL Web Reports

1
Customized GIL Web Reports
GIL Users Group Conference 4-5 June,
2001 Presented by Bob Trotter
2
Overview

• A quick look at GIL Web Reports
• Introduction to SQLPlus
• Example queries
• The Future
• Questions/More examples

3
Custom GIL Web Reports

• Security Issues
• Report Formats
• Total Columns
• Saving/Loading Queries

4
Introduction to SQLPlus

• Select/From/Where/Group by/Order by Clauses
• Operators
• Joins
• Functions

5
Select Clause

• List of columns to be included in report
• Separated by commas
• Can be qualified by table name/alias
• Can us functions

bt.bib_id, bt.author, substr(bt.title,1,30),
mm.display_call_no
6
From Clause

• List of all tables used in report
• Supply an alias

Bib_text bt, bib_mfhd bm, mfhd_master mm
7
Where Clause

• Used to limit rows of the result set
• Used for table joins
• Use boolean operators

bt.bib_id 12345 and bt.bib_id bm.bib_id
and bm.mfhd_id mm.mfhd_id
8
Where Clause - Operators
9
Where Clause Operators

• Character and date values are enclosed in single
  quotes
• Character data is case sensitive, so must appear
  exactly as it is stored in the database

10
Where Clause Examples

• bt.begin_pub_date in (1998,1999)
• bt.begin_pub_date between 1998 and 1999
  (between is inclusive)
• bt.begin_pub_date like 99
• bt.descript_form is not null
• bt.begin_pub_date not like 00
• bt.bib_id in (select max(bib_id) from bib_text)

11
Where Clause Multiple

• Joined by either AND or OR
• AND statements are evaluated before OR statements
• Parenthesis can be used to change the evaluation
  order

(bt.bib_id 12345 or bt.bib_id 23456)
and bt.title like Einstein
12
Table Joins

• Joins are performed in the Where Clause
• The common column from one table is set equal to
  that column from the other table. This is usually
  the primary key on one of the tables.

bt.bib_id bm.bib_id and bm.mfhd_id mm.mfhd_id
13
Table Joins Outer Join

• A standard join retrieves rows from two tables
  only when a matching row is found.
• In some cases, a value may be missing from one
  table, and now row would be returned.
• To display all rows, even those without a match,
  use the Outer Join. This is done with the ()
  symbol.

bib_history.operator_id operator.operator_id()
14
Functions - Character
15
Functions - Character
16
Functions - Numeric
17
Functions - Numeric
18
Functions - Date
19
Functions - Date
20
Functions Date To_char
Mask values
21
Functions Date - To_char

• to_char(receive_date, YYYY-MM-DD) 2001-06-04

22
Group By Clause

• Group By allows a set of rows to be grouped
  together and manipulated as a set.
• It is required when using Group Functions with
  other columns in a select statement.
• Group Functions return a row for a set of records

23
Group By Group Functions
24
Group By Group Functions
25
Order By Clause

• Denotes the columns to use to sort the result
  set.
• Multiple column names can be used separated by
  commas

26
Example 1
select distinct p.last_name ', '
p.first_name ' ' p.middle_name, p.ssan,
sum(f.fine_fee_balance/100) from patron p,
fine_fee f where p.patron_id f.patron_id
and f.fine_fee_balance gt 0 group by
p.last_name', 'p.first_name'
'p.middle_name, p.ssan order by
p.last_name', 'p.first_name'
'p.middle_name
27
Example 2

• select
• li.bib_id,
• ist.invoice_status_desc,
• ili.unit_price/100,
• ili.line_price/100,
• ili.quantity,
• ilif.amount/100,
• i.invoice_number,
• l.location_name,
• to_char(i.invoice_date,'yyyy-mm-dd'),
• i.invoice_status,
• lis.line_item_status_desc,
• bt.title

28
Example 2 (continued)

• from
• invoice i,
• invoice_line_item ili,
• line_item li,
• line_item_copy_status lics,
• bib_text bt,
• location l,
• invoice_line_item_funds ilif,
• fund f,
• invoice_status ist,
• line_item_status lis

29
Example 2 (continued)

• where
• f.fund_id 183 and
• f.ledger_id 5 and
• ilif.ledger_id f.ledger_id and
• ilif.fund_id f.fund_id and
• ili.inv_line_item_id ilif.inv_line_item_id
  and
• i.invoice_id ili.invoice_id and
• ili.line_item_id li.line_item_id and
• li.line_item_id lics.line_item_id and
• lics.location_id l.location_id and
• li.bib_id bt.bib_id and
• i.invoice_status ist.invoice_status and
• lics.line_item_status lis.line_item_status

30
Examples 2 (continued)

• order by
• bt.title

31
Example 3

• select
• from
• po_status

32
Example 4

• select
• fund_name, fund_id
• from
• fund
• where
• ledger_id 5

33
Example 5

• select
• decode(p.po_status,0,'Pending',
• 1,'Approved/Sent',
• 3,'Received Partial',
• 4,'Received Complete',
• 5,'Complete',
• 6,'Cancelled',
• 'NOT FOUND'),
• li.unit_price,
• li.line_price,
• li.quantity,
• lif.amount,
• p.po_number,
• l.location_name,
• to_char(p.po_approve_date,'yyyy-mm-dd'),
• lis.line_item_status_desc,
• bt.title

34
Example 5 (continued)

• from
• line_item_funds lif,
• fund f,
• line_item_copy_status lics,
• line_item li,
• location l,
• bib_text bt,
• purchase_order p,
• line_item_status lis

35
Example 5 (continued)

• where
• f.fund_id 30 and
• f.ledger_id 2 and
• lif.ledger_id f.ledger_id and
• lif.fund_id f.fund_id and
• lif.copy_id lics.copy_id and
• lics.line_item_id li.line_item_id and
• lics.location_id l.location_id and
• lics.line_item_status lis.line_item_status
  and
• li.bib_id bt.bib_id and
• li.po_id p.po_id and
• (p.po_status lt 3 or
• p.po_status 6)
• order by
• bt.title

36
Future upgrades

• Ability to Update and Delete queries
• Access to Bib Blob
• Prompt for input value or pick value from list
• Having Clause
• Patron security
• Interface to PL/SQL