Larry Groves
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:
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
For example:
ORDER . AMOUNT
12345 . 100.00
12345 . 95.98
12346 . 10.22
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
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?)
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;
"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;
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
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
ASKER
The table name is actually ORDERS. I was just using it as an example in my example select statement.
Thanks,
Larry
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"
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"
ASKER
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...
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
select order_id,address_id
from orders
where order_id in (12345,12345,12346,)
order by order_id;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you PortletPaul. This worked perfectly! I appreciate your help.
Thanks,
Larry
Thanks,
Larry