Download - 1 Data Structures SQL.pdf
-
8/9/2019 1 Data Structures SQL.pdf
1/15
Page 1-1 Understanding eBusiness Suite Data Structures SQL
SQL for End-Users
Jeannie Dobney
Agenda:
Background to using SQL for Applications users
How the underlying data structures relate to what you see on the screen with Oracle
Applications
Basic relational database concepts
Introducing the SQL language
Basic SELECT syntax
Using Logical operators
Single Row functions
Group Functions
Joining multiple tables
Sub-Queries
Brief overview of DML (Insert, Update and Delete)
Commit statements
Brief overview of DDL (Create, Alter, Drop and Truncate)
Brief overview of DCL (Grant and Revoke, System and object privileges)
Oracle’s Data Dictionary
Reports written in SQL*Plus
Page 1-2 Understanding eBusiness Suite Data Structures SQL
Why Learn SQL?
• Oracle Applications stores data inan Oracle relational database(RDBMS).
• SQL is the language used tointeract with an Oracle database.
• Super users are often asked torun SQL commands by OracleSupport, or use them to solveproblems.
-
8/9/2019 1 Data Structures SQL.pdf
2/15
-
8/9/2019 1 Data Structures SQL.pdf
3/15
Page 1-5 Understanding eBusiness Suite Data Structures SQL
Tables and
Blocks
Journals’ table
Journal Lines
Name
Category
Source
Status
Header Block / Region
Lines Block / Region
Line # Dr Cr Amt Desc.
Journals Window
The underlying tables are represented on the user’s screen using a tool called Forms, which
both paints the screens and manipulates the data.
Each table is represented by a ‘block’ within a window. A block is a logical group of fields.It may present the data as single records (as in the journal header block), or as a multi-record
block (as in the journal lines block).
The term “block” is used in some of the diagnostic tools, so it may be helpful to understand
it.
The next slide displays the window discussed above as an example.
Page 1-6 Understanding eBusiness Suite Data Structures SQL
Tables and Blocks
This is the actual Applications window represented diagrammatically in the previous slide.
It will now be clear that in this window the user is looking at data from two tables.
A Forms developer has written code which ‘joins’ these tables so that when the user retrieves
a particular journal, they see the lines related to THAT journal, rather than some other
journal.
It also explains why when I put my cursor in one of journal lines and invoke the Find
Window, I get a different system response than if I have my cursor in one of the fields in the
journal header region.
To anthropomorphise this a little:
If my cursor is in the journal header region and I invoke the find window, the system thinks:
aha, she wants to search for a particular journal.
If my cursor is in one of the lines, the system thinks: she’s looking for a particular line in this
journal.
-
8/9/2019 1 Data Structures SQL.pdf
4/15
Page 1-7 Understanding eBusiness Suite Data Structures SQL
Which Table is BeingAccessed?
Within Oracle Applications there are a number of helpful links to the underlying technology
components.
You will find these links in the Help menu. Help ~ Record History tells you not only which user created the record (the so-called Row
Who data written transparently with each record) but also the name of the table from which
the data is drawn.
Help ~ Diagnostics ~ Examine provides access to values hidden from the user. These include
foreign keys and codes represented by different display values.
Help ~ About Oracle Applications includes version and path information.
You must have a password to use some of these tools – your DBA can provide this to you.
Page 1-8 Understanding eBusiness Suite Data Structures SQL
Where is it in the Schema of things?
GL AP PO
AR PA
APPS
Data to and from users
Within the database, there are product ‘schemas’ (database user areas).
For every application which is installed, you will have a schema which stores its data, for
example GL, AP, PA, etc
The GL schema contains all the tables, views and other database objects belonging to the
General Ledger Application.
The Apps Schema has access to the complete Oracle Applications data model. It consists
mainly of PL/SQL logic rather than storage (in packaged procedures and views). Much of
this logic is there to enable Oracle’s Multi-Org technology.
Since the advent of Multi-Org technology all users write to the various tables in GL, AP etc
through procedures stored in the APPS schema, which ensures that new records are ‘tagged’
with the organisation ID linked to that user (usually through a profile option at Responsibility
level). In the same way when users retrieve data, the APPS schema ‘filters’ the data so that
users only see the data relating to their organisation.
So the APPS schema is a sort of gate-keeper to the other schemas.
In this way, Multi-Org technology allows multiple organisations to secure data within a
single database.
It is supported by an ORG_ID column on tables and an MO:Operating Unit profile option for
each Responsibility (which creates an implicit ‘where clause’ linking the user’s organisation
only with records which have the appropriate Org_ID value).
-
8/9/2019 1 Data Structures SQL.pdf
5/15
Page 1-9 Understanding eBusiness Suite Data Structures SQL
• SQL is the language used to add, modifyand remove data from the database.
• SQL is an ‘English-like’ programminglanguage i.e. relatively easy to read (forexample in Applications’ log files).
• It consists of a number of clausesstructured around some key verbs: – Select - to read or retrieve data
– Insert - to create a new record
– Update - to modify an existing record
– Delete - to remove a record from a table.
SQL is a standardised language used by all relational databases.
The SQL acronym stands for Structured Query Language and is pronounced sequel .
Oracle’s version complies with the international (ANSI) standard and is only marginally
different to that of other vendors (like Microsoft’s SQLServer or IBM’s DB2).
SQL commands are not case sensitive.
As a general principle, users should never attempt to update the database directly using SQL.
Updating the database directly (rather than through the Applications) can seriously damage
the integrity of your data and invalidate your support contract.
SQL commands are typically issued from a utility like Oracle’s SQL Plus tool or Quest’s
TOAD.
Both these tools require client-side set-up (i.e. installation on you PC) before you can usethem.
For both tools you will also need a database user name and password from your DBA. You
cannot use your Applications login here.
Page 1-10 Understanding eBusiness Suite Data Structures SQL
Introducing the Sample Data
Emp table
Primary Key:EmpNo
Dept table
Foreign Key:DeptNo
Primary Key:DeptNo
This set up script for this data is shipped with every Oracle database.
It might seem simplistic, however it gives you an easy environment in which to learn the
skills which you can subsequently use with large volumes of data in the Applicationsenvironment.
The sample data is reproduced on the final page of this document.
-
8/9/2019 1 Data Structures SQL.pdf
6/15
Page 1-11 Understanding eBusiness Suite Data Structures SQL
Simple SELECT Statements
• Select * from emp;
• Select *from emp;
• Select ename from emp;
• Select ename, sal from emp;
• Select dname from dept /
• Select dname Department from dept;
The verb SELECT allows us to retrieve or ‘read’ data from a table.
Two clauses are introduced here:
The select list which specifies the columns to be displayed.
The from clause which specifies which table the data is in.
It makes no difference whether you type your commands on one or more lines; by convention
each clause is started on a new line.
An asterisk (pronounced “star”) in the select list means all columns.
If you are selecting more than one column by name, separate them with commas (except for
the last column).
A word following a column name without an intervening comma is interpreted as a column
alias and will be used as the heading for that column in the data output i.e.
SELECT col_name1, col_name2 changed_name, col_name3, etc.
If you are accessing more than one table, commas should also separate the table list. You
may also alias table names, but having done so, every table reference in the statement must
use the alias.
To execute the command you use either a semi-colon on the final command line or a forward
slash on the following line.
Page 1-12 Understanding eBusiness Suite Data Structures SQL
Two more SELECT clauses
• Select * from empwhere job = ‘CLERK’;
• Select * from empwhere sal < 1500;
• Select * from emporder by ename;
• Select * from emporder by sal desc;• Select deptno, ename, sal
from emporder by 1, 3;
Two more clauses are introduced here:
The where clause which restricts which rows are retrieved and displayed.The order by clause which sorts the display of data.
To use the where clause to restrict rows, you must use a logical condition (more about logical
operators in the next slide).
Dates and alphanumeric literals (so-called character data) must be enclosed in single quotes
and are case sensitive.
Default date format is ‘DD-MON-YY’
A select statement without a ‘where clause’ will retrieve all of the records in the table.
By default the order by clause sorts the output in an ascending sequence, you can change this
by using the DESC qualifier as shown.
The numbers in the final statement in the above slide are a short-hand way of referring to the
columns in the select list.
You can sort by columns which you do not display (i.e. are not in your select list).
-
8/9/2019 1 Data Structures SQL.pdf
7/15
Page 1-13 Understanding eBusiness Suite Data Structures SQL
Logical Operators• >, =,
-
8/9/2019 1 Data Structures SQL.pdf
8/15
Page 1-15 Understanding eBusiness Suite Data Structures SQL
Other Tricks
• Arithmetic e.g. Date_column + 7(using + - * / )
• Concatenation and the use of literalse.g. ‘Hello ’||emp_name
• Logical combinations: AND, OR
(use parenthesis to ensure executionorder)
• Select distinct job from emp;
Other ‘tricks’ which can be used in Select statements include the following:
Simple arithmetic e.g. Date_column + 7 (i.e. a week after the recorded date)
Multiplication and division have precedence over addition and subtraction.Use parenthesis to enforce execution order.
Nulls will cause very strange results – we’ll see how to handle them in a moment.
Concatenation e.g. ‘Number ’||ID_column (i.e. this adds the word ‘number’ to every ID
value retrieved)
The logical operators AND and OR may be used to combine operators, e.g
where period_name = ‘JAN-01’ or period_name = ‘FEB-01’
Remember to use parenthesis to control the order in which such statements are evaluated –
otherwise AND takes precedence over OR
The keyword DISTINCT removes duplicate values
for example Select distinct period_name from GL_JE_Batches lists each period in which Journal batches exist but regardless of how many batches exist for
that period, the period is displayed only once.
Page 1-16 Understanding eBusiness Suite Data Structures SQL
Introducing Functions
• NVL (col, alternative)
– substitute value to replace null values
• Example:Select ename, sal, sal*12+com Packagefrom emp;
• Now try:Select ename, sal, sal*12+nvl(com,0) pkg
from emp;
Functions are mini-programs which can be applied to column values to manipulate them.
The NVL function may be one of the most important functions for Applications users.
The syntax is NVL(col, alt) where col is the column or value to be evaluated and alt is the
value to substitute in if a null is found in col.
A column value which has not been populated for a particular record is said to contain a null
value.
Null values produce unexpected results when they are involved in arithmetic.
For example:
1,000 + null = null.
NVL allows null values to be replaced in ways that produce sensible results.
For example:
1,000 + NVL(null, 0) = 1,000
NVL only replaces nulls, it will not affect real values.
-
8/9/2019 1 Data Structures SQL.pdf
9/15
Page 1-17 Understanding eBusiness Suite Data Structures SQL
Some Other Examples of Functions
• TO_CHAR (col) – forces the value to be reador stored as a character string
• ROUND(number, 2) – round the value to 2decimal places
• UPPER (col) – forces the value to upper case
• SUBSTR (col, m,n) – string operator allowinga portion of a character string to be selectedor removed (from m char to nth char).
Functions are classified according to the type of data which they are used with: Number,
Date, Character or Conversion functions.
TO_CHAR is an example of a conversion function. Similar functions exist for the other data-types as well (To_Number, To_Date). They can also be used for some kinds of formatting.
ROUND is an example of a function which manipulates numbers.
UPPER is an example of a function which manipulates character strings.
SUBSTR is am example of a string operator (also called a character function).
These functions return values for each row passed to them, so they are called single row
functions; later we will meet functions which produce one result for a group of rows.
Functions can also be ‘nested’ – in which case the innermost function executes first … out tothe outermost function.
Page 1-18 Understanding eBusiness Suite Data Structures SQL
Some More Character Functions
• InitCap (col) - converts text to title case
• Lower (col) - converts text to lower case
• Length (col) - returns the length of a string
• Lpad (col, length, ‘char’) - makes a string aspecified length by adding characters
• Rtrim (col, ‘chars’) - trims unwanted charactersfrom the right end of a string
• Instr (col, char, start, num) - searches through astring for a set of characters
-
8/9/2019 1 Data Structures SQL.pdf
10/15
Page 1-19 Understanding eBusiness Suite Data Structures SQL
Some More Number Functions
• Ceil (value) – returns the smallest integerlarger than or equal to the value
• Floor (value) – returns the largest integer
larger than or equal to the value
• Power (value, exponent) – raises the valueto the specified exponent
• Sign (value) – returns 1 if value is positive,-1 if the value is negative and 0 if zero
N.B. the Sign function can be useful in determining which values are larger.
For example Sign (Col_A – Col_B) will return +1 if Col_B is less than Col_A
Page 1-20 Understanding eBusiness Suite Data Structures SQL
Some Date Functions
• Add_Months ( Date_value, Num) – adds thenumber of months to the original date
• Next_day (Date_value, ‘Day’) – returns thenext e.g. ‘FRIDAY” after the original date
• Months_Between (Date2, Date1) – returns
the number of months between the dates• Last_day (date-value) – returns last day of
the month in which the original date was in
• Trunc (date) – removes time portion of date
Note that logical operations including dates can be complicated by the time portion which is
invisible by default. This makes the TRUNC or ROUND functions valuable because you can
effectively remove the time component from a logical operation.
-
8/9/2019 1 Data Structures SQL.pdf
11/15
Page 1-21 Understanding eBusiness Suite Data Structures SQL
Date Conversion Example
Statement:
Select to_char (sysdate,'fmDay ddth "of" Month YYYY') today
from dual
Output:TODAY
--------------------------------Monday 16th of April 2007
This example shows the flexibility and power of Oracle’s functions.
Sysdate is the system date stored by the system.These sorts of values are called pseudo-columns, because you can manipulate them like data
in columns.
Dual is a ‘dummy’ table which allows operations like this one i.e. where the structure of the
select statement requires a ‘from clause’ even though the required result is not found in a
table.
In this case the To_Char function takes 2 ‘arguments’ or inputs: the date it is to manipulate
and the ‘mask’ which tells the system how to display the information. The mask is made up
of ‘tokens’. You can see a complete list of these tokens and all the arguments accepted by
Oracle functions in the SQL Language reference manuals supplied by Oracle as part of theirdatabase server documentation (or available in softcopy form Oracle’s OTN Technet).
Page 1-22 Understanding eBusiness Suite Data Structures SQL
The Power of Decode
DECODE(col, if_value, then_value, …else_value)
Example:
select distinct decode (actual_flag,
'A', 'Actual','B', 'Budget','Encumbrances')
from GL_je_headers;
DECODE is a powerful if-then-else replacement operator.
The example above shows the power of the decode function.In the example the single letter ‘flag’ is displayed using a whole word which would be more
meaningful to the user.
Clever developers can use the decode functions in some very interesting ways.
The syntax is: DECODE (expr, search, result [, search, result...] [,default])
Where expr is the expression (column or value) being evaluated
and search is the value to find
and result is the value to replace it with
(you may have as many search and result pairs as you require
and default is the value to output if the expression contains none of the specified search values
-
8/9/2019 1 Data Structures SQL.pdf
12/15
-
8/9/2019 1 Data Structures SQL.pdf
13/15
Page 1-25 Understanding eBusiness Suite Data Structures SQL
JoinExample
AP_Invoices_All
AP_Invoices_Distributions_All
GL_Code_Combinations
PO_Vendors Supplier Name
Account Code
CC_ID
Vendor_ID
Actual Statement:select vendor_name, c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5
account_code
from PO_vendors V,
AP_Invoices_All I,
AP_Invoice_Distributions_All D,
GL_Code_Combinations C
where i.Invoice_ID = d.Invoice_ID
and i.vendor_id = v.vendor_id
and d.dist_code_combination_id = c.code_combination_id
Output:VENDOR_NAME ACCOUNT_CODE
------------------------- -------------------------Building Management Inc. 01.420.7410.0000.000.
Page 1-26 Understanding eBusiness Suite Data Structures SQL
Accounts Payable Data Structures
2 primary clusters of tables:
– Invoices:
• AP_INVOICES_ALL
• AP_INVOICE_DISTRIBUTIONS_ALL
• AP_PAYMENT_SCHEDULES_ALL
– Payments:• AP_CHECKS_ALL
• AP_INVOICE_PAYMENTS_ALL
• AP_PAYMENT_HISTORY_ALL
Developers unfamiliar with a particular module will need to invest some time and effort to
become familiar with its tables.
In this slide, the major tables supporting the Accounts Payable functionality are displayed,however, this display does make it look somewhat simpler than it in fact is…
For example, in the AP_INVOICES_ALL table the Supplier name value which the user sees
on the Applications screen, has to be retrieved by the developer from the PO_VENDORS
table by joining on the Vendor_ID (code). Similarly the Invoice Distributions table does not
contain the account code which the user sees – it only contains an ID (foreign key) pointing
to where the account code is really stored.
This account code ID is called the CC_ID or Code Combination ID and it must be joined to
the GL_CODE_COMBINATIONS table in order to retrieve the code which the user is used
to seeing.
The tables are all quite large and the data will not be meaningful to the developers in the
same way which it is to the business user – thus the ideal development methodology for
modifications involves close collaboration between a business user and a developer.
-
8/9/2019 1 Data Structures SQL.pdf
14/15
Page 1-27 Understanding eBusiness Suite Data Structures SQL
Sub-Queries
• One SQL Select statement may benested within another:select invoice_num, invoice_date from AP_invoices_allwhere vendor_id =
(select vendor_id from PO_vendors where vendor_name like 'Building%')
The nested query (sub-query) executesfirst.
Sub-Queries are a very powerful tool for solving more difficult data retrieval problems.
If you are reviewing SQL scripts written by applications developers you are likely to see such
statements. They are easier to read if you remember that they are executed sequentially: the
most deeply nested statement executes first and then passes it’s results to the next most
deeply nested statement etc.)
If need be, use a highlighter to identify each sub-query and read them as if they were
executing sequentially from the most deeply nested statement, remembering that the nested
statement passes it’s results to the statement in which it is nested.
Page 1-28 Understanding eBusiness Suite Data Structures SQL
Other Useful Tips
• Pseudo-columns like RowNum can bevery helpful:
Select * from Huge_Table
where RowNum < 100; limits the data retrieved to 99 rows
Note that RowNum cannot be used meaningfully in conjunction with an order by clause as it
limits the rows retrieved before they are sorted.
-
8/9/2019 1 Data Structures SQL.pdf
15/15
Page 1-29 Understanding eBusiness Suite Data Structures SQL
The Data Dictionary
• USER_views = your own schema,ALL_views = everything you have access toDBA_views = everything
• Desc DICT – a good starting point
• Example:
Select table_name from all_tables where table_name like ‘%PROJECT%’;
Oracle maintains it’s own list of which objects exist in the database. This is called the data
dictionary (dict).
You can use standard SQL commands to find out information about various objects.
There are three classes of views (pseudo tables) which you can query, they have the prefixes
USER_xxx, ALL_xxx and DBA_xxx
For most of us the ALL_xxx views will probably be the most useful as they contain
information about all the objects which you can access.
Here are some examples:
Select text from ALL_VIEWS
Where view_name = ‘AP_INVOICES’;
Select table_name from ALL_TABLES
Where table_name like ‘%USER%’;
The Describe command (also Desc) will list the columns / structure of any specified table e.g.
Desc GL_JE_Headers
This command does not need a semi-colon to execute it.
Page 1-30 Understanding eBusiness Suite Data Structures SQL
Sample Data
Emp Table
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 13-JUN-83 800 207499 ALLEN SALESMAN 7698 15-AUG-83 1600 300 30
7521 WARD SALESMAN 7698 26-MAR-84 1250 500 30
7566 JONES MANAGER 7839 31-OCT-83 2975 20
7654 MARTIN SALESMAN 7698 05-DEC-83 1250 1400 30
7698 BLAKE MANAGER 7839 11-JUN-84 2850 30
7782 CLARK MANAGER 7839 14-MAY-84 2450 10
7788 SCOTT ANALYST 7566 05-MAR-84 3000 20
7839 KING PRESIDENT 09-JUL-84 5000 10
7844 TURNER SALESMAN 7698 04-JUN-84 1500 0 30
7876 ADAMS CLERK 7788 04-JUN-84 1100 20
7900 JAMES CLERK 7698 23-JUL-84 950 307902 FORD ANALYST 7566 05-DEC-83 3000 20
7934 MILLER CLERK 7782 21-NOV-83 1300 10
Dept Table
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
If you are looking for a reference book, we recommend Oracle N: the Complete Reference by
Kevin Loney and George Koch (where N is the current version i.e. 8i, 9i, 10g etc)
You can also download Oracle documentation without charge from Oracle’s OTN site(http://otn.oracle.com.index.html) – although you may find it a little daunting to begin with.
There are also excellent resources available via Google
The author can be contacted via email at [email protected]