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 SQL query doesn't return proper value Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query doesn Watch "SQL query doesn New topic
Author

SQL query doesn't return proper value

Isaac Ferguson
Ranch Hand

Joined: Jun 22, 2012
Posts: 419
Hi

I am working with the next query




I have two tables

Person
---------

Person_id , last_name, first_name, street, city

Order
--------

Order_id, Order_no, Person_id

I want ALL orders with First Name of the corresponding person (if available)

The data in the DB is

Person_id (Cod1), last_name(Isaac), first_name(Dura), street(Rozel), city(Bristol)

Order_id(1), Order_no(2), Person_id(Cod1)

When I run the query I get no errors but the column Order_id is empty

Any idea, please?

Thanks



Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

I recommend joining the person/order explicitly using the "INNER JOIN" syntax. It may resolve some of the issues you are seeing.


My Blog: Down Home Country Coding with Scott Selikoff
Isaac Ferguson
Ranch Hand

Joined: Jun 22, 2012
Posts: 419
Hi like this


select public.order.ORDER_ID from public.person INNER JOIN public.order where public.person.person_id=public.order.person_id and public.person.first_name='Dura'

I get an an error like

Error code 0, SQL state 42601: ERROR: sintax error close to «where»

Thanks
Greg Charles
Sheriff

Joined: Oct 01, 2001
Posts: 2864
    
  11

I'm curious why your first query didn't work. Your second query though just has bad syntax. It should be:



Isaac Ferguson
Ranch Hand

Joined: Jun 22, 2012
Posts: 419
Hi

I have run the new query. But I don´t get result. It is estrange. Maybe could be another reason?

Regards
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

It could be because what you are getting is really what is in the database, and you are mistaken about what you think is in the database. Or it could be that your code which extracts data from the database is wrong.
Isaac Ferguson
Ranch Hand

Joined: Jun 22, 2012
Posts: 419
I am running the query in the DB itself.. I mean I am not using code.

When I run select public.order.ORDER_ID from public.person,public.order where public.person.person_id=public.order.person_id

I get the number of the order but when I add the nextg two queries I get ORDER_ID but it is empty

select public.order.ORDER_ID from public.person,public.order where public.person.person_id=public.order.person_id and public.person.first_name='Dura'

or

SELECT public.order.ORDER_ID FROM public.person
INNER JOIN public.order ON public.person.person_id=public.order.person_id
WHERE public.person.first_name='Dura'

Any idea,please?

Thanks
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1871
    
  16

I just created two tables person and order on my PostgreSQL database, inserted records like your person/order examples, and ran the following queries, which both returned a single row with the expected Order ID:

The query syntax is correct, so the problem must be in how it's finding (or not finding) the data. One problem could be if you have trailing spaces on your names e.g. "Dura " instead of "Dura". You can check for this by using TRIM on the name columns:

If this query works, then you know there are rogue spaces in your first_name column, so you need to trim these off when inserting/updating your data. Also, make sure you use VARCHAR (or VARCHAR2) and not CHAR as the data-type for your character columns, as some databases fill CHAR columns with spaces without you realising it.


No more Blub for me, thank you, Vicar.
Isaac Ferguson
Ranch Hand

Joined: Jun 22, 2012
Posts: 419
Hi

now when I use



It works well.

But when I type



args[0] contains Frederic

But the resultset is empty

Any idea, please?

Thanks
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

Of course not. If you take the string



and paste it into the tool you're using to test your SQL code, you'll find it doesn't return any records there either. That's because your table doesn't contain any records with the string "args[0]" in the first_name column.
Isaac Ferguson
Ranch Hand

Joined: Jun 22, 2012
Posts: 419
Now I have try

String argsName=args[0];

String sqlQueryOrder = "SELECT public.order.ORDER_ID,public.person.first_name,public.person.last_name " +
"FROM public.person INNER JOIN public.order " +
"ON public.person.person_id=public.order.person_id WHERE public.person.first_name='argsName'";

Yes it doesn´´t works

Could anyone tell what way can I do it, please?

I mean this

public.person.first_name='argsName'";

Thanks
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61764
    
  67

Think!

That's no different. Does your table have a first name column with the value 'argsName'? Of course not...

The big hint: PreparedStatement


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL query doesn't return proper value