General SQL Stuff

SQL Order of Operations

SELECT field1, field2, field3
FROM table
(JOIN TYPE) table on table1.field=table2.field
WHERE condition
GROUP BY any field not listed in an aggregate clause
ORDER BY fields in order

Miscellaneous Oracle Trickery

  • ps_customfields.getcf ('students',,'FIELDNAMEGOESHERE') will get you a custom field's value
  • When in doubt, look in the Gen table; check the CAT and VALUE and NAME fields
  • To convert a date to MM/DD/YYYY, use to_char (ex: to_char(DOB,'MM-DD-YYYY')
  • To "pivot" a field, like Test Scores:
    MAX(DECODE(, 'CST ELA', sts.numscore, NULL)) "ELA Score"
    (this will create one column called CST ELA)

Silly Strings

  • SUBSTR(first_name,1,1) gets the first letter of the first name
  • INSTR(first_name,"G") checks to see if the letter G exists in a first name
  • SUBSTR(lastfirst,',') will return you the position of a comma in a string
  • Using the combination of those two, you can do SUBSTR(lastfirst,1,(instr(lastfirst,',')-1)) to get the last name (all of the characters before the comma
  • Using the reverse logic, you can do SUBSTR(lastfirst,(instr(lastfirst,',')+1),LENGTH(lastfirst)) to get everything after the comma
  • To concatenate text, use the pipe character. To put the first and last name together in one field, go like this: first_name||' '||last_name
  • Log entries and other free-form text fields often have hard returns, which can wreak havoc on your exports. Be sure to use a REGEXP_REPLACE function to get rid of all the junk characters: to_char(replace(REGEXP_REPLACE(l.entry, '[[:cntrl:]]+', ''),'Æ','`')) "Log Text"