Truncated incorrect DOUBLE value error using On Instr 1

[Saners]

Hi.

I have the following 2 input tables:

1. tblTempValue has 2 fields: Value (TEXT) and AssetID (BIGINT(20)) and holds the following values:

Value AssetID
1,3,10 90
4 91
8 92
2 93
5 95


2. My lookup tblTextDOB has 2 fields: RaceNumber (VARCHAR(20)) and DateOfBirth (VARCHAR(10)) and holds 100 records (up to RaceNumber 100):

RaceNumber DateOfBirth
1 20/10/1996
2 01/05/1998
3 10/11/1997
2 03/05/1995
4 11/01/1994
5 23/02/1998
6 09/12/1995
7 21/05/1996
8 13/10/1999
9 16/05/1994
10 06/04/1998
--- ---
--- ---
--- ---
100 13/05/1996


3. I have 1 output table – tblTempDOB has 4 fields AttributeID BIGINT(20), RaceNumber (VARCHAR(20)), DOBAssetID BIGINT(20), DateOfBirth (VARCHAR(20)).

4. I run this code. I only want to insert a value on tblTempDOB for each occurrence of Value on tblTempValue:

insert
into tblTempDOB
(AttributeID , RaceNumber , DOBAssetID , DateOfBirth)

Select 42,
RaceNumber,
AssetID ,
DateOfBirth
From tblTempValue
Inner Join tblTextDOB
On Instr(',' + RaceNumber + ',', ',' + Value + ',') > 0

4. I want my output in tblTempDOB to be:

AttributeID RaceNumber DOBAssetID DateOfbirth
42 1 90 20/10/1996
42 3 90 10/11/1997
42 10 90 01/04/1998
42 4 91 11/01/1994
42 8 92 13/10/1999
42 2 93 01/05/1998
42 5 95 23/02/1998


5. What’s happening is that the code is adding a record to tblTempDOB for every record on my lookup table tlbTextDOB. I know this is because of the JOIN but I can’t see what. I am also getting a Truncated incorrect DOUBLE value: ',' - error when running the code.

I will really appreciate any help you can give as it’s getting critical.

Thank you.

Saners.

 

[r937]

concatenation in mysql is achieved with the CONCAT function

inside your INSTR function, you seem to be trying to add strings

by the way, your query will not scale

the more rows you get, the slower the query will be

storing multiple comma-delimited values inside a single column is almost always a really bad idea


r937.com | rudy.ca

 

[Saners]

Thank you r937.com. I am new to MySQl and I think it shows!
Would really appreciate it if you could advise on how to amend my code to achieve the required results.

I agree re multiple comma-delimited values. I inherited the table design.

Thanks.

 

[Saners]

I also wanted to say that inside my INSTR function I am attempting to link the Value field in the first table with the RaceNumber field in the 2nd table to pick up the DateOfBirth field from 2nd table. Can anyone help with the correct syntax?

Thanks again.

 

[Saners]

I have changed the code to:

Select 42,
b.RaceNumber,
a.AssetID ,
b.DateOfBirth
From tblTempValue a
Left Join tblTextDOB b
ON a.Value LIKE concat('%', b.RaceNumber)

This works to a degree but I have just discovered that the data comes in with random spaces and commas

e.g.

Value AssetID
1, 3,10 90
4 91
8 92
2 93
5 95
9,4 , 12 97


To account for all the possible permutations (i.e spaces either side of the commas) I need to write code such as


Select b.RaceNumber,
a.AssetID ,
b.DateOfBirth
From tblTempValue a
Left Join tblTextDOB b
ON a.Value LIKE b.RaceNumber
OR a.Value LIKE concat(b.RaceNumber , ',' , '%')
OR a.Value LIKE concat('%',',' , b.RaceNumber , ',' , '%')
OR a.Value LIKE concat('%', ', ' , b.RaceNumber , ',' , '%')
OR a.Value LIKE concat('%',',' , b.RaceNumber )
OR a.Value LIKE concat('%',', ' , b.RaceNumber )
OR a.Value LIKE concat('%',',' , b.RaceNumber , '%')
OR a.Value LIKE concat('%',', ' , b.RaceNumber , '%')


Again this works to a degree but I am still not getting exact matches. The problem is with the larger numbers in the Value field (see e.g. below). I need to get exact matches. I have tried changing 'LIKE' to '=' but this makes no difference.

e.g.

Value AssetID
21, 213,110 80
1023 ,5 , 28 89


Can you please help with this?

Thanks.

 

[r937]

KEFT OUTER JOIN ...
ON [blue]FIND_IN_SET[/blue](a.Value, b.RaceNumber) > 0


r937.com | rudy.ca

 

[r937]

KEFT ha!

LEFT OUTER JOIN



r937.com | rudy.ca

 

[Saners]

Thank you! I cleaned up the data and that worked perfectly.