Link to home
Start Free TrialLog in
Avatar of Larry Groves
Larry GrovesFlag for United States of America

asked on

Returning duplicate values from where clause

I have an xls file with a bunch of rows. The rows include columns order number and dollar amount. I need to add 1 more column (address ID) to the XLS. The problem I'm having is that in the XLS, a given order number can be in there 2 or more times because the order may have been split-shipped. So, in my select's where clause, I will be passing a given order number 2 or more times and I need my result set to return the same number of times that I'm passing it.

For example:

ORDER . AMOUNT
12345 . 100.00
12345 . 95.98
12346 . 10.22

Open in new window


select order,address from order where order in (12345,12345,12346,...) order by order;

Thanks,
Larry

when I run this, I only get back 12345 1 time. Is there anyway I can get back a result with 12345 in there twice
Avatar of PortletPaul
PortletPaul
Flag of Australia image

I see no reason why the second or subsequent rows would be returned for each order. It's not the SQL that is stopping this.
What is the relevance of Oracle to this question? (please don't use non-relevant topics)

Where are you executing the SQL? (In Excel?)
Oh, sorry for the multiple posts

 "order" is a reserved SQL word (i.e it is used as a command to control the sorting of the output)

I suggest changing the table name and column name to something other than this word. e.g. table = order_detail and column = order_num

select order_num,address from order_detail where order_num in (12345,12345,12346,...) order by order_num;
Avatar of Larry Groves

ASKER

Apparently you didn't read my entire question. How exactly do you run a query in Excel using:

select order,address from order where order in (12345,12345,12346,...) order by order;

Yes, Oracle IS the relevant topic.

As for your first reply, if I run that select statement, Oracle will only return:
12345,<address>
12346,<address>

because there is only 1 row for order 12345 in the DB. The thing is, I need 12345 returned twice (based on my where clause) because there are 2 rows for that 1 order in the Excel file.

Thanks,
Larry
The table name is actually ORDERS. I was just using it as an example in my example select statement.

Thanks,
Larry
Larry, I did read the question - what you failed to mention is that the address table is in Oracle.

You cannot get more rows returned from the table by using an IN list, you have to use a join e.g.

select
   x."order", x.amount, o.address
from excel_table x
left join ora.table_name o on x."order" = o."order"
I'm totally confused. What exactly is "excel_table" and I'm not running any queries against an address table. Maybe this example query is better...

select order_id,address_id
from orders
where order_id in (12345,12345,12346,)
order by order_id;

Open in new window


The list of order_id values is in an actual Excel spreadsheet. I'm not linking the spreadsheet to the DB in any way.

Thanks,
Larry
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you PortletPaul. This worked perfectly! I appreciate your help.

Thanks,
Larry