Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

query returning null

 
seshayya krishna
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34839
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34839
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi, can you tell me the data type of status variable
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,

you use getObject and then use toString function
then uyou see the result and tell me your output
 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The data type of status variable is varchar .
 
Carol Enderlin
drifter
Ranch Hand
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34839
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic