File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes query returning null Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "query returning null" Watch "query returning null" New topic
Author

query returning null

seshayya krishna
Greenhorn

Joined: Feb 24, 2005
Posts: 11
Hi
When I ran the following query in sqlplus of oracle 9i it returns all
column values properly. (matching table affiliation status properly). But when I ran the query in java, it returns 'null' values for status column.

Can some one tell me, what is wrong with the query ? Thanks in advance.

Krishna


Select * from (
SELECT rownum as count, organization_id, duns, duns_plus4, Cage_code, legal_bus_name, dunsnumber, status,affiliation_id
FROM (
SELECT null, b.organization_id, b.duns, duns_plus4, b.Cage_code, b.legal_bus_name,
CONCAT(CONCAT(b.duns,'-'),nvl(b.duns_plus4,'')) dunsnumber, a.status as status, a.affiliation_id
from affiliation a, organization b
where b.organization_id = a.organization_id
AND a.nspires_user_id = '44B016B17E677985CC9D49BD0370D125'
UNION ALL
Select null, b.organization_id, b.duns, duns_plus4, b.Cage_code, b.legal_bus_name, CONCAT(CONCAT(b.duns,'-'),nvl(b.duns_plus4,'')) dunsnumber, to_char(null) as status, null
from organization b Where NOT EXISTS (
Select * from affiliation c
where b.organization_id = c.organization_id
AND c.nspires_user_id = ('44B016B17E677985CC9D49BD0370D125') ) order by legal_bus_name )x
where LEGAL_BUS_NAME LIKE UPPER('%o%') order by legal_bus_name
) where count >= 0 and count < 40

[edited to change subject from "Very Urgent I need help"]
[ February 24, 2005: Message edited by: Jeanne Boyarsky ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

Seshayya,
Welcome to JavaRanch!

I edited your post to give it a more meaningful subject. We prefer people not use "urgent" in the subject. See How To Ask Questions the Smart Way for more tips and the reasons behind this.


[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
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

Now on to your question:
I assume status is a varchar? It seems like a simple rs.getString() should work.

Can you post the snippet of code where you read in the resultset? Also, try to come up with a simplified example. See how much of the query you can remove and still have the problem. That will help you isolate the problem.
seshayya krishna
Greenhorn

Joined: Feb 24, 2005
Posts: 11
while (rs.next())
{
AffiliatedOrganizationViewBean affiliationObjectBean = new AffiliatedOrganizationViewBean();
affiliationObjectBean.setId(rs.getString("organization_id"));
affiliationObjectBean.setDunsPlus4(rs.getString("duns_plus4")) ;
affiliationObjectBean.setCageCode(rs.getString("Cage_code")) ;
affiliationObjectBean.setDuns(rs.getString("duns")) ;
affiliationObjectBean.setOrganizationName(rs.getString("legal_bus_name")) ;
affiliationObjectBean.setStatus(rs.getString("status"));

String dNum = rs.getString("dunsnumber").replace('-', ' ');

if ((rs.getString("duns_plus4") != null) && !(rs.getString("duns_plus4").equals("")))
{
dNum += '-' + rs.getString("duns_plus4");
}
affiliationObjectBean.setDunsnumber(dNum) ;

if ((rs.getString("status") == null ) || (rs.getString("status").equals("")))
{
affiliationObjectBean.setStatus("Unaffiliated");
}
AffiliatedOrganizations.add(affiliationObjectBean) ;
}
seshayya krishna
Greenhorn

Joined: Feb 24, 2005
Posts: 11
Hi
Thanks for your quick message. When I read the result set like rs.getString("status") I am getting 'null' values. Even though the same query
returns correct values in SQL PLUS (oracle 9i).

I tried all possible combination of selection criteria.

Please help me. This is very urgent. I have no help.

Krishna
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
hi, can you tell me the data type of status variable


Hareesh Ram Chanchali
SCJP 5.0, IBM Certified Solution Designer
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
hi,

you use getObject and then use toString function
then uyou see the result and tell me your output
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
Hi Seshayya,

You can try one thing.
Before executing the query, try to print it in a console and just execute the query printed in the console in a SQLPlus Session.

Also it's agood practise to use Nvl() method.

i'm just curious about what the following query having many null does

Select null, b.organization_id, b.duns, duns_plus4, b.Cage_code, b.legal_bus_name, CONCAT(CONCAT(b.duns,'-'),nvl(b.duns_plus4,'')) dunsnumber, to_char(null) as status, null


Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

A common mistake in JDBC is to read the same value multiple times: you shouldn't!

When calling rs.getString("duns_plus4"), call it once and store the value then refer to the storred value. This may or may not be related to your problem...

Dave
seshayya krishna
Greenhorn

Joined: Feb 24, 2005
Posts: 11
The data type of status variable is varchar .
Carol Enderlin
drifter
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
Are you sure it is behaving differently in sqlplus vs jdbc?

I see in your query that you are making status null for part of your query:

to_char(null) as status
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

Seshayya,
Did you try Dave's suggestion? What happened?

Also try,
affiliationObjectBean.setStatus(rs.getString(8));

This will tell you if the problem is with the field name or something else.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: query returning null