It Works in Developer but not in sqlReports

Often times I hear "my sql works in oracle sql developer but not in sqlReports" and I thought I'd write an article explaining common reasons why.  Although the focus is on oracle sql developer, the following would be true for any sql client.  But the main reasons why things work in developer and not in sqlReports is because of 1) tlist_sql, 2) html, and 3) sqlReports code:

tlist_sql Issues

sqlReports uses a PS command called tlist_sql to output the results of the sql used in the report.  While tlist_sql is nice and powerful, it does have it's rules, one of which is no colon, semi-colon, or square bracket is allowed anywhere in the sql unless you use an ascii reference.  Below are some examples courtesy of Adam Larsen on how you have to write the sql:

Colon:

Before:  to_char(to_date(attt.time_in,'SSSSS'),'HH:MI AM')
After:  to_char(to_date(attt.time_in,'SSSSS'),'HH'||chr(58)||'MI AM')

Square Brackets:

Before: where (length(regexp_replace(s.guardianemail,'[^@]')) != length(regexp_replace(s.guardianemail,'[^,]'))+1)
After:  where (length(regexp_replace(s.guardianemail,chr(91) || '^@' || chr(93))) != length(regexp_replace(s.guardianemail,chr(91) || '^,' || chr(93)))+1)

Another tlist_sql rule is if you include date information in your where clause, you may need to use to_date commands to make sure tlist_sql treats everything in a date format.  Developer may not care and will let you get away without using to_date in some cases.  If you have dates in your where clause and aren't getting any information or incorrect information, try removing them and see if you get results.  If so, you have a date formatting issue and this article may help. 

Finally, in tlist_sql you must define values in the select.  In Developer, you can do something like Select * from students.  The Select * will not work in tlist_sql - you must replace it with actual values, such as field names or aggregate commands.  

Fortunately you can test out your sql in developer using the same sql you'll need to use in sqlReports, so make sure to try things like the chr coding, date formats, and selects with fields, in Developer before trying it in sqlReports.


HTML

The another reason things don't work in sqlReports when they do in developer is the fact that sqlReports is a set of web pages and uses html coding in it's output.  For example, if you have double quotes in your sql, that will cause an issue with the html.  The most common example of this is when one has an alias in a select in developer in double quotes, such as "Student Name".  Trying to use that in sqlReports will cause you to get a blank report due to the double quotes and how they're treated in html in general.  You don't need to have aliases at all in your main select in sqlReports because of the column header part, but if you use them, make them one word so you don't need the double quotes. 

Another example where the report can break is trying to use double quotes in html in the output, such as in a link to a student.  See this article for the proper way to use single quotes in those instances, as well as how to use various html commands in sqlReports.


sqlReports Code

One final reason for issues is coding in sqlReports itself, primarily javascript and jquery commands.  For example, there's javascript coding that counts the number of comma's in the main select in order to create the number of columns in the output.  The script is called when you click the Build Header link to create column headings, but is also used on the params.html page when the sql is executed.  If the number of items in the main select and the number of columns in the output don't match, you'll have issues.  So you always need to make sure they match, but a comma in a concatenation, such as first_name||', '||last_name will be counted and will throw off the column count.  In those cases, you'll need to use the ascii code for comma as a workaround like so - first_name||chr(44)||' '||last_name.  The comma will still output but will not be counted by the javascript since it's in ascii code.

Another example of sqlReports coding and issues is the code looks for spaces after the words With or Select due to the way javascript works.  If you have a return after them instead of a space, they will be ignored.  It's best to put something on the same line as any With or Select rather than just have them on lines by themselves.



Works in sqlReports but not Developer

I don't hear the above phrase that often, but when I do it has to do with the fact one has PS commands in the sql.  Developer isn't going to recognize PS specific commands, such as ~(curschoolid), ~[if., or the parameter codes like param1.  In those case you must put actual data in those places when you run the sql in Developer.  Just be sure to switch them back to the PS codes when you put the sql back in sqlReports so that the report is once again dynamic.

 

  Back to the List of Articles