Now we enter the world of queries. A query is what you do with a SELECT command. Queries can be simple or complex. In a simple query, you might ask for information from one table ”for example, SELECT TABLE_NAME from ALL_TABLES . In a complex query, the SELECT statement is qualified by condi-tions such as WHERE or by the use of union, join, or other such construct. Let's investigate these a little further. In a join , columns selected from two tables on the basis of a key match ”primary to foreign ”are connected: SELECT L_NAME, TEST_ID from STUDENTS, TEST_HISTORY WHERE STUDENTS.STUDENT_ID = TEST_HISTORY.STUDENT_ID AND STUDENTS.L_NAME LIKE '%Klie%'
This join connects the STUDENT_ID columns in both tables:
You can join unrelated columns in what is called a non-key join . Suppose that you wanted to see if any author had the same name as any student. You could write a SELECT statement matching the two columns. (Non-key joins are not common, but you should know that they are possible.) Outer joins can be left-handed or right-handed. Are you confused ? Well, here's how an outer join works. We would use an outer join if we wanted all rows in a column when we joined tables, even those without a match on both tables. For example, some students in the STUDENTS table will not yet have taken an exam. Suppose that we want to list all students, along with the exams they have taken. If we do a normal join, WHERE STUDENTS.STUDENT_ID = TEST_HISTORY.STUDENT_ID we will get only the students that have taken exams. To get all students, we have to write our query like this (this is a left outer join): WHERE STUDENTS.STUDENT_ID(+) = TEST_HISTORY.STUDENT_ID. We would use a right outer join to do something like finding all the tests, in- cluding those that have not been taken: WHERE TEST_HISTORY.TEST_ID = TEST_ID.TEST_ID(+) Now as long as we're talking about joins, what is a union? A union is a way of combining similar data from several tables. If you wanted to send a mailing to everyone in your STUDENTS table who had also taken exams, or only to those who had not ever taken an exam, or to everyone in both tables, but you did not want to send two letters to them, a union would be the tool for you. To get everyone listed in both tables, use the simple UNION statement: SELECT STUDENTS.STUDENT_ID from STUDENTS UNION SELECT TEST_HISTORY.STUDENT_ID; And here's the list you will get:
To get students who are in the STUDENTS table and not in the TEST_HISTORY table, use the MINUS command: SELECT STUDENT_ID from STUDENTS MINUS SELECT STUDENT_ID from TEST_HISTORY; Here are the results:
And you guessed it, to get only names in both tables, use INTERSECT : SELECT STUDENT_ID from STUDENTS INTERSECT SELECT STUDENT_ID from TEST_HISTORY; Here's the resulting report:
Putting the Date in the Spool File Name You've probably noticed that the preceding examples give the spool file a simple name. Sometimes you want to create the spool file with the current date in its name, for obvious reasons. One way to do this is to add another column, then build your spool file name by concatenating the date and the name: col filename new_value XSPOOLNAME set term off select to_char(sysdate, 'mondd') 'PERSONS.txt' XSPOOLNAME from dual; spool c:\&XSPOOLNAME select * from person_table; spool off;
OK, how fancy can we get with the spool file name and dates? Here's an example of another script that has not only the date, but also the time and the type of application (GL). This script is also on the book's CD: REM REM Here's a nifty little script to use the current date REM and time as an output file name REM REM There's actually quite a bit going on here. First, REM the SCAN ON command is used to REM make sure our setup will handle substitution variables REM Second, a variable is created - FILEDATE. REM Third, we load FILEDATE with the current date and REM time REM Now we set the SPOOL FILE to FILEDATE by using the REM substitution symbol '&' REM Then we just continue processing, and finally turn REM spooling off. REM REM Note that you can add other text to the spool file REM name, such as "GENERAL LEDGER" and REM so forth by just adding additional concatenations. I REM put the "GL" both at the beginning and at the REM end to show you that you can append additional text REM anywhere: REM REM SELECT REM 'GL'TO_CHAR(SYSDATE,'YYYYMMDDHH24MI')'GL''.TXT' REM filedate from dual; REM and the spool file name is: GL200208280952GL.TXT REM REM REM Step 1: Set "scan on" to make sure that your setup will REM use substitution variables SET SCAN ON REM Step 2: Create the variable COLUMN FILEDATE NEW_VALUE FILEDATE REM Step 3: Load the date and time SELECT 'GL'TO_CHAR(SYSDATE,'YYYYMMDDHH24MI')'GL.TXT' FILEDATE FROM DUAL; REM Step 4: Set the SPOOL FILE SPOOL C:\&FILEDATE; REM Step 5: Continue processing Sdate_takenELECT FNAME from STUDENTS; REM At the end, turn spooling off SPOOL OFF; |