-2

I have TABLE A with one of the columns containing a single value and TABLE B with one of the columns containing list of possible matching values.

My code seems to take only first items in the list but does not go deeper within a list to find matching number.

Can you please help me to improve the following code:

select  Logs.SingleValue, 
Instances.list from Logs,Instances
where Logs.Column1=Instances.DeviceNumber and 
 (',' + RTRIM(Instances.list) + ',') LIKE  Logs.SingleValue

The data in the list looks like

106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120

or

3346, 3347, 3348, 3349, 3350, 3351, 3352, 3353, 3354, 3355, 3356, 3357, 3358, 3359, 3360

I use SQL within R programming environment; not sure what version it is. I'm not sure if the DBMS is MS SQL Server or ORACLE. All I know is that I have found a similar case and the command did not work so it needs to be handwritten in pure SQL.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Dominic
  • 1
  • 4
  • Your CSV data appears to have spaces in it, which your `LIKE` expression did not include. But besides that, why are you storing your data as CSV in the first place? – Tim Biegeleisen Apr 22 '17 at 14:37
  • I use CSV as I got no DB on server I work on. It would be time consuming to set up. Now coming back to space. I think they are within a list of values. Now I put (',_' + RTRIM(Instances.list) + ',_') LIKE Logs.SingleValue line instead but it did not change number of results – Dominic Apr 22 '17 at 14:49

2 Answers2

3

The syntax looks like T-SQL, meaning it's MS SQL Server.

The best advice I can give you is to normalize your database - get rid of that comma-delimited column and move it to a table.
Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

If you can't do that, you should probably change your current SQL code to something like this:

select  Logs.SingleValue, Instances.list 
from Logs
inner join Instances ON Logs.Column1 = Instances.DeviceNumber 
and (', ' + RTRIM(Instances.slotlist2) + ',') LIKE '%, '+ Logs.Column2 +',%'

This way you should be able to get all the records where slotlist2 has the number in Column2 somewhere in the list.
Note the space after the first comma in both sides of the like operator.

Please also note that I've changed your implicit join to an explicit join.
Since explicit joins have been a part of ANSI SQL for over 25 years now, and every self-respecting RDBMS supports them; there really is no need to use implicit joins anymore.

Edit: I've tested my query, and it seems to be working fine.
You can look at it yourself on rextester.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • I work on CSV and use SQL within R studio. There is no DB but thanks for advice. I have runned modified code. Result looks like the code only considers first values in the list and hance producing 252 rows outcome insted of 5000 expected. – Dominic Apr 22 '17 at 14:57
  • I know nothing about R, but I do know sql. I'm currently reading this on my phone so I might be missing something but the code seems fine to me. I'll try to check againg tomorrow. – Zohar Peled Apr 22 '17 at 18:38
  • Well for me there needs to be loop statment saying (look at first and see if it match if not look at the second and see if ... ) I am not wizard in SQL but it seems it just ignores all but first values in the sequence. (I also assume R is not the case as SQL works ok until this case) I extremly appriciate your commitment ! – Dominic Apr 22 '17 at 19:34
  • So I've set up a small test on rextester, seems to be working fine. You can see the link in my answer. – Zohar Peled Apr 23 '17 at 13:20
  • Thanks for extra effort. It means that there is something wrong on the R package side. I will investigate. – Dominic Apr 23 '17 at 13:44
  • Glad to help :-) – Zohar Peled Apr 23 '17 at 14:16
  • I read "sqldf uses sqlite by default and can also use H2, MySQL and PostgreSQL backends. SQL Server is not supported." so maybe there is something within code native to an SQL server that makes it indigestable to R. Do you know some alternative way of writing code provided so it would suit sqllite ? – Dominic Apr 23 '17 at 14:20
  • Try change the `+` operator to `||` - It's ANSI-SQL string concatenation operator - Sqlite should support it. – Zohar Peled Apr 23 '17 at 14:25
0

Result of most recent Query

So as on the screen shot there seems to be the problem with either a Query or a package within an R studio that lets operate SQL on CSV files without the Data Base.

Kind Regards

Dominik

P.S. Orginal post contains renamed column names just to simplify the case I am after solving.

Dominic
  • 1
  • 4