This week's book giveaway is in the Java in General forum.
We're giving away four copies of Think Java: How to Think Like a Computer Scientist and have Allen B. Downey & Chris Mayfield on-line!
See this thread for details.
Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Big SQL

 
Pranit Saha
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How to assign big SQL statements in a String variable.. The excerpts given below..

String rsString=
"SELECT SO_NUM,ORDERING_CODE,FIN_QTY
FROM
CCTPL_RAWMATS_ISSUE_DETAIL
WHERE ORDERING_CODE=
(
SELECT ORDERING_CODE_REAL
FROM
CCTPL_SALES_QUOT_DETAILS
WHERE ORDERING_CODE='099-0004'
AND QUOT_NO=
(SELECT QUOT_NO
FROM
CCTPL_SALES_PO_MASTER
WHERE PO_NO='004'
)
)
AND PO_NO LIKE '004'";

In Compilation above code gives multiple "Unclosed character literal" error..

Pranit..
 
Dmitry Melnik
Ranch Hand
Posts: 328
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
chandana sapparapu
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using a StringBuffer is better, to prevent memory leak.
 
Dmitry Melnik
Ranch Hand
Posts: 328
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There will be no memory leak, if all Strings being added are constants. The result will be calculated at compile time.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Pranit,

I would recommend laying the query out in the Java source as you would if it was specified in a SQL script or stored procedure. I've adapted it to my style below (well, not quite). I find it helpful to distinguish between keywords and tables/columns using upper/lower case. The neat little column of plusses is rather sad, but a personal preference. Obviously your project coding standards (or personal taste?!) may preclude you from adopting this style.

Additionally, I think I'm right in saying that, as the sub-queries are both singletons, you could rewrite the query like this:

That should return exactly the same results. I think that makes the query more readable. It should also improve its performance (logical IOs), though I doubt that is an issue.

I know this wasn't what you asked for, but I hope you find it useful in some way.

Jules
 
Stefan Wagner
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
... but Jules, if you hadn't mentioned it, I would.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic