aspose file tools*
The moose likes Oracle/OAS and the fly likes How to iterate in pure SQL? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "How to iterate in pure SQL?" Watch "How to iterate in pure SQL?" New topic
Author

How to iterate in pure SQL?

Chengwei Lee
Ranch Hand

Joined: Apr 02, 2004
Posts: 884
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 ]

SCJP 1.4 * SCWCD 1.4 * SCBCD 1.3 * SCJA 1.0 * TOGAF 8
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Patrick van Zandbeek
Ranch Hand

Joined: Aug 17, 2005
Posts: 37
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.


u is a letter, not a word.
Chengwei Lee
Ranch Hand

Joined: Apr 02, 2004
Posts: 884
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

Joined: Apr 02, 2004
Posts: 884
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

Joined: Aug 17, 2005
Posts: 37
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

Joined: Apr 02, 2004
Posts: 884
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to iterate in pure SQL?