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_GPA_TYPE_IND = 'O' AND GPA_LEVL_CODE = 'U' AND GPA_ACTIVITY_DATE = --2017 (SELECT MAX(X.GPA_ACTIVITY_DATE) FROM GPA X WHERE X.GPA_PIDM = stu_pidm.PIDM AND GPA_GPA_TYPE_IND = 'I' --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 = 'I' --AND GPA_GPA_TYPE_IND = 'O' ) ;
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 = 'I' AND GPA_GPA_TYPE_IND = 'O' AND GPA_LEVL_CODE = 'U' AND GPA_ACTIVITY_DATE = --2017 (SELECT MAX(X.GPA_ACTIVITY_DATE) FROM GPA X WHERE X.GPA_PIDM = stu_pidm.PIDM --AND GPA_GPA_TYPE_IND = 'I' 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 = 'I' 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_GPA_TYPE_IND = 'O' AND GPA_LEVL_CODE = 'U' AND GPA_ACTIVITY_DATE = 2017 /*(SELECT MAX(X.GPA_ACTIVITY_DATE) FROM GPA X WHERE X.GPA_PIDM = stu_pidm.PIDM AND GPA_GPA_TYPE_IND = 'I' --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 = 'I' --AND GPA_GPA_TYPE_IND = 'O' ) ;
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_GPA_TYPE_IND = 'O' AND GPA_LEVL_CODE = 'U' AND GPA_ACTIVITY_DATE = --2017 (SELECT MAX(X.GPA_ACTIVITY_DATE) FROM GPA X WHERE X.GPA_PIDM = stu_pidm.PIDM AND GPA_GPA_TYPE_IND = 'I' --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 = 'I' --AND GPA_GPA_TYPE_IND = 'O' )*/ ;
As you can 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!