PL/SQL

That Time the Parens “Almost” Got Me

As a developer, there are days when you pat yourself on the back for the cool, innovative code you wrote, and smile at how clever you are.

Then there are times when you wonder how you can walk and chew gum at the same time. I had one of those “Duh!” moments yesterday.

I am working on a project for our admissions office. They used to get boxes and boxes of paper Advanced Placement(AP) test scores from high school students who are considering applying to Temple. AP tests are exams that high school students can take, usually in a subject they excel in, such as math, history or biology, and if they score high enough on the exam, they can obtain college credit for a course they normally would have had to take.It can shave 3 or more credits off of their academic program of study once in college.

The admissions staff were known to have manually entered over 2,000 scores during any one admissions cycle. As of this past summer, those test scores are now arriving electronically and loading into Banner – our student information system. However, there is a third party system we are using to evaluate those test scores and how the AP credit can be applied towards Temple programs. That system is where the admissions staff used to key in the test scores, and that is my part of what is now a two-step automated process- I have to take the scores out of Banner, and massage the data and get it into the third party solution, where the actual evaluation of AP credit to Temple credit occurs.

My team and I have made good progress so far and I was testing my part yesterday. The university has dozens of types of tests that come in for students such as entrance exams (SATs, ACTs, MCATS, LSATS, etc) and placement tests. The AP scores are designated in such a manner that the first two characters of the test code are “AP”. So for example, “AP20” is a biology test, “AP25”  Chemistry, etc.

In my querying of the database, I was grabbing all AP tests that had been added to students’ records , with a score of at least three, and that had no “DR” code in another column. However, in my result set I was getting tests that did not start with AP. Where were those EETK, MP1, MP2, MP3, EESS scores coming from ?

parens_trouble1_092316

After scratching my head a bit and a few expletives mumbled under my breath, the “Ah ha” moment arrived. Lines 7 and 8. The lack of properly placed parens (parenthesis) with the ANDs and OR was doing me in. Once I got them right, my result set looked much better.

parens_trouble2_fixed_092316

Moral of the story – you can never be too careful, even when you “think” you have this SQL thing nailed down. Take the time to check yourself!

 

Quick and Dirty Coding Hacks

I have been working with Oracle technology since 2010, and know my way around a database well enough, and do an okay job supporting our E.R.P. system at my job. However, I’m no PL/SQL nor SQL expert. Far from it. And some days I just don’t have the time nor the inclination to “Ask Tom”, so I resort to the tools I am familiar with, no matter how elementary/rudimentary they may be, to get me through the day.

For example: I recently was in a situation where I had to copy three of our prod tables down to a non-prod schema, renaming the tables and each column in the table, for training purposes for a new staff member, so he could follow along with the way the tables were structured in a training manual. I work at Temple University (Go Owls!) which is an Ellucian Banner school, and Banner’s table-naming standards are such that the column headings each have the table name in them. For example, on the SPRIDEN table, every column is named SPRIDEN_[something],  as in SPRIDEN_ID, SPRIDEN_FIRST_NAME … you get the picture. But the training manuals referred to tables in a training schema I no longer had access to,  the  SWRIDEN table, and all column names SWRIDEN_[something]”. Some of these tables have hundreds of columns and I was not inclined to type hundreds of ALTER TABLE” statements into my SQL Developer editor window. Maybe I’m just lazy.

So after creating the new tables by copying the prod versions, via SQL Developer, I could not find a way to globally rename the column headings for the newly copied/renamed tables. If there is such a way to do this, and anyone who may be reading this article knows how, I’d greatly appreciate a comment telling me how to do so. In other words, is there a command where I can say: rename all columns on a table from __ to __?

Instead, I handled this with the tools at hand that I’m familiar with. My Methodology may make you cringe. But remember- this is  “quick and dirty” hack # 1.

First thing: I set my SQL Developer Database preferences for Drag and Drop to format select statements for me, so that I would not have to type out each field name on these very large tables.

Drag and Drop

Next, I selected all of the columns and copied them: (Ctrl+C) …

copy and paste_spriden-image2

… then pasted them onto a new Excel worksheet, in the second column, B (Ctrl+v)

paste_excel-column2-image3

Since each column name is followed by a comma, I globally removed them on the worksheet by selecting the column, and doing a “find & select” then going to the “replace” tab. I just replaced the commas with a space (Literally a hit of the space bar).

remove the commas-image4

Next, I copied that column, with its removed commas, on to another column, skipping one in between. So I went from column “B” to “D”, making sure I  pasted as “values”:

 

copy and paste whole column B to D-image6

paste to D-image7

Now I had identical data in columns “B” and “D”, and needed to replace the old column names with the new. So just like I used “find & select” to replace the commas in column “B” with spaces, I did the same thing in column “D”, replacing the old table name with the new, or in my case, replace SPRIDEN with SWRIDEN:

replace-all_columnd-imag8

So far I had:

two new columns-image9

I was then ready to build the “ALTER TABLE” statements.

In column A1 of the spreadsheet, I entered my alter statement: Alter table SWRIDEN rename column. In C1 put to and in E1, the semicolon to end the SQL statements I’m creating:

prepare your alter statements1-image10

To copy the alter statements down to the last row in A that is adjacent to B,  don’t click and drag!  The easiest way to fill a column in Excel is by hovering over the bottom right corner of the cell you want to copy down, until the small square black box appears in that corner, then double click on the small black box:

the little black square1-image11

Double click on the small black box in the right corner to fill the column of data to the last row of the adjacent column

12c1

Fill columns C  “to”  and in E the semicolon

image13a

 

The alter statements have now been built.  Then I simply copied and pasted them right into SQL Developer and executed:

or right into sql developer-image16

 

I hope this little hack will be useful to others out there who may be new to SQL, and just need a fast way to get something done, with older, more familiar tools. Look for more quick and dirty hacks to come. I’d love to hear some of yours as well!