Troubleshooting SQL Code Using Comments

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 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!