aspose file tools*
The moose likes JDBC and the fly likes searching BETWEEN values? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "searching BETWEEN values?" Watch "searching BETWEEN values?" New topic
Author

searching BETWEEN values?

Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

How do people implement those searches that ask for min and max values? I can do the sql for between. But the problem comes when 1 field is left blank. 1 null spoils the whole equation.
How do you handle something like this
SELECT * FROM table WHERE name BETWEEN x AND y;
What happens when x is null, or y is null? Do I have to have 3 sql statements? 1 for each possibility? THis is a common search so I assume this problem has been solved. Whats the answer??
Thanks!
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
How about something like this?


"I'm not back." - Bill Harding, Twister
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

You understand that x and y are the variables the user is entering?
That looks interesting and I think I will give it a shot! Can't use BETWEEN but who cares. I'll report back after I exercise this.
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

JBOSSQL does not seem to allow the x or y input values being null. I have never seen a comparison for nullness on input values either, is this legal?
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

ok, I got it now. it was simpler than I thought.

SELECT * FROM table where
((x = '') OR (x <> '' AND firstName >= x)) AND
((y = '') OR (y <> '' AND firstName <= y)) AND etc...
I shouldn't use null values, just use empty strings. That's probably why everyone that answered the question answered as you did.
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
You understand that x and y are the variables the user is entering?
Well no, I wasn't really sure about that, or if the statement would be legal if that were the case - just trying ideas. Glad to see something vaguely similar worked out...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: searching BETWEEN values?