Computing desk
< December 5	<< Nov \| December \| Jan >>	Current desk >

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

December 6[edit]

Querying multiple items in a single field from Excel to Oracle SQL[edit]

I write lots of SQL-based queries for my company and I'm often asked if it's possible to query on a number of items at once (product numbers, customer accounts, etc.). People want to be able to input ten or twelve or a hundred items and get the query to spit out the results. Googling has not been very helpful; I either get people doing something else (querying different fields), something simpler (querying some small fixed number of parameters, solvable with OR statements), and stuff from years ago pronouncing it undoable.

Here's a quick example query: SELECT * FROM MSQ_PROD PR WHERE PR.PRODUCT_NO IN (?) where I'd like the ? parameter to point to a cell with some number of SKUs in it, separated by commas. When I try, it says it's an "invalid number" yet I can copy-paste the content of the cell directly into the SQL and have it work.

Is there a way? It seems like the kind of thing lots of people would clamour for, so it boggles me that that this is just not possible. On the other hand, Excel chokes on SQL parameters leading directly into pivot tables, so maybe I shouldn't be surprised. Matt Deres (talk) 19:23, 6 December 2019 (UTC)[reply]

The IN argument requires a second SQL query. This is usually the slow way. If necessary, add filters behind the SELECT argument. Usually MS-Access and SQL server console support drawing or paint such queries instead programming it. --Hans Haase (有问题吗) 17:07, 9 December 2019 (UTC)[reply]

The IN clause can contain a list of values (at least in MS-SQL), e.g. ... WHERE PR.PRODUCT_NO IN (1,2,3,42,999). If I understand the question correctly you would need to construct the SQL statement first, as (assuming the values are in cell A1). ="SELECT * FROM MSQ_PROD PR WHERE PR.PRODUCT_NO IN (" & A1 & ")" and then execute it. Unless you are 100% sure that the list will be valid (or even if you are) you should sanitise it first, to avoid the possibility of SQL injection (aka the Bobby Tables problem) AndrewWTaylor (talk) 20:55, 9 December 2019 (UTC)[reply]

Thank you; I'll give that a shot, though I'm not familiar with that format you've used. MS-Query allows for named parameters, but they're in a format like [?date] or [?start], as opposed to SQL's simple ?. I don't think I've ever injected a cell reference directly into the SQL statement before. Well, nothing ventured, nothing gained. :) Matt Deres (talk) 13:56, 12 December 2019 (UTC)[reply]

SD card question[edit]

I somehow managed to break the write protect switch completely off an SD memory card, as in the switch is no longer there on the entire card. As a result, the card can be read all OK, but it's permanently write protected. Can this be fixed? JIP | Talk 19:57, 6 December 2019 (UTC)[reply]

Easy to fix. See https://www.youtube.com/watch?v=oKrn-0jXZCU for instructions. --Guy Macon (talk) 21:46, 6 December 2019 (UTC)[reply]

Yes, the switch is not an electrical switch inside the SD-Card, its keyed and red by a switch inside the card socket. Just glue something not sticky on the card, to trigger the switch inside the socket. --Hans Haase (有问题吗)