1 / 35

Query Developer User Group

Query Developer User Group. March 15, 2006. Tips and Techniques. Cutting and pasting SQL between QM, Excel, and Access Filtering the list of tables in Excel Saving a query in Excel SQL Pass-through in Access Using parameters Excel Access Changing the join type Examples of Advanced SQL.

ephraim
Download Presentation

Query Developer User Group

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Query Developer User Group March 15, 2006

  2. Tips and Techniques • Cutting and pasting SQL between QM, Excel, and Access • Filtering the list of tables in Excel • Saving a query in Excel • SQL Pass-through in Access • Using parameters • Excel • Access • Changing the join type • Examples of Advanced SQL

  3. Cutting and Pasting SQL • Going from QM to Excel • The query has to be in SQL, Prompted will not work • Open the query in Edit or Display mode • Can also use option 5 from the University Provided Reports menu • Select and copy the SQL using standard techniques • Open a new query in Excel or MS Query • Open the SQL window and paste the SQL • Any parameters will have to be adjusted to meet MS Query requirements (covered in the next section)

  4. Cutting and Pasting SQL • Going from QM to Access • The query has to be in SQL, Prompted will not work • Open the query in Edit or Display mode • Can also use option 5 from the University Provided Reports menu • Select and copy the SQL using standard techniques • Open a new query in Access using Design View • Open the SQL window and paste the SQL • Table names have to be adjusted for Access (change DATACOLL.view to DATACOLL_view) • Any parameters will have to be adjusted to meet Access requirements (covered in the next section)

  5. Cutting and Pasting SQL • Going from Excel to Access • Open the SQL window in MS Query • Copy to the clipboard • Open the Access query SQL window • Paste from the clipboard • Table names have to be adjusted for Access (change DATACOLL.view to DATACOLL_view) • Enter the parameter prompt string

  6. Filtering the list of tables in Excel • In the Add Tables dialog window, the list can be filtered by doing the following • Select DATACOLL from the Schema pull down • Click Options button and make sure that only Views is selected

  7. Saving a Query in Excel • Saved with the sheet in the workbook • Saved externally • Default location • C:\Document and Settings\userid\Application Data\Microsoft\Queries • The Application Data folder is hidden • A location to share with others • This can be any you want; just like any other Save As dialog

  8. Using a Saved Query in Excel • In the Choose Data Source window, there is an “Options” button that lets you choose the location of your data sources • By default, it includes your ODBC data sources and queries stored in the default directory. • Click this button to add a directory. This directory will be used to populate the Queries tab. This directory could be on a shared drive or on your local hard drive. • The Queries tab facilitates reuse of queries

  9. SQL Pass-through in Access • Pass-through is turned on by clicking Query -> SQL Specific -> Pass-through • You need to turn this on when you have more advanced SQL than what Access can handle. • You can’t use parameters • You have to use standard SQL naming (DATACOLL.tablename)

  10. Using Parameters in Excel, Access • In the criteria window, enter the prompt string enclosed in square brackets [ ] • In Access, if using more than 1 parameter, use the Query Parameter dialog to set the order of the prompts (Query -> Parameters) • In Access, you can’t use parameters in a pass-through query

  11. Changing the Join Type • Excel • Access • Click the join line between the 2 matching columns and select either a left outer join or a right outer join • Notice that the line between the 2 columns changes

  12. Advanced SQL Examples • Fetch first n rows only • Left Outer Join • Left Exception Join • Full Outer Join • Case Expression • Excluding zeroes from an average calculation

  13. FETCH FIRST n ROWS ONLY • Sets the maximum number of rows to be returned • Follows the ORDER BY clause if it is present • If the ORDER BY clause is present, then the FETCH FIRST operation is performed on the sorted data

  14. FETCH FIRST n ROWS ONLY SELECT MAJ1_CURR, COUNT(*) FROM DATACOLL.STUDENTS WHERE CURR_STDT = 'Y' GROUP BY MAJ1_CURR ORDER BY 2 DESC,1 FETCH FIRST 10 ROWS ONLY

  15. Left Outer Join • A Left Outer Join returns values for all of the rows from the first table (the table on the left) and the values from the second table for the rows that match. Any rows that do not have a match in the second table will return the null value for all columns from the second table. FROM T1 LEFT OUTER JOIN T2 ON T1.COLA = T2.COL1

  16. Left Outer Join • This can be selected using the techniques on slide 8 • When would you use this?

  17. Left Outer Join Example select schl_code, schl_name, sum(stdt) as count from (select schl_code, schl_name, case when soc_sec is not null then 1 else 0 end as stdt from datacoll.institutn i left outer join (select soc_sec, hs_code from datacoll.fa05students where curr_stdt = 'Y' and stu_class in ('FR','SO','JR','SR') and stu_type = 'FT') s on schl_code = hs_code) as high_school group by schl_code, schl_name order by count desc

  18. Left Exception Join • A Left Exception Join returns only the rows from the left table that do not have a match in the right table. Columns in the result table that come from the right table have the null value. FROM T1 LEFT EXCEPTION JOIN T2 ON T1.COLA = T2.COL1

  19. Left Exception Join • The previous syntax will not work in Access • Has to be manually entered in the SQL window in Excel and Access • Access also will require pass-through to be set • There is a work around for Excel and Access if you don’t want to use the SQL window • Make the join a left outer join • Add the matching column from the right table to the criteria and check for “Is Null”

  20. Left Exception Join • When would you use this?

  21. Left Exception Join SELECT NAME, STU_CLASS, HS_CODE FROM DATACOLL.STUDENTS LEFT EXCEPTION JOIN DATACOLL.INSTITUTN ON HS_CODE = SCHL_CODE WHERE CURR_STDT = 'Y' AND STU_CLASS IN ('FR','SO','JR','SR') AND HS_CODE <> ' '

  22. Full Outer Join • The Full Outer Joinincludes the inner join (the rows that match) and the rows from both the left and right tables that do not match. • The Full Outer Join is not included in this version of SQL, but it can be simulated. • You simulate a Full Outer Join by using a Left Outer Join union’ed with a Right Exception Join

  23. Full Outer Join Example SELECT S.SOC_SEC, SUBSTR(GRE_TEST_CCYYMM,1,4)||'/'|| SUBSTR(GRE_TEST_CCYYMM,5,2), G.SOC_SEC FROM DATACOLL.STDT S LEFT OUTER JOIN DATACOLL.GRETST G ON S.SOC_SEC = G.SOC_SEC WHERE STU_CLASS = 'GM' UNION SELECT S.SOC_SEC, SUBSTR(GRE_TEST_CCYYMM,1,4)||'/'|| SUBSTR(GRE_TEST_CCYYMM,5,2), G.SOC_SEC FROM DATACOLL.STDT S RIGHT EXCEPTION JOIN DATACOLL.GRETST G ON S.SOC_SEC = G.SOC_SEC

  24. Case Expressions • The CASE expression can be used to easily manipulate the data in a column • It can be used to change codes into meaningful words • It can be used to provide special sort order • In Excel, has to be in SQL window • In Access, has to be in SQL window with pass-through turned on.

  25. Case Expressions SELECT A.NAME_REVERSED, CASE SUBSTR(C.HALL,1,4) WHEN 'BLSH' THEN '%BLAIR-SHANNON HOUSE%' WHEN 'FRED' THEN '%FREUDENBERGER HOUSE%' WHEN 'WOOD' THEN '%WOODS HOUSE%' WHEN 'HAMH' THEN '%HAMMONS HOUSE%' WHEN 'NEWH' THEN '%NEW HALL%' WHEN 'SCHO' THEN '%SCHOLARS HOUSE%' WHEN 'EAPT' THEN '%ELM APARTMENTS%' WHEN 'SUNV' THEN 'SUNVILLA TOWER%' WHEN 'KENT' THEN '%KENTWOOD HALL%' WHEN 'DAPT' THEN '%DOGWOOD APARTMENTS%' WHEN 'SAPT' THEN '%SUNVILLA APARTMENTS%' WHEN 'KAPT' THEN '%KENTWOOD SOUTH APTS%' WHEN 'WELL' THEN '%WELLS HOUSE%' END AS HALL, A.PERMNT_STREET, A.PERMNT_CITY, A.PERMNT_STATE, A.PERMNT_ZIP

  26. Case Expressions SELECT NAME, STU_CLASS, CASE(STU_CLASS) WHEN('FR') THEN 1 WHEN('SO') THEN 2 WHEN('JR') THEN 3 WHEN('SR') THEN 4 ELSE 999 END AS CLASS FROM DATACOLL.STUDENTS WHERE SEM_PNTR = ‘SP' AND YEAR_PNTR = '2006' AND CURR_STDT = 'Y' AND STU_CLASS IN ('FR','SO','JR','SR') ORDER BY CLASS, NAME

  27. Case Expressions SELECT DISTINCT A.SOC_SEC, A.NAME, A.MAJ1_CURR, A.MAJ2_CURR, A.STU_CLASS, B.COURSE_CODE, B.COURSE_NO, B.SECTION, B.SEMESTER, CASE(B.SEMESTER) WHEN('FA') THEN 'Fall' WHEN('SP') THEN 'Spring' WHEN('SU') THEN 'Summer' ELSE ' ' END FROM DATACOLL.STDT A,DATACOLL.CLASSES B

  28. Where can a CASE expression be used? • Anywhere an expression is allowed in a SQL statement • SELECT clause • WHERE clause • HAVING clause • Built-in Function • IN (list) • ORDER BY clause

  29. Can I use a subselect inside a CASE expression? SELECT NAME, STU_CLASS, COMB_UG_GPA, CASE WHEN (SELECT SUM(CREDIT_HOURS) FROM DATACOLL.CLASSES C WHERE A.SOC_SEC = C.SOC_SEC AND YEAR = '2006' AND SEMESTER = ‘SP') >= 12 THEN 'FULL-TIME' WHEN (SELECT SUM(CREDIT_HOURS) FROM DATACOLL.CLASSES C WHERE A.SOC_SEC = C.SOC_SEC AND YEAR = '2006' AND SEMESTER = ‘SP') < 12 THEN 'PART-TIME' ELSE 'DOES NOT COUNT' END AS STATUS FROM DATACOLL.STUDENTS A WHERE SEM_PNTR = ‘SP' AND YEAR_PNTR = '2006’ AND CURR_STDT = 'Y' AND STU_CLASS IN ('FR','SO','JR','SR')

  30. A More Efficient Query SELECT NAME, STU_CLASS, COMB_UG_GPA, CASE WHEN CREDIT_HOURS >= 12 THEN 'FULL-TIME' WHEN CREDIT_HOURS < 12 THEN 'PART-TIME' ELSE 'DOES NOT COUNT' END AS STATUS FROM DATACOLL.STUDENTS A, (SELECT SUM(CREDIT_HOURS) AS CREDIT_HOURS FROM DATACOLL.CLASSES WHERE SEMESTER = 'SP' AND YEAR = '2006') AS FULLPART WHERE SEM_PNTR = 'SP' AND YEAR_PNTR = '2006' AND CURR_STDT = 'Y' AND STU_CLASS IN ('FR','SO','JR','SR')

  31. Using CASE in an ORDER BY SELECT NAME_REVERSED, PERMNT_STATE FROM DATACOLL.ADDRESSES WHERE SOC_SEC BETWEEN '496000000' AND '499999999' ORDER BY CASE PERMNT_STATE WHEN ‘IL' THEN 0 WHEN ' ' THEN 999 ELSE 1 END, PERMNT_STATE

  32. Using Case in an ORDER BY SELECT NAME, MAJ1_CURR, STU_CLASS, COMB_UG_GPA FROM DATACOLL.STDT WHERE MAJ1_DEPT = 'CI' AND CURR_STDT = 'Y' AND STU_CLASS IN ('FR','SO','JR','SR') ORDER BY CASE STU_CLASS WHEN 'FR' THEN 1 WHEN 'SO' THEN 2 WHEN 'JR' THEN 3 WHEN 'SR' THEN 4 END, COMB_UG_GPA

  33. Using an IN list in a CASE expression SELECT NAME, STU_CLASS, CASE(STU_CLASS) WHEN STU_CLASS IN ('FR‘, ‘SO’) THEN ‘LOWER DIVISION’ WHEN STU_CLASS IN ('JR‘, ‘SR’) THEN ‘UPPER DIVISION’ ELSE 999 END AS CLASS FROM DATACOLL.STUDENTS WHERE CURR_STDT = 'Y' AND STU_CLASS IN ('FR','SO','JR','SR') ORDER BY CLASS, NAME

  34. Eliminating zeroes from a calculation SELECT NAME, SOC_SEC, HNRS_ACT, STU_CLASS, SMS_UG_CUM_GPA, ACT_COMP_STD, FIN_AID, SEX FROM DATACOLL.STUDENTS WHERE HNRS_ACT = 'C' AND REG_SPR = &YEAR AND (STU_CLASS ='FR' OR STU_CLASS ='SO' OR STU_CLASS ='JR' OR STU_CLASS = 'SR') AND NOT NAME = 'AAA FAKE D' ORDER BY 5, 1

  35. Eliminating zeroes from a calculation SELECT NAME, SOC_SEC, HNRS_ACT, STU_CLASS, NULLIF(SMS_UG_CUM_GPA,0), NULLIF(ACT_COMP_STD,0), FIN_AID, SEX FROM DATACOLL.STUDENTS WHERE HNRS_ACT = 'C' AND REG_SPR = &YEAR AND STU_CLASS IN ('FR','SO','JR','SR') AND NOT NAME = 'AAA FAKE D' ORDER BY 5, 1

More Related