• 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
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

Big SQL

 
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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..
 
Ranch Hand
Posts: 328
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1923
Scala Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
... but Jules, if you hadn't mentioned it, I would.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic