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.  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 something mentioned below in Developer before trying it in sqlReports.


tlist_sql Issues

Special Characters

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)

The sqlReports vesion will work in Developer as well, so you can test them there to see if you have any errors before using them in sqlReports.  Here's another popular example - splitting lastfirst into two at the comma:

Before:  select regexp_substr(lastfirst, '[^,]+', 1, 1), regexp_substr(lastfirst, '[^,]+', 1, 2)
After:  select regexp_substr(lastfirst, chr(91)||'^,'||chr(93)||'+', 1, 1), regexp_substr(lastfirst, chr(91)||'^,'||chr(93)||'+', 1, 2)

Dates

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. 

 

HTML

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 therefore uses html coding in it's output.  Here are some common HTML issues with sqlReports:

Double Quotes

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.

<br> Command

Another example of where html may cause an issue is trying to use the <br> command in the select part of the query.  For example, if you want to have a report where you have one row per student but in one column you want to have the entry and exit dates for the student on separate lines, you would have to do the following:

select lastfirst, grade_level, 'Entry Date - ' || entrydate || chr(60) || 'br' || chr(62) || 'Exit Date - ' || exitdate

The dates are concatenated together and the br command will create the line break, but in order for it to be recognized in sqlReports, you need to use chr commands in place of the < and >


sqlReports Code

One final reason for issues is coding in sqlReports itself - here are some common issues:

Wrong Number of Headings

There's javascript coding that counts the number of commas 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.  If you add or remove something from the select, make sure to add or remove column headings as well.   You can click on the Build Header link at any time to check the count. 

Also, a comma in a concatenation 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:

Before:  last_name || ', ' || first_name
After:   last_name || chr(44) || ' ' || first_name

Commas or the Word From in the Results Areas of Case Statements

A comma in the results of a case statement can be counted and throw off a column heading and will need to be concatenated.  In the following, the Before example would return 5 columns and the After example would return the corrent number of 4:

Before:  select lastfirst, grade_level, case when grade_level = 9 then 'The student is a Freshman, be sure records are updated' end, home_phone
After:   select lastfirst, grade_level, case when grade_level = 9 then 'The student is a Freshman'||chr(44)||' be sure records are updated' end, home_phone

The word From in the results of a case can also cause issues because sqlReports counts the number of commas between the words select and from of the main part of the sql statement.  In the the following, sqlReports will stop counting commas in the Before example once it sees the from in the case result part and the Build Headings link will return only 3 columns.  The workaround is to either replace the word from in the case results with another word or to concatenate from like in the After example, which will then return the correct number of 4 columns:

Before:  select lastfirst, grade_level, case when grade_level = 12 then 'The student will be graduating from the HS' end, home_phone
After:  select lastfirst, grade_level, case when grade_level = 12 then 'The student will be graduating fr'||'om the HS' end, home_phone

Extra commas will increase the number of columns in the count, while the word from will reduce the count due to it stopping the count.

Spacing Issues

Another issue is with spacing in some places.  sqlReports looks for spaces after the words With, Select, and From due to the way javascript works.  If you do not have a space at all after them, they will be ignored.  For example, as mentioned above, sqlReports creates columns based on the number of columns between the words select and from.  If you do not have a space after select or from, it never sees those exacts words to use in the coding and therefore doesn't create the correct number of columns.  

Also, if you have a return after With, Select, and From instead of a space, sqlReports may not recognize the word properly.   It's best to put something on the same line as those three commands too rather than just have them on lines by themselves, and always make sure you have a space after the words.

Select *

In Developer, you can do something like Select * from students.  However, the Select * will not work in tlist_sql because sqlReports counts the number of commas between the select and from and builds columns based on that number (see above).   It will only see one column if you use Select * and therefore you must replace the * with actual values, such as field names or aggregate commands.  



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