How to iterate in pure SQL?

[Chengwei Lee]

Hi all,

Using PL, I know I can iterate by using the below codes:

  
  6      l_query long := 'select name ';
  7  begin
  8  
  9      for i in 1 .. trunc(p_end)-trunc(p_start)+1
 10      loop
 11          l_query := l_query || ', sum( decode( trunc(d), ' ||
 12                          'to_date( ''' || to_char(p_start+i-1,'yyyymmdd') ||
 13                          ''', ''yyyymmdd'' ), amt, 0 )) "' ||
 14                          to_char(p_start+i-1) || '"';
 15      end loop;
 16      l_query := l_query || ' from t group by name';
 17      open p_cursor for l_query;
 18  end;

But how do I translate the for-loop into pure SQL only?

Thanks!
[ August 19, 2005: Message edited by: Chengwei Lee ]

[Jeanne Boyarsky]

Chengwei,
Where are you calling the pure SQL from? If it is JDBC, you can handle the logic in Java.

[Patrick van Zandbeek]

You can't do loops and stuff in pure SQL. It's a query language not a real programming language. That's why there's PL/SQL in Oracle, T-SQL in SQL Server, etc. Those are really extensions to SQL so that you -can- program around your queries and make nice decision trees.

The only thing remotely resembling programming are CASE statements which let you do a sort of if else constructions, but they can get ugly real fast.

So either do it in Java with JDBC like Jeanne suggested or if that's not possible you should try something like PL/SQL.

[Chengwei Lee]

Hi Jeanne,

Thanks for your reply & sorry for my late reply.

Where are you calling the pure SQL from? If it is JDBC, you can handle the logic in Java.

I'm embedding the query inside a JasperReport template. I've a crosstab report that JasperReport current doesn't supports. Hence, I thought of using the query to return the ResultSet in the crosstab format.

By keeping the query inside the report template instead of Java codes, I won't have to recompile & repackage my application every time there's a requirement change or bug fix.

Regards.

Chengwei

[Chengwei Lee]

Hi Patrick,

Again, thanks for your reply & sorry for my late reply.

You can't do loops and stuff in pure SQL. It's a query language not a real programming language. That's why there's PL/SQL in Oracle, T-SQL in SQL Server, etc. Those are really extensions to SQL so that you -can- program around your queries and make nice decision trees.

The only thing remotely resembling programming are CASE statements which let you do a sort of if else constructions, but they can get ugly real fast.

So either do it in Java with JDBC like Jeanne suggested or if that's not possible you should try something like PL/SQL.

As far as possible, I'm trying to avoid JDBC & PL/SQL. Both methods would reduce the real-time scalability of our reporting application. By having my SQL in JasperReports template, with any changes to a report, I do not have to touch my existing Java codes at all.

Regards.

Chengwei

[Patrick van Zandbeek]

Cool, don't know much about Jasper Reports. I do know a lot about SQL though and I understood "pure SQL" as meaning, without additional non SQL programming. If Jasper Reports gives you the additional functionality to run pieces of SQL multiple times, generate it dynamically or let you work with the result set then that could solve your problem. However, while I work a lot with reporting tools and SQL, I haven't worked with Jasper yet, so I could only guess here.

Hope you find the ideal solution

[Chengwei Lee]

Hi Patrick,

Actually they do have plans to build in crosstab report support for JasperReports, its just a matter of whether am I able to wait for the feature to be incorporated or not.

Thanks, once again.

Chengwei