• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to iterate in pure SQL?

 
Ranch Hand
Posts: 884
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
"How many licks ..." - I think all of this dog's research starts with these words. Tasty tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic