• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to iterate in pure SQL?

 
Chengwei Lee
Ranch Hand
Posts: 884
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

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



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

Thanks!
[ August 19, 2005: Message edited by: Chengwei Lee ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chengwei,
Where are you calling the pure SQL from? If it is JDBC, you can handle the logic in Java.
 
Patrick van Zandbeek
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 884
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 884
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 884
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic