Introduction
In my day to day job, I create a lot of SQL reports using Oracle SQL Developer. Often, I edit the SQL code in Notepad++, because I find the syntax highlighting to be better in Notepad++; plus I use it for many other editing purposes.
Often, when I’m writing my SQL code, the results produced have additional or too many rows returned, so I started using commenting techniques to simply development. This is what this article is about, simplifying development of SQL code with comments.
Example Code
Here is some example obfuscated code:
SELECT *
FROM GPA
WHERE GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'I'
AND GPA_LEVL_CODE = 'U'
AND GPA_ACTIVITY_DATE =
(SELECT MAX(X.GPA_ACTIVITY_DATE)
FROM GPA X
WHERE X.GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'I'
)
AND GPA_HOURS_ATTEMPTED = (
SELECT MAX(X.GPA_HOURS_ATTEMPTED)
FROM GPA X
WHERE X.GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'I'
)
;
Notice for the AND operators I’ve put them individually on a newline. Also, you see after GPA_ACTIVITY_DATE
, I have 2017 commented out at the end of the line.
Changing to Outcome GPA
To change my code to Outcome GPA, which is GPA_GPA_TYPE_IND = ‘O’
, I simply make this change:
SELECT *
FROM GPA
WHERE GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'O'
AND GPA_LEVL_CODE = 'U'
AND GPA_ACTIVITY_DATE =
(SELECT MAX(X.GPA_ACTIVITY_DATE)
FROM GPA X
WHERE X.GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'O'
)
AND GPA_HOURS_ATTEMPTED = (
SELECT MAX(X.GPA_HOURS_ATTEMPTED)
FROM GPA X
WHERE X.GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'O'
)
;
Then just run it to see the result. The change is just uncommenting 3 lines and commenting the lines with “AND GPA_GPA_TYPE_IND = ‘I'”
.
Activity Date of 2017
To change the GPA_ACTIVITY_DATE
to just 2017
, make this simple change:
SELECT *
FROM GPA
WHERE GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'I'
AND GPA_LEVL_CODE = 'U'
AND GPA_ACTIVITY_DATE = 2017
AND GPA_HOURS_ATTEMPTED = (
SELECT MAX(X.GPA_HOURS_ATTEMPTED)
FROM GPA X
WHERE X.GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'I'
)
;
I simply uncommented the sub selection statement and uncommented 2017
.
No Need for GPA Hours
Let’s say I don’t need GPA_HOURS_ATTEMPTED
. The change is also simple:
SELECT *
FROM GPA
WHERE GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'I'
AND GPA_LEVL_CODE = 'U'
AND GPA_ACTIVITY_DATE =
(SELECT MAX(X.GPA_ACTIVITY_DATE)
FROM GPA X
WHERE X.GPA_PIDM = stu_pidm.PIDM
AND GPA_GPA_TYPE_IND = 'I'
)
;
As you can see, by formatting my code and using comments at various points, it makes it easy to troubleshoot the SQL code by making minimal changes.
Hope this helps you!
I’m a software developer. Currently I’m working at Taft College as a Programmer.