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 question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql question" Watch "sql question" New topic
Author

sql question

Abigail Moore
Greenhorn

Joined: May 13, 2004
Posts: 12
I have the following sql:

SELECT customer_id, hierarchy_id, hierarchy_type_cd,
primary_station, primary_type_cd
FROM ebat.ha_station_hierarchy_811
WHERE customer_id = '9999999999'
AND primary_station = '5629402401'

This returns 2 rows of data where primary_type_cd is different. How do I change this sql so that it rolls the 2 rows of data into 1, ignoring the difference in the primary_type_cd?

Thanks for the help
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61606
    
  67

Abigial, why would you be posting a SQL question in a forum about build tools such as Ant? Please be a bit more careful about where you are posting.

I've moved this post to the JDBC forum for you.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Abigail Moore:
I have the following sql:

SELECT customer_id, hierarchy_id, hierarchy_type_cd,
primary_station, primary_type_cd
FROM ebat.ha_station_hierarchy_811
WHERE customer_id = '9999999999'
AND primary_station = '5629402401'

This returns 2 rows of data where primary_type_cd is different. How do I change this sql so that it rolls the 2 rows of data into 1, ignoring the difference in the primary_type_cd?

Thanks for the help


Note the addition of the DISTINCT keyword, and the removal of the primary_type_cd column from the selection criteria; since there are two different values and you want just one row, you either have to pick a particular value of primary_type_cd or omit it.

An example of picking one of the primary_type_cd values is:

The success of this SQL depends on what your database actually supports and the actual data type of the primary_type_cd column; it might not work for you on your particular database, but something else along those lines will.
Abigail Moore
Greenhorn

Joined: May 13, 2004
Posts: 12
Thank you! I will try that, I did try distinct but didn't remove primary_type_cd.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql question