Help with Select Statement

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Help with Select Statement

Post by Zero-G. » Fri 13 Jul 2007 13:43

Hey
Maybe, you can help me with a Select Statement. I don't know, how to get it work.
Following situation: I have a table called Coat. In this table, there are some predefined values like:

Coat Information
-----------------------------------------
SM Information zur Schicht SM
S Farbe
E Alternativfarbe

OK. - For down compatibility, the coats can compare together. - And I have to split them. - Lets say the coat entered from an employee is 'SM', the problem is not very big. - But when the employee combines two coates like: SME, the problem will be very big.
I tried the following Select Statement: Select Coat from Coat where Instr('SM', Coat)
Then I get the following result
-------------
SM
S
-------------
The problem is, that S is not correct now. - Do you know, how I can examine, the correct Coat?
THX
I use mySQL 4.1 - So Stored Procedures, are not possible

Serious

Post by Serious » Fri 13 Jul 2007 14:42

This problem is not related to MySQLDirect .NET.

Nevertheless, to retrieve rows that contain 'SM' substring in 'Coat' column try to use following statement:

Code: Select all

Select Coat from Coat where Coat like '%SM%'
If this is not your intend, please specify what you need to do in detail.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Sun 15 Jul 2007 08:44

Hey
Thanks for your answer. I do know, that this problem has nothing to do directly with your components. I hope, that you can help me nevertheless.

I try to specify:
In the coat table are the entries for possible coats. The user has a little programm, where he can choose the coats, he needs. - This program writes the coats into one textbox (this is because of down compatibility).
So, the user can specify 1 or more coats to a product. So in the textbox can stand a code like: SME
This would implement two coats (based on the example on my first question). - 1 Coat = SM 2 Coat = E
But even, I told you, that the problem is, that S is also a coat. - But this is not meant from the user.

OK - So, what I have to do (I thought) - I have to check, if the code is plausibly. - So, when I use the following code:
Select Coat from Coat where Instr('SME', Coat)
I will get the following result:
SM
S
E
But this is the point, I don't know further. - And so I thought, you have maybe an idea, how to solve this problem.
THX

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 16 Jul 2007 08:08

We can't understand the physics of your system. What is coat? Jacket or cover or layer or what? Could you explain in other words? Does this table always contain only three entries? If so, this task can probably be solved.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Mon 16 Jul 2007 14:22

Hey!

Ok, I try to explain the whole szenario:
With coat, I mean my Table and coat is similar with coats you can deliver with glasses. (I work for an optician)
So, the coats for the glasses can be choosen in a programm. - They depend on some physics, which I don't wan't to explain closer here.

So, in the table coats there are about 150 possibles coats. Each coat is unique. - The coats themselves have codes(values) like S, SM, E, ZS,....
For example:
ZS means, that the glass will be coated with a non reflicting coat.
E means, that the glass has a sun protection.
S is a special color.
(This codes are given by the manufactor)
And so on.

So, the user can chosse the coats, he want to use on the glass. The only constant is, that there can as maximum 2 coats on one glass!
So, after the user chooses his coat(s), the codes for the coats will be written to a textbox. - like SM, or ZS, OR maybe: ZSE (anti reflecting coat & sun Protection)
As I told you, is the problem, that this textbox has to be in such a style, because of down compatibility.
So, I have to find out by programming, which coats the user has choosen.
And I don't know, how I can solve the problem. - I hoped that you can help me, with some code (for mySQL) or a tip for a prorgramm workaround.

Hope, now you understand better.

THX

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 17 Jul 2007 14:06

The taks is clear enough now. But, unfortunately, we cannot help you on this. You are to implement quite complex logic, which we are not aware of. In particular, we don't know what coats might agree to each other. Probably, there two coats that are not applicable together. Just a hypothesis, you should know this better, but we don't know this at all. Also there might be coats 'Z' or 'M', so that string 'ZSM' is not parsable at all. I hope, you understood me.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Tue 17 Jul 2007 14:17

Hey

Ok - Thanks nevertheless.

One more question: I have send you last week a sample project, where the DataSet Wizzard seems not to work correctly. - Do you have any idea of the status?

THX

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 17 Jul 2007 14:44

I answered you on Wednesday. The message was
Neither dataset has such tables. Please advise.
Could you provide me with step-by-step scenario to reproduce all three
problems?
Probably your mail server blocked my letter. Check this out.
Please create another thread for this problem.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 19 Jul 2007 06:54

I've answered you by e-mail:
None of three problems could be reproduced.
Your dataset is correct.

Post Reply