find records with only numbers
I hope someone can give me a suggestion on this.
I'd like to find records in a table where a specific field only contains a
number.
For example,
Select Field1 as f
from table1 as t
where lcase(t.f) not like in
('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')
This obviously doesn't work or I wouldn't be asking the question. In this
example field1 is a varchar(25) field and it is normally appropriate for it to
contain data that has numeric characters and alpha characters. Examples of
valid data in this field are '456987','142154','200145C1','954xxx','H 1231','My
Test', ..etc. In my query above I'm trying to find the records where there
is only a six digit numeric value and no others.
Thanks for any advice
before insert trigger
Is it possible for a trigger to test the data to be inserted and if it doesn't meet specific criteria not insert the data at all? I have an application that writes a lot of data to my table. I don't have control over this application and it writes a lot more data then I need. So I'd like to have a trigger that doesn't allow certain records to be written at all. I can't seem to find anything in the documentation that describes what I'm looking for. Thanks
ODBC 3.51.22 problem - please help
I've found a glaring problem with the latest ODBC connector. Data types have been changed and data is no longer being read correctly. I'm running MySQL 5.1.16 on Netware. My apps are VB6 and VBA using ADO. The following query produces different data types depending on the version of the ODBC driver. SELECT ConCat(21000,'-','a') In 3.51.19 it is a VarChar and in 3.51.22 it's a VarBinary. Concat is supposed to return a string. In C a byte array may be fine but in VB a string should be a VarChar. Is this a bug or is there a server or OBDC setting that can be changed to make sure that it always returns a VarChar Thanks for the help
What's up with 5.1x beta
Does anyone have an explanation as to why it's taking so long for an official release of version 5.1? Is it me or does it seem like 5.1 is taking a lot longer to be released. I don't remember previous versions having 20 beta revs before an official release. Is there a real date available for an official release?
Insert Select query problem
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Thanks
Re: Insert Select query problem
Thanks Jay,
I had to make a change to the first part of the query to get the results that I
wanted but your suggestion was definitely what I needed to get to the solution.
Thanks again.
For those that are interested, here's the final solution,
INSERT INTO purchase (Source, Item, Qty)
SELECT 1, totals.Item,if((totals.TotQty -r.Qty)0,r.qty, totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
UNION All
SELECT 0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
On 8/10/07 at 12:33 PM, in message [EMAIL PROTECTED], Jay Pipes [EMAIL
PROTECTED] wrote:
Ed Reed wrote:
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Try this:
INSERT INTO purchase (Source, Item, Qty)
SELECT
1, totals.Item, r.Qty
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty = totals.TotQty
UNION ALL
SELECT
0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
cheers,
Jay
Re: General MySQL Question: Ed Reed (CA, United States of America) Medium
I just didn't enjoy the location. When it was in Orlando a few years ago, it was great. There were plenty of things to do and see; different places to eat every night. I had a really good time. Then the following year I went to Santa Clara and there was nothing to do. I went to the movies one night. Had dinner at Sizzler three times. I drove 45 minutes to find a place I could buy a shirt. The rest of the time I stayed in the hotel and watched crappy tv. If you folks want me to be away from my family and friends for five days you've gotta give me a reason to wanna go besides the great technical information. I didn't go to last years, I'm not going to this years and I probably won't go to any future ones held in Santa Clara. When I came home from the last one, my kids asked what I got them from my trip and I had nothing for them. It was just a boring place to go and I don't wanna go back. I'd just like to see it held someplace new every year. Thanks for replying to my comment Lenz Grimmer [EMAIL PROTECTED] 1/25/07 2:21 AM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ed, thank you for your message! On Thu, 18 Jan 2007, Ed Reed has filled out the MySQL Contact Form with a General MySQL Question: Can someone tell me you folks are going to hold all future User Conferences in Santa Clara? I would like to go this year but I didn't enjoy myself at the last one I went to in Santa Clara in '05 and I don't wanna go back. If you folks plan to hold all future conferences there I'd like to know so I can resign myself to the fact that it's never gonna change instead of hoping that it's gonna be somewhere else every year. To be honest, it's not entirely clear yet if we will change the location for upcoming events. But could you explain why you didn't enjoy yourself at the last one? How can we improve? Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Community Relations Manager, EMEA MySQL GmbH, http://www.mysql.de/, Hamburg, Germany Visit the MySQL Forge at http://forge.mysql.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFFuISeSVDhKrJykfIRAsJxAJ9hLkjhA10B68OHpw97x6QHbSqjLACePQym m9lEE93nxdC+K//cQsL658Q= =W/8N -END PGP SIGNATURE-
Re: General MySQL Question: Ed Reed (CA, United States ofAmerica) Medium
I understand that. I go there for the same thing. My point was that, the conference needs the attendees as much we want the conference. When I go I'm going for the conference stuff but I have a life and they're asking us to give up that life for maybe 6 days. After a the second day things start to get a little monotonous and we need a break. After the third or fourth day the brain is fried. There needs to be some kind of other activity that takes your mind off of the same hotel walls. Let's put it another way. When you go to work do you stay there for 5 days straight? Probably not. You go home, you go out to dinner. You see friends. You do other things to take your mind away from work because you have to recharge your batteries. It's the same thing at the conferences. Unless you're a robot you have to get out and see the sights and be entertained so that you can go back the next day and absorb the next days meetings. I just liked it better when it felt like it was gonna be held at a different location every year. I made this same complaint last year and it never got this much attention. - cheers Joshua J. Kugler [EMAIL PROTECTED] 1/29/07 4:36 PM That's funny...when I go to a technical conference, I usually go for the conference, and couldn't care less if there is other stuff to do in the area during the off hours. j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com ( http://www.eeinternet.com/ ) PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 Ed Reed [EMAIL PROTECTED] 1/29/07 1:57 PM I just didn't enjoy the location. When it was in Orlando a few years ago, it was great. There were plenty of things to do and see; different places to eat every night. I had a really good time. Then the following year I went to Santa Clara and there was nothing to do. I went to the movies one night. Had dinner at Sizzler three times. I drove 45 minutes to find a place I could buy a shirt. The rest of the time I stayed in the hotel and watched crappy tv. If you folks want me to be away from my family and friends for five days you've gotta give me a reason to wanna go besides the great technical information. I didn't go to last years, I'm not going to this years and I probably won't go to any future ones held in Santa Clara. When I came home from the last one, my kids asked what I got them from my trip and I had nothing for them. It was just a boring place to go and I don't wanna go back. I'd just like to see it held someplace new every year. Thanks for replying to my comment Lenz Grimmer [EMAIL PROTECTED] 1/25/07 2:21 AM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ed, thank you for your message! On Thu, 18 Jan 2007, Ed Reed has filled out the MySQL Contact Form with a General MySQL Question: Can someone tell me you folks are going to hold all future User Conferences in Santa Clara? I would like to go this year but I didn't enjoy myself at the last one I went to in Santa Clara in '05 and I don't wanna go back. If you folks plan to hold all future conferences there I'd like to know so I can resign myself to the fact that it's never gonna change instead of hoping that it's gonna be somewhere else every year. To be honest, it's not entirely clear yet if we will change the location for upcoming events. But could you explain why you didn't enjoy yourself at the last one? How can we improve? Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Community Relations Manager, EMEA MySQL GmbH, http://www.mysql.de/, Hamburg, Germany Visit the MySQL Forge at http://forge.mysql.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFFuISeSVDhKrJykfIRAsJxAJ9hLkjhA10B68OHpw97x6QHbSqjLACePQym m9lEE93nxdC+K//cQsL658Q= =W/8N -END PGP SIGNATURE-
Re: New DBManager Released
Yes it is true. I downloaded it and installed it to try and manage my user accounts. In the feature list it says Object Maintenance and User Objects are listed. But when you try to manage a user account I get a message saying that the feature is not available in this version. - enjoy COS [EMAIL PROTECTED] 1/24/07 4:08 AM Hi, Not true, please read http://www.dbtools.com.br/EN/dbmanagerpro/features.php (present in my original post) for details. Best Regards, DBTools Software http://www.dbtools.com.br ( http://www.dbtools.com.br/ ) - Original Message - From: Ed Reed [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com; PostgreSQL Announce List pgsql-announce@postgresql.org; SQlite Users Group sqlite-users@sqlite.org; Firebird Tools [EMAIL PROTECTED] Sent: Tuesday, January 23, 2007 3:20 PM Subject: Re: New DBManager Released The free version is crippled. You don't get all the capabilities they say. COS [EMAIL PROTECTED] 1/23/07 8:16 AM Hi, DBTools Software is proud to announce the availability of the new DBManager Professional Enterprise Edition 3.4.2 and DBManager Standard Edition 3.2.2. For a complete list of changes please check our website at http://www.dbtools.com.br/EN. The new versions are available in our downloads center at http://www.dbtools.com.br/EN/downloads. What is DBTools Manager? DBTools manager is an application for database managementm supporting MySQL, PostgreSQL, Interbase, Firebird, SQlite, DBF Tables, MSAccess, MSSQL Server, Sybase, Oracle and ODBC Datasources. This is a simple list of its features: - Full object management - Query Builder with planning, debugging capabilities - Diagram Designer - Report and Form Builders - Lots of Wizards to import and export data/structure to/from a variety of sources - Database Documenter, Comparer and Migration Wizards - View, Procedure and Function Builder - English and Portuguese BR language resources available - and much more Available in two editions: Enteprise and Standard. The latest can be used for free for personal use. For a complete list of its features check http://www.dbtools.com.br/EN/dbmanagerpro/features.php. Best Regards, DBTools Software http://www.dbtools.com.br ( http://www.dbtools.com.br/ ) ( http://www.dbtools.com.br/ ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New DBManager Released
The free version is crippled. You don't get all the capabilities they say. COS [EMAIL PROTECTED] 1/23/07 8:16 AM Hi, DBTools Software is proud to announce the availability of the new DBManager Professional Enterprise Edition 3.4.2 and DBManager Standard Edition 3.2.2. For a complete list of changes please check our website at http://www.dbtools.com.br/EN. The new versions are available in our downloads center at http://www.dbtools.com.br/EN/downloads. What is DBTools Manager? DBTools manager is an application for database managementm supporting MySQL, PostgreSQL, Interbase, Firebird, SQlite, DBF Tables, MSAccess, MSSQL Server, Sybase, Oracle and ODBC Datasources. This is a simple list of its features: - Full object management - Query Builder with planning, debugging capabilities - Diagram Designer - Report and Form Builders - Lots of Wizards to import and export data/structure to/from a variety of sources - Database Documenter, Comparer and Migration Wizards - View, Procedure and Function Builder - English and Portuguese BR language resources available - and much more Available in two editions: Enteprise and Standard. The latest can be used for free for personal use. For a complete list of its features check http://www.dbtools.com.br/EN/dbmanagerpro/features.php. Best Regards, DBTools Software http://www.dbtools.com.br ( http://www.dbtools.com.br/ ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Administrator problem
Thanks for the reply Daniel, This is not on a Mac. I'm running MySQL Administrator 1.2.8 on a XP/SP2 machine and I'm connecting to a MySQL 5.1.9 server running on Netware6.5. - Still looking for help. Daniel Culver [EMAIL PROTECTED] 1/19/07 9:52 AM Are you working on a Mac? If so, logging in as root is not good enough. You must have set up and log into the root user account of your Mac computer or server. This, if anything is a Apple problem and advantage. The Administrator is opening to the account you are in, the login in window will accept any correct password combination under ShadowHash or other authorising protocol, but you will still be working in the account you are in with those privileges. HTH Daniel Culver [EMAIL PROTECTED] On Jan 17, 2007, at 4:49 PM, Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do.
MySQL Administrator problem
Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do. Any thoughts?
Re: MySQL Administrator problem
There was already a bug report submitted. That's where I found other users that have the same problem. Colin Charles [EMAIL PROTECTED] 1/17/07 6:05 PM Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. Have you reported a bug to bugs.mysql.com ? kind regards -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/
Re: MySQL Administrator problem
I don't have any way of selecting a host. I only have one host on my system and I don't see anything in the program that explicitly shows me where to select the host. Igor Alexandrov [EMAIL PROTECTED] 1/17/07 9:30 PM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do. Any thoughts? Hello! I think, that you haven't chosen a host for user. Choose it and user options will be enabled to edit. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org ( http://enigmail.mozdev.org/ ) iD8DBQFFrwYIn6e/N0YuRmcRAmJuAJ4u50WeNgr+fuTudhwXNk0mvi7VsACgpk1b sNzrOlk/rpVfD8ssw6hukUo= =Z2sQ -END PGP SIGNATURE-
Re: Insert ... Select troubles
Thanks for the Brent, What do you think about trying to make this work by using a stored procedure? A colleague mentioned it to me but I can't seem to get my head wrapped around it yet. - Thanks Brent Baisley [EMAIL PROTECTED] 1/15/07 7:45 AM When you said multiple field unique key, I assumed those two field were your primary key. The way I described in the easiest way to implement it. Especially since you can do future insert/select without having to worry about figureing out sequence numbers for each group. You may want to think if you need two unique indexes. Obviously if you already have related information in place based on the RecID value it would be difficult to switch. You would need to store the value of both fields to setup a relation. I don't think what you want to do can be done in a single query while also being able to add data in a similar manner in the future. Future insert/selects would require first determining the highest number for each group, then incrementing from there. You could create a middle table in structure I described and then run two insert/selects. The first to generate you sequence, then second to populate the main table. - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 12, 2007 8:22 PM Subject: Re: Insert ... Select troubles Thanks for the suggestion Brent. The auto_increment won't work in my situation though. My t1 table has a RecID field that already is my primary key and set to auto_increment. Is there another way that this can be done? Thanks Brent Baisley [EMAIL PROTECTED] 1/12/07 1:10 PM Sine ItemCount is part of your unique key, you should just make ItemCount an auto_increment field. Mysql will then handle providing sequential numbering within the group. For example: CREATE TABLE t1 ( GroupCount int, ItemCount int auto_increment, PRIMARY KEY (GroupCount,ItemCount) ) When you do an insert, leave out the ItemCount. insert into t1 (GroupCount) values (1),(1),(2),(2) The t1 table then looks like this; GroupCount ItemCount 11 12 21 22 The compound primary key causes a difference auto increment sequence to be made for each record group. - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 12, 2007 12:42 PM Subject: Re: Insert ... Select troubles ItemCount is essentially a counter of the records from the select statement. So, every new INSERT ... SELECT statement gets a new GroupCount (the next number up) and ItemCount represents the ID of the items in that new group. Does that make sense? - Thanks Michael Dykman [EMAIL PROTECTED] 1/12/07 8:13 AM On 1/11/07, Ed Reed [EMAIL PROTECTED] wrote: I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. +--+---+--+--+--+ |GroupCount| ItemCount | Field1 |Field2| Field3 | +--+---+--+--+--+ |1 | 1 | data from t2 | data from t2 | data from t2 | |1 | 2 | data from t2 | data from t2 | data from t2 | |1 | 3 | data from t2 | data from t2 | data from t2 | |2 | 1 | data from t2 | data from t2 | data from t2 | |2 | 2 | data from t2 | data from t2 | data from t2 | |3 | 1 | data from t2 | data from t2 | data from t2 | |3 | 2 | data from t2 | data from t2 | data from t2 | |3 | 3 | data from t2 | data from t2 | data from t2 | |3 | 4 | data from t2 | data from t2 | data from t2 | |3 | 5 | data from t2 | data from t2 | data from t2 | |3 | 6 | data from t2 | data from t2 | data from t2 | |4 | 1 | data from t2 | data from t2 | data from t2 | |4 | 2 | data from t2 | data from t2 | data from t2 | |4 | 3 | data from t2 | data from t2 | data from t2 | |4 | 4 | data from t2 | data from t2 | data from t2 | |4 | 5 | data from t2 | data from t2 | data from t2 | |4 | 6 | data from t2 | data from t2 | data from t2 | |4 | 7 | data from t2 | data from t2 | data from t2 | |5 | 1 | data from t2 | data from t2 | data from t2 | |5 | 2 | data from t2 | data from t2 | data
Re: Insert ... Select troubles
ItemCount is essentially a counter of the records from the select statement. So, every new INSERT ... SELECT statement gets a new GroupCount (the next number up) and ItemCount represents the ID of the items in that new group. Does that make sense? - Thanks Michael Dykman [EMAIL PROTECTED] 1/12/07 8:13 AM On 1/11/07, Ed Reed [EMAIL PROTECTED] wrote: I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. +--+---+--+--+--+ |GroupCount| ItemCount | Field1 |Field2| Field3 | +--+---+--+--+--+ |1 | 1 | data from t2 | data from t2 | data from t2 | |1 | 2 | data from t2 | data from t2 | data from t2 | |1 | 3 | data from t2 | data from t2 | data from t2 | |2 | 1 | data from t2 | data from t2 | data from t2 | |2 | 2 | data from t2 | data from t2 | data from t2 | |3 | 1 | data from t2 | data from t2 | data from t2 | |3 | 2 | data from t2 | data from t2 | data from t2 | |3 | 3 | data from t2 | data from t2 | data from t2 | |3 | 4 | data from t2 | data from t2 | data from t2 | |3 | 5 | data from t2 | data from t2 | data from t2 | |3 | 6 | data from t2 | data from t2 | data from t2 | |4 | 1 | data from t2 | data from t2 | data from t2 | |4 | 2 | data from t2 | data from t2 | data from t2 | |4 | 3 | data from t2 | data from t2 | data from t2 | |4 | 4 | data from t2 | data from t2 | data from t2 | |4 | 5 | data from t2 | data from t2 | data from t2 | |4 | 6 | data from t2 | data from t2 | data from t2 | |4 | 7 | data from t2 | data from t2 | data from t2 | |5 | 1 | data from t2 | data from t2 | data from t2 | |5 | 2 | data from t2 | data from t2 | data from t2 | |6 | 1 | data from t2 | data from t2 | data from t2 | |6 | 2 | data from t2 | data from t2 | data from t2 | |6 | 3 | data from t2 | data from t2 | data from t2 | +--+---+--+--+--+ I can figure most of it out with the exception of the ItemCount value. What I have so far is this, INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT (SELECT Max(GroupCount)+1 From t1), ?, Field1, Field2, Field3 FROM t2; The part with the ? is what I can't figure out. Any ideas? I'm not very clear what ItemCount is supposed to represent.. if you restate it, perhaps I can help -- - michael dykman - [EMAIL PROTECTED]
Re: Insert ... Select troubles
Thanks for the suggestion Brent. The auto_increment won't work in my situation though. My t1 table has a RecID field that already is my primary key and set to auto_increment. Is there another way that this can be done? Thanks Brent Baisley [EMAIL PROTECTED] 1/12/07 1:10 PM Sine ItemCount is part of your unique key, you should just make ItemCount an auto_increment field. Mysql will then handle providing sequential numbering within the group. For example: CREATE TABLE t1 ( GroupCount int, ItemCount int auto_increment, PRIMARY KEY (GroupCount,ItemCount) ) When you do an insert, leave out the ItemCount. insert into t1 (GroupCount) values (1),(1),(2),(2) The t1 table then looks like this; GroupCount ItemCount 11 12 21 22 The compound primary key causes a difference auto increment sequence to be made for each record group. - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 12, 2007 12:42 PM Subject: Re: Insert ... Select troubles ItemCount is essentially a counter of the records from the select statement. So, every new INSERT ... SELECT statement gets a new GroupCount (the next number up) and ItemCount represents the ID of the items in that new group. Does that make sense? - Thanks Michael Dykman [EMAIL PROTECTED] 1/12/07 8:13 AM On 1/11/07, Ed Reed [EMAIL PROTECTED] wrote: I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. +--+---+--+--+--+ |GroupCount| ItemCount | Field1 |Field2| Field3 | +--+---+--+--+--+ |1 | 1 | data from t2 | data from t2 | data from t2 | |1 | 2 | data from t2 | data from t2 | data from t2 | |1 | 3 | data from t2 | data from t2 | data from t2 | |2 | 1 | data from t2 | data from t2 | data from t2 | |2 | 2 | data from t2 | data from t2 | data from t2 | |3 | 1 | data from t2 | data from t2 | data from t2 | |3 | 2 | data from t2 | data from t2 | data from t2 | |3 | 3 | data from t2 | data from t2 | data from t2 | |3 | 4 | data from t2 | data from t2 | data from t2 | |3 | 5 | data from t2 | data from t2 | data from t2 | |3 | 6 | data from t2 | data from t2 | data from t2 | |4 | 1 | data from t2 | data from t2 | data from t2 | |4 | 2 | data from t2 | data from t2 | data from t2 | |4 | 3 | data from t2 | data from t2 | data from t2 | |4 | 4 | data from t2 | data from t2 | data from t2 | |4 | 5 | data from t2 | data from t2 | data from t2 | |4 | 6 | data from t2 | data from t2 | data from t2 | |4 | 7 | data from t2 | data from t2 | data from t2 | |5 | 1 | data from t2 | data from t2 | data from t2 | |5 | 2 | data from t2 | data from t2 | data from t2 | |6 | 1 | data from t2 | data from t2 | data from t2 | |6 | 2 | data from t2 | data from t2 | data from t2 | |6 | 3 | data from t2 | data from t2 | data from t2 | +--+---+--+--+--+ I can figure most of it out with the exception of the ItemCount value. What I have so far is this, INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT (SELECT Max(GroupCount)+1 From t1), ?, Field1, Field2, Field3 FROM t2; The part with the ? is what I can't figure out. Any ideas? I'm not very clear what ItemCount is supposed to represent.. if you restate it, perhaps I can help -- - michael dykman - [EMAIL PROTECTED]
Insert ... Select troubles
I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. +--+---+--+--+--+ |GroupCount| ItemCount | Field1 |Field2| Field3 | +--+---+--+--+--+ |1 | 1 | data from t2 | data from t2 | data from t2 | |1 | 2 | data from t2 | data from t2 | data from t2 | |1 | 3 | data from t2 | data from t2 | data from t2 | |2 | 1 | data from t2 | data from t2 | data from t2 | |2 | 2 | data from t2 | data from t2 | data from t2 | |3 | 1 | data from t2 | data from t2 | data from t2 | |3 | 2 | data from t2 | data from t2 | data from t2 | |3 | 3 | data from t2 | data from t2 | data from t2 | |3 | 4 | data from t2 | data from t2 | data from t2 | |3 | 5 | data from t2 | data from t2 | data from t2 | |3 | 6 | data from t2 | data from t2 | data from t2 | |4 | 1 | data from t2 | data from t2 | data from t2 | |4 | 2 | data from t2 | data from t2 | data from t2 | |4 | 3 | data from t2 | data from t2 | data from t2 | |4 | 4 | data from t2 | data from t2 | data from t2 | |4 | 5 | data from t2 | data from t2 | data from t2 | |4 | 6 | data from t2 | data from t2 | data from t2 | |4 | 7 | data from t2 | data from t2 | data from t2 | |5 | 1 | data from t2 | data from t2 | data from t2 | |5 | 2 | data from t2 | data from t2 | data from t2 | |6 | 1 | data from t2 | data from t2 | data from t2 | |6 | 2 | data from t2 | data from t2 | data from t2 | |6 | 3 | data from t2 | data from t2 | data from t2 | +--+---+--+--+--+ I can figure most of it out with the exception of the ItemCount value. What I have so far is this, INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT (SELECT Max(GroupCount)+1 From t1), ?, Field1, Field2, Field3 FROM t2; The part with the ? is what I can't figure out. Any ideas? Thanks
On Duplicate Key Update question
I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks
Re: On Duplicate Key Update question
Sorry for the premature question. I think I figured it out. On Duplicate Key Update Qty=Qty+Values(Qty); I haven't tested it yet but it makes sense that it'll work. Ed Reed [EMAIL PROTECTED] 1/5/07 2:40 PM I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks
Netware 5.1 beta binaries
Can someone tell me what happen to the MySQL 5.1.x-beta for Netware binaries? They aren't in the downloads page anymore. Thanks
Re: Number extraction from a string
Is there anyway to use RegExp in a field parameter? What would be great
is if I could do this,
SELECT Trim(Both RegExp '[a-z]' From value) as value_num, value
FROM num_test
WHERE value REGEXP 'N[1-999]';
The biggest problem is trying to find the position of where the number
starts in the string so that the number by itself can be shown as
returned field.
Chris White [EMAIL PROTECTED] 12/8/06 8:08 AM
On Friday 08 December 2006 01:57, Philip Mather wrote:
You'll need a regex, see these...
http://dev.mysql.com/doc/refman/4.1/en/pattern-matching.html
http://dev.mysql.com/doc/refman/4.1/en/string-comparison-functions.html
http://dev.mysql.com/doc/refman/4.1/en/regexp.html
Yes, you could use a regex and it would work, but if the format of N###
is
persistant and there are no false positives than I'd rather use that
instead
of regexes, which can an intensive operation. Also you might want to
try:
SUBSTR(value,2) instead of LIKE 'N%' just to see how they compare. If
a regex
is required, you could have something like:
SELECT SUBSTRING(value,2) as value_num, value FROM num_test WHERE value
x;
where x is one of the following depending on the situation:
REGEX('N[0-9]+$')
REGEX('N[0-9]+')
REGEX('N[0-9]{3}$')
depends on how specific you want to get really.
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Number extraction from a string
Can someone help me with this? I have a text field that sometimes contains a number preceded with the letter N. So it might look like this A test N60 or N45 someother text or This happened. N122, Then there was this. I need to come up with a Select statement that can show me all the instances where there is a N### value and return the number ### as a separate field. Anyone have a quick and easy solution? Thanks
Re: Number extraction from a string
Thanks for the quick reply Chris.
It's close but it's a little off. Your example also returns all
instances that where the letter N exists in another words as well
Do you know of a way to prevent that?
Thanks again
Chris White [EMAIL PROTECTED] 12/7/06 3:11 PM
On Thursday 07 December 2006 14:57, Ed Reed wrote:
Can someone help me with this?
quick hack, but it works from what you've stated:
mysql create table num_test (id SERIAL PRIMARY KEY, value
VARCHAR(256));
Query OK, 0 rows affected (0.07 sec)
mysql insert into num_test (value) VALUES
('N400'),('400'),('300'),('N500');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql SELECT REPLACE(value,'N','') as value_num, value FROM num_test
WHERE
POSITION('N' IN value);
+---+---+
| value_num | value |
+---+---+
| 400 | N400 |
| 500 | N500 |
+---+---+
2 rows in set (0.00 sec)
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Create stored procedures with Query Browser
Can someone tell me if it is possible to create stored procedures with Query Browser and if it is can you provide an example the works? Thanks
Re: Stored procedure parameters problem
Hi Paul, I've spent the past two days trying to reproduce my problem and I think I may have brought this on myself. I've had been trying to create a procedure with the Query Browser GUI and was unable to do so. So I tried to create them with MySQL-Front and MySQL Control Center and somehow I was able to get the procedure added to the proc table. Once the procedure was created I went into the mysql.proc table with MySQL-Front and modified the values of param_list and body fields to the values I had. So I probably put the strVal VarChar in the param_list without ever going through a Create or Alter Procedure statement. My bad. I'm still not able to create a procedure with Query Browser, which confuses me. I can create one using the command line client but the same commands do not work in Query Browser. That's a problem for another post. Paul DuBois [EMAIL PROTECTED] 12/2/06 10:38 AM At 11:34 AM -0800 12/1/06, Chris White wrote: On Friday 01 December 2006 11:22, Ed Reed wrote: I have a problem/question I'd like to find someone else to verify/answer for me. I'm using MySQL 5.1.09 running on Netware. I've created a stored procedure that has one parameter, Create Procedure MyTest(strVal VarChar) Begin Select Field1, Field2, Field3 From MyTable Where Field3=strVal; End varchar is meant to be variable, so it MUST have a length supplied. If you want the same flexibility without specifying a specific length, use TEXT instead. Hmm ... I don't know. CHAR is equivalent to CHAR(1), but for VARCHAR the length is not optional. For example, if you try to create a table using a length-less VARCHAR, this happens: mysql create table t (c varchar); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 If you are able to create the procedure when no VARCHAR length is given, I think that might be considered a bug. And it's certainly a problem that you get proc table is missing, corrupt, or contains bad data. when attempting to invoke the procedure. Ed, could you file a bug report about this issue at bugs.mysql.com? That way, one of the developers can figure out what's going on and resolve the issue. Thanks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedure parameters problem
I have a problem/question I'd like to find someone else to verify/answer
for me.
I'm using MySQL 5.1.09 running on Netware. I've created a stored
procedure that has one parameter,
Create Procedure MyTest(strVal VarChar)
Begin
Select Field1, Field2, Field3 From MyTable
Where Field3=strVal;
End
When I call the procedure like this,
Call MyTest('test');
Its suppose to return a result set but it fails with an error that the
proc table is missing, corrupt, or contains bad data. But if I create
the procedure like this,
Create Procedure MyTest(strVal VarChar(25))
Begin
Select Field1, Field2, Field3 From MyTable
Where Field3=strVal;
End
and call it the same as before I get the expected result set.
So now the question, should the fact that I specified a VarChar as the
data type indicate that this is supposed to be a variable-length-string
variable with no size limit other than the max size of the data type?
Thanks in advance.
Re: Stored procedure parameters problem
Got it. Thanks Chris White [EMAIL PROTECTED] 12/1/06 11:34 AM On Friday 01 December 2006 11:22, Ed Reed wrote: I have a problem/question I'd like to find someone else to verify/answer for me. I'm using MySQL 5.1.09 running on Netware. I've created a stored procedure that has one parameter, Create Procedure MyTest(strVal VarChar) Begin Select Field1, Field2, Field3 From MyTable Where Field3=strVal; End varchar is meant to be variable, so it MUST have a length supplied. If you want the same flexibility without specifying a specific length, use TEXT instead. -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show tables replacement
Anyone have an idea on this? Thanks Ed Reed [EMAIL PROTECTED] 7/5/06 1:54:25 PM Anyone have an idea on this? Thanks Ed Reed [EMAIL PROTECTED] 6/30/06 2:51:44 PM Opps! Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL 4.1.11 Any other suggestions? Thanks again. Dan Nelson [EMAIL PROTECTED] 6/30/06 2:48:57 PM In the last episode (Jun 30), Ed Reed said: Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. SELECT table_name FROM information_schema.tables; -- Dan Nelson [EMAIL PROTECTED]
Re: Show tables replacement
Thanks for the helpful words. The fact of the matter is that I would love to upgrade but since 5.0x doesn't have all the capabilities of 4.1x, an upgrade is out of the question at this time. So I'm left to looking here for help. Thanks for your time. Anyone else have an idea on this? Jochem van Dieten [EMAIL PROTECTED] 7/6/06 9:47:26 AM On 7/6/06, Ed Reed [EMAIL PROTECTED] wrote: Anyone have an idea on this? Upgrade. Or at least stop repeating the question. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show tables replacement
Anyone have an idea on this? Thanks Ed Reed [EMAIL PROTECTED] 6/30/06 2:51:44 PM Opps! Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL 4.1.11 Any other suggestions? Thanks again. Dan Nelson [EMAIL PROTECTED] 6/30/06 2:48:57 PM In the last episode (Jun 30), Ed Reed said: Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. SELECT table_name FROM information_schema.tables; -- Dan Nelson [EMAIL PROTECTED]
Show tables replacement
Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. Thanks in advance
Re: Show tables replacement
Opps! Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL 4.1.11 Any other suggestions? Thanks again. Dan Nelson [EMAIL PROTECTED] 6/30/06 2:48:57 PM In the last episode (Jun 30), Ed Reed said: Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. SELECT table_name FROM information_schema.tables; -- Dan Nelson [EMAIL PROTECTED]
RE: Return virtual records
Thanks for the response Shawn but there's nothing covert here. I really need a list of partnumbers based on the Sum of that part in the table. My users will be marking off the parts in the list and if there a more than one of a partnumber then it needs to show up in the list more than once. - Thanks again Shawn Green [EMAIL PROTECTED] 4/12/06 12:15:56 PM --- Ed Reed [EMAIL PROTECTED] wrote: Thanks for the response but neither one of the responses I've received does exactly what I need since they don't return multiple rows. Are there any other ideas out there? Thanks Jay Blanchard [EMAIL PROTECTED] 4/7/06 12:37:32 PM [snip] Anyone have an idea on this? Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part# Qty 1254 5 1414 2 14758 1 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() [/snip] Please do not hijack threads, open a new e-mail and send it to the list address. SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254' What you are asking MySQL to do is not a normal request. There are probably better ways to solve your issue than by creating fake or virtual data. As was posted before, what is the real reason you want to auto-generate separate rows of data? If we understood your REAL problem (not your request, we understand that) we could probably help you find a faster, more robust solution than the one you are proposing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
RE: Return virtual records
Thanks for the response but neither one of the responses I've received does exactly what I need since they don't return multiple rows. Are there any other ideas out there? Thanks Jay Blanchard [EMAIL PROTECTED] 4/7/06 12:37:32 PM [snip] Anyone have an idea on this? Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part# Qty 1254 5 1414 2 14758 1 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() [/snip] Please do not hijack threads, open a new e-mail and send it to the list address. SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Return virtual records
Anyone have an idea on this? Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part#Qty 1254 5 1414 2 147581 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() Can someone help me with this? - Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
That's not really what I meant. I know what the function SUM() does. But Sum()
takes an expression and '1' doesn't seem like much of an expression to me. So
what is that 1 equates to and where in the MySQL documentation can I find this
explained.
Thanks again.
Peter Brawley [EMAIL PROTECTED] 4/5/06 10:28 AM
Hi Ed,
Count(1) works just as well. Sum(1) just adds 1 for each row so it's logically
equivalent.
PB
-
Ed Reed wrote: WOW!!! THAT WAS AWESOME!!! Thanks a lot Peter. Ok, so what is
SUM(1)? How is it able to do this? And where can I learn more about it? Thanks
again. Peter Brawley [EMAIL PROTECTED] 4/4/06 10:13:00 PM Ed,e:
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that
I have to make work together and the problemreport table does not reference the
employeeid in the employees table. It was all create about ten years ago and
the data has just always been migrated to the db du jour. I'm currently using
MySQL 4.1x but most importantly I must be able to run the entire query in a
single call. Thanks for the help. OK, that's doable in a subquery, and you can
get the total from SUM(1), so something like ...SELECT IF( SUM(1) = 0,
'', CONCAT( 'You have ', SUM(1), ' Problem
Report', IF(SUM(1) = 1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM ( SELECT
Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName) AND Employees.DateTerminated IS NULL AND
Employees.UserName='User1') AS priorities;PB- Peter Brawley [EMAIL
PROTECTED] 4/4/06 2:35:49 PM Ed,The big slowdown in your query is
likely the join on ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName)) Eek. Does the employees table not have a primary key,
and does the problemreports table not reference that key? If not, I would
think a first priority would be to fix that.Meanwhile, you can simplify your
monster query by writing the user's problem priorities to a temp table, then
doing a simple pivot on priority to generate your sentence. Something like
...DROP TEMPORARY TABLE IF EXISTS ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT
Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open' AND
ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName)
AND Employees.DateTerminated IS NULL AND Employees.UserName='User1'SELECT
COUNT(*) INTO @n FROM ProbSum;SELECT IF( @n = 0, '', CONCAT( 'You
have', @n, 'Problem Report',
IF(@n=1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM
probsum;DROP TEMPORARY TABLE probsum;All this would be easier in a stored
procedure, if you have MySql 5.PB -Ed Reed wrote: Can someone help me
simplify this query please? It's meant to return a single string result that
looks something like this, You have 12 open Problem Reports:
Priorities(High=5, Med=6, Low=1) The relavent columns from the two tables are
Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table:
EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
Return virtual records
Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part#Qty 1254 5 1414 2 147581 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() Can someone help me with this? - Thanks
Re: very long query for such a simple result
WOW!!! THAT WAS AWESOME!!!
Thanks a lot Peter. Ok, so what is SUM(1)? How is it able to do this? And where
can I learn more about it?
Thanks again.
Peter Brawley [EMAIL PROTECTED] 4/4/06 10:13:00 PM
Ed,e: Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff
that I have to make work together and the problemreport table does not
reference the employeeid in the employees table. It was all create about ten
years ago and the data has just always been migrated to the db du jour. I'm
currently using MySQL 4.1x but most importantly I must be able to run the
entire query in a single call. Thanks for the help. OK, that's doable in a
subquery, and you can get the total from SUM(1), so something like ...
SELECT
IF( SUM(1) = 0,
'',
CONCAT( 'You have ',
SUM(1),
' Problem Report',
IF(SUM(1) = 1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM (
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
) AS priorities;
PB
-
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM Ed,The big
slowdown in your query is likely the join on ProblemReports.Responsible =
CONCAT(Employees.FirstName, ' ',Employees.LastName)) Eek. Does the employees
table not have a primary key, and does the problemreports table not reference
that key? If not, I would think a first priority would be to fix
that.Meanwhile, you can simplify your monster query by writing the user's
problem priorities to a temp table, then doing a simple pivot on priority to
generate your sentence. Something like ...DROP TEMPORARY TABLE IF EXISTS
ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT Priority FROM ProblemReports,
Employees WHERE ProblemReports.Status='Open' AND
ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName)
AND Employees.DateTerminated IS NULL AND Employees.UserName='User1'SELECT
COUNT(*) INTO @n FROM ProbSum;SELECT IF( @n = 0, '', CONCAT( 'You
have', @n, 'Problem Report',
IF(@n=1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM
probsum;DROP TEMPORARY TABLE probsum;All this would be easier in a stored
procedure, if you have MySql 5.PB -Ed Reed wrote: Can someone help me
simplify this query please? It's meant to return a single string result that
looks something like this, You have 12 open Problem Reports:
Priorities(High=5, Med=6, Low=1) The relavent columns from the two tables are
Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table:
EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found
in this incoming message.Checked by AVG Free Edition.Version: 7.1.385 / Virus
Database: 268.3.5/300 - Release Date: 4/3/2006No virus found in this
incoming message.Checked by AVG
very long query for such a simple result
Can someone help me simplify this query please? It's meant to return a single
string result that looks something like this,
You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)
The relavent columns from the two tables are
Table: ProblemReports
Fields: PRNo, Status, Priority, Responsible
Table: Employees
Fields: Firstname, Lastname, DateTerminated, UserName
Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0,
If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ',
(Select Priority From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))),
Concat('You have ', @a, ' open Problem Reports: Priorities(',
(Select ConCat('High=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='High')),', ',
(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='Med')),', ',
(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='Low')),')')),'');
Re: very long query for such a simple result
Sorry, here's the results. BTW the query works it just seems overly complex and I'd like to streamline it. CREATE TABLE `employees` ( `EmployeeID` int(11) NOT NULL auto_increment, `FirstName` varchar(50) default NULL, `LastName` varchar(50) default NULL, `DateTerminated` datetime default NULL, `UserName` varchar(15) default NULL, PRIMARY KEY (`EmployeeID`) UNIQUE KEY `EmployeeID` (`EmployeeID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `problemreports` ( `PRNo` double NOT NULL auto_increment, `Status` varchar(10) default NULL, `Responsible` varchar(20) default NULL, `Priority` varchar(10) default NULL, PRIMARY KEY (`PRNo`), UNIQUE KEY `PRNo` (`PRNo`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 [EMAIL PROTECTED] 4/4/06 1:56:10 PM You are new at this, aren't you? First thing you need to do is to let us see exactly what we need to deal with. Please respond with the results of the following two commands SHOW CREATE TABLE Employees\G SHOW CREATE TABLE ProblemReports\G We (the list members) will be able to help straighten you out from there (there are lots of things we need to talk about but I think that we should get you working first, ok?) Always CC the list (or hit the REPLY TO ALL button or whatever you have in your email client). That way everyone on the list stays informed of the progress of this issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: very long query for such a simple result
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that
I have to make work together and the problemreport table does not reference the
employeeid in the employees table. It was all create about ten years ago and
the data has just always been migrated to the db du jour. I'm currently using
MySQL 4.1x but most importantly I must be able to run the entire query in a
single call.
Thanks for the help.
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM
Ed,
The big slowdown in your query is likely the join on
ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName))
Eek. Does the employees table not have a primary key, and does the
problemreports table not reference that key? If not, I would think a first
priority would be to fix that.
Meanwhile, you can simplify your monster query by writing the user's problem
priorities to a temp table, then doing a simple pivot on priority to generate
your sentence. Something like ...
DROP TEMPORARY TABLE IF EXISTS ProbSum;
CREATE TEMPORARY TABLE ProbSum
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
SELECT COUNT(*) INTO @n FROM ProbSum;
SELECT
IF( @n = 0,
'',
CONCAT( 'You have',
@n,
'Problem Report',
IF(@n=1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM probsum;
DROP TEMPORARY TABLE probsum;
All this would be easier in a stored procedure, if you have MySql 5.
PB
-
Ed Reed wrote:
Can someone help me simplify this query please? It's meant to return a single
string result that looks something like this, You have 12 open Problem
Reports: Priorities(High=5, Med=6, Low=1) The relavent columns from the two
tables are Table: ProblemReportsFields: PRNo, Status, Priority, Responsible
Table: EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's
the query Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found
in this incoming message.Checked by AVG Free Edition.Version: 7.1.385 / Virus
Database: 268.3.5/300 - Release Date: 4/3/2006
Re: Migration toolkit
YIKES! :) - Thanks Shawn [EMAIL PROTECTED] 2/14/06 10:08:14 AM I haven't tried the migration toolkit so I don't know what it can or cannot do. I am assuming that you have hand-transferred a few users from your old system to the new one. What I can suggest is that you generate two sets of data. The first is a list of your users, their hostnames, and their password hashes from your old server: CREATE TABLE oldUsers SELECT user, host, password FROM mysql.user; You can use the utility mysqldump to convert oldUsers table to a SQL script. Save this dump into a text file (oldusers.sql) for later. Next you need to run a bunch of SHOW GRANTS for statements. A script (pick your favorite scripting language for this) can crawl through oldUsers (just created) and capture the results of SHOW GRANTS FOR 'user'@'host' for every user in the oldUsers table. Save the results into another text file (oldgrants.sql). These will be the SQL statements you will need to restore permissions to your current users in your new database. Now comes the fun part: I prefer to enter the CLI and navigate to the correct database by hand before executing scripts like these (I have seen many people just do this from the command line but I would rather be sure). On your new server, start your mysql CLI and navigate to the mysql database. Once there, execute the script that generates the oldUsers table. The sequence should look something like this: mysql -u yourlogin -p mysql provide your password mysql source full_path_to_oldusers.sql That should create a table of all of your user accounts in the table oldUsers in the mysql database of your new server. Bulk insert them into your users table like this INSERT IGNORE user (user, host, password) SELECT user, host, password FROM oldUsers; And refresh the permissions cache: FLUSH PRIVELEGES; Now you are ready to re-apply privileges. Assuming that you correctly captured the GRANT statements from your SHOW GRANTS for script, you should be able to say. mysql source full_path_to_oldgrants.sql and do one last FLUSH PRIVILEGES; Your old accounts should now exist on your new server with their old permissions restored. Sorry but you asked for any ideas... ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Reed [EMAIL PROTECTED] wrote on 02/14/2006 12:11:05 PM: Does anyone have any idea on this one? - Thanks Ed Reed [EMAIL PROTECTED] 2/10/06 3:09 PM I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Re: Migration toolkit
Does anyone have any idea on this one? - Thanks Ed Reed [EMAIL PROTECTED] 2/10/06 3:09 PM I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Migration toolkit
I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 system. It doesn't appear that it can successfully migrate my usernames and privileges. Is it supposed to be able to and is there anything special I need to do to make it happen? - Thanks
Re: Converting decimal to binary
Can you (or anyone else) explain to me how, or point me somewhere that I can learn how this works? I'd really like to know more about how bitwise arithmetic works. Thanks Francesco Riosa [EMAIL PROTECTED] 1/10/06 4:58:47 PM Francesco Riosa wrote: And another one is (in inverse order for laziness): select (8 1) AS `0` , (8 2 1) AS `1` , (8 4 1) AS `2` , (8 8 1) AS `3` , (8 16 1) AS `4` , (8 32 1) AS `5` , (8 64 1) AS `6` , (8 128 1) AS `7` ; but this one looks better: select (8 1) AS `0` , (8 1 1) AS `1` , (8 2 1) AS `2` , (8 3 1) AS `3` , (8 4 1) AS `4` , (8 5 1) AS `5` , (8 6 1) AS `6` , (8 7 1) AS `7` ; http://dev.mysql.com/doc/refman/4.1/en/bit-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting decimal to binary
Actually please ignore my previous question. I just had a brain cramp. Thanks Ed Reed [EMAIL PROTECTED] 1/18/06 11:34:11 AM Can you (or anyone else) explain to me how, or point me somewhere that I can learn how this works? I'd really like to know more about how bitwise arithmetic works. Thanks Francesco Riosa [EMAIL PROTECTED] 1/10/06 4:58:47 PM Francesco Riosa wrote: And another one is (in inverse order for laziness): select (8 1) AS `0` , (8 2 1) AS `1` , (8 4 1) AS `2` , (8 8 1) AS `3` , (8 16 1) AS `4` , (8 32 1) AS `5` , (8 64 1) AS `6` , (8 128 1) AS `7` ; but this one looks better: select (8 1) AS `0` , (8 1 1) AS `1` , (8 2 1) AS `2` , (8 3 1) AS `3` , (8 4 1) AS `4` , (8 5 1) AS `5` , (8 6 1) AS `6` , (8 7 1) AS `7` ; http://dev.mysql.com/doc/refman/4.1/en/bit-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory| product a| product b| product c 2006-01-02| 100| 50| 25 2006-01-03| NULL| 45| NULL 2006-01-05| 78| NULL| 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date| product a| pa_up| product b| pb_up| product c | pc_up 2006-01-02| 2006-01-03| 100| TRUE| 50| TRUE| 25 | TRUE 2006-01-03| 2006-01-05| 100| FALSE| 45| TRUE| 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE| 45| FALSE| 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal number to binary and have the result be returned as a separate field for each bit? For example, what I'd like to do is, Select ConvertToBin(245); And have a result that looked like this +---+---+---+---+---+---+---+---+ | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 | +---+---+---+---+---+---+---+---+ | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 | +---+---+---+---+---+---+---+---+ - Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Thanks Gordon and Bill but this has one big problem
If my decimal number is 8 the result ends up,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 0 | 0 | | | | |
+---+---+---+---+---+---+---+---+
when what i really need is,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
+---+---+---+---+---+---+---+---+
Any thoughts?
Gordon Bruce [EMAIL PROTECTED] 1/10/06 1:44 PM
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
- MID(CONV('A5',16,2),2,1) AS `6`,
- MID(CONV('A5',16,2),3,1) AS `5`,
- MID(CONV('A5',16,2),4,1) AS `4`,
- MID(CONV('A5',16,2),5,1) AS `3`,
- MID(CONV('A5',16,2),6,1) AS `2`,
- MID(CONV('A5',16,2),7,1) AS `1`,
- MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
Bill Dodson
Parkline, Inc. http://www.parkline.com
phone: 304-586-2113 x149
fax: 304-586-3842
email: [EMAIL PROTECTED]
Email Disclaimer
The information in any email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to the email
message by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or
any action or omission taken by you in reliance on it, is prohibited and
may be unlawful. If you have received an email message in error, please
notify the sender immediately by email, facsimile or telephone and
return and/or destroy the original message.
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Well I solved the problem by using LPAD but it would be nice if there was a
more elegant way of handling this problem.
Thanks for the help.
Ed Reed [EMAIL PROTECTED] 1/10/06 2:52 PM
Thanks Gordon and Bill but this has one big problem
If my decimal number is 8 the result ends up,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 0 | 0 | | | | |
+---+---+---+---+---+---+---+---+
when what i really need is,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
+---+---+---+---+---+---+---+---+
Any thoughts?
Gordon Bruce [EMAIL PROTECTED] 1/10/06 1:44 PM
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
- MID(CONV('A5',16,2),2,1) AS `6`,
- MID(CONV('A5',16,2),3,1) AS `5`,
- MID(CONV('A5',16,2),4,1) AS `4`,
- MID(CONV('A5',16,2),5,1) AS `3`,
- MID(CONV('A5',16,2),6,1) AS `2`,
- MID(CONV('A5',16,2),7,1) AS `1`,
- MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
Bill Dodson
Parkline, Inc. http://www.parkline.com
phone: 304-586-2113 x149
fax: 304-586-3842
email: [EMAIL PROTECTED]
Email Disclaimer
The information in any email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to the email
message by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or
any action or omission taken by you in reliance on it, is prohibited and
may be unlawful. If you have received an email message in error, please
notify the sender immediately by email, facsimile or telephone and
return and/or destroy the original message.
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Can someone tell me why this fails?
Can someone tell me why this fails? I'm using v4.1.11 Select IF(((Select 1+1) Null) and ((Select 1+1) 0), (Select 1+1), 'WRONG') I would expect a result of 2. - Thanks
Re: Can someone tell me why this fails?
Thanks Peter, for the quick reply. Peter Brawley [EMAIL PROTECTED] 1/4/06 11:44 AM Ed, Can someone tell me why this fails? I'm using v4.1.11 Select IF(((Select 1+1) Null) and ((Select 1+1) 0), (Select 1+1), 'WRONG') I would expect a result of 2. NULL is neither equal nor unequal to anything including itself. To get the result you expect, write Select IF(((Select 1+1) IS NOT Null) and ((Select 1+1) 0), (Select 1+1), 'WRONG'); PB
Re: Easy or not so easy GROUP BY
Can anyone please help me with this? I'm just not getting it. - Thanks Ed Reed [EMAIL PROTECTED] 10/6/05 2:50:46 PM Thanks for the reply, Sorry; I'm using 4.1.11 Thanks again. Pooly [EMAIL PROTECTED] 10/6/05 1:51 AM 2005/10/6, Ed Reed [EMAIL PROTECTED] : I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8 | 5 | NULL | +++--+ | 8 | 4 | a | +++--+ | 8 | 6 | NULL | +++--+ | 8 | 6 | a | +++--+ | 8 | 5 | a | +++--+ | 8 | 4 | b | +++--+ | 9 | 1 | NULL | +++--+ | 9 | 2 | NULL | +++--+ | 9 | 1 | a | +++--+ | 9 | 3 | NULL | +++--+ | 9 | 2 | a | +++--+ | 9 | 1 | b | +++--+ | 9 | 4 | NULL | +++--+ | 9 | 4 | a | +++--+ | 9 | 2 | b | +++--+ | 9 | 1 | c | +++--+ | 10 | 1 | NULL | +++--+ | 10 | 1 | a | +++--+ | 10 | 2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b | +---+ | 8-5a | +---+ | 8-6a | +---+ | 9-1c | +---+ | 9-2b | +---+ | 9-3 | +---+ | 9-4a | +---+ | 10-1a | +---+ | 10-2 | +---+ - Thanks in advance So, Max group by month/item ? http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html and for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html Since we don't know your MySQL version, I can't give you a precise answer. HIMH -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy or not so easy GROUP BY
Can anyone please help me with this? I'm just not getting it. - Thanks Ed Reed [EMAIL PROTECTED] 10/6/05 2:50:46 PM Thanks for the reply, Sorry; I'm using 4.1.11 Thanks again. Pooly [EMAIL PROTECTED] 10/6/05 1:51 AM 2005/10/6, Ed Reed [EMAIL PROTECTED] : I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8 | 5 | NULL | +++--+ | 8 | 4 | a | +++--+ | 8 | 6 | NULL | +++--+ | 8 | 6 | a | +++--+ | 8 | 5 | a | +++--+ | 8 | 4 | b | +++--+ | 9 | 1 | NULL | +++--+ | 9 | 2 | NULL | +++--+ | 9 | 1 | a | +++--+ | 9 | 3 | NULL | +++--+ | 9 | 2 | a | +++--+ | 9 | 1 | b | +++--+ | 9 | 4 | NULL | +++--+ | 9 | 4 | a | +++--+ | 9 | 2 | b | +++--+ | 9 | 1 | c | +++--+ | 10 | 1 | NULL | +++--+ | 10 | 1 | a | +++--+ | 10 | 2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b | +---+ | 8-5a | +---+ | 8-6a | +---+ | 9-1c | +---+ | 9-2b | +---+ | 9-3 | +---+ | 9-4a | +---+ | 10-1a | +---+ | 10-2 | +---+ - Thanks in advance So, Max group by month/item ? http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html and for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html Since we don't know your MySQL version, I can't give you a precise answer. HIMH -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy or not so easy GROUP BY
Thanks for the reply, Sorry; I'm using 4.1.11 Thanks again. Pooly [EMAIL PROTECTED] 10/6/05 1:51 AM 2005/10/6, Ed Reed [EMAIL PROTECTED] : I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8 | 5 | NULL | +++--+ | 8 | 4 | a | +++--+ | 8 | 6 | NULL | +++--+ | 8 | 6 | a | +++--+ | 8 | 5 | a | +++--+ | 8 | 4 | b | +++--+ | 9 | 1 | NULL | +++--+ | 9 | 2 | NULL | +++--+ | 9 | 1 | a | +++--+ | 9 | 3 | NULL | +++--+ | 9 | 2 | a | +++--+ | 9 | 1 | b | +++--+ | 9 | 4 | NULL | +++--+ | 9 | 4 | a | +++--+ | 9 | 2 | b | +++--+ | 9 | 1 | c | +++--+ | 10 | 1 | NULL | +++--+ | 10 | 1 | a | +++--+ | 10 | 2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b | +---+ | 8-5a | +---+ | 8-6a | +---+ | 9-1c | +---+ | 9-2b | +---+ | 9-3 | +---+ | 9-4a | +---+ | 10-1a | +---+ | 10-2 | +---+ - Thanks in advance So, Max group by month/item ? http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html and for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html Since we don't know your MySQL version, I can't give you a precise answer. HIMH -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Easy or not so easy GROUP BY
I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8|5 | NULL | +++--+ | 8|4 | a | +++--+ | 8|6 | NULL | +++--+ | 8|6 | a | +++--+ | 8|5 | a | +++--+ | 8|4 | b | +++--+ | 9|1 | NULL | +++--+ | 9|2 | NULL | +++--+ | 9|1 | a | +++--+ | 9|3 | NULL | +++--+ | 9|2 | a | +++--+ | 9|1 | b | +++--+ | 9|4 | NULL | +++--+ | 9|4 | a | +++--+ | 9|2 | b | +++--+ | 9|1 | c | +++--+ | 10 |1 | NULL | +++--+ | 10 |1 | a | +++--+ | 10 |2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b| +---+ | 8-5a| +---+ | 8-6a| +---+ | 9-1c| +---+ | 9-2b| +---+ | 9-3 | +---+ | 9-4a| +---+ | 10-1a | +---+ | 10-2| +---+ - Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP - Group_Concat broken after update
That's not true. Group_Concat can already return more than 6meg. It's set by group_concat_max_len variable. The fact is that I've been told there are security issues corrected in the 4.1.14a update and I can't apply them because it will break my already existing apps. Is there a fix? - Thanks Dan Nelson [EMAIL PROTECTED] 9/27/05 11:54:35 PM Probably to allow for a result longer than 255 characters, I guess. -- Dan Nelson [EMAIL PROTECTED]
Re: MySQL Query Browser
I think Control Center is a much better tool then the combination of Query Browser and Administrator. I still use Control Center for my day to day stuff and only check out Query Browser when new versions come out to see if they've solved the major problems. I think they've gone completely in the wrong direction with these tools and I even told members of MySQL-AB at this years user conference. I gave a written list of suggestions and not a single one of them has been addressed in the program. It's definitely not an intuitive application and could be so much better if they'd just take the best ideas from all the other tools that are already out here. I don't think they've done that; it seems as if they've created this thing based on the way they _want_ to use it and not the way users _need_ to use it. Pooly [EMAIL PROTECTED] 9/30/05 8:57:04 AM 2005/9/30, Sujay Koduri [EMAIL PROTECTED] : I am not saying MysQL Query Browser is anyway bad or inferior, but as for my experience sqlyog is very good. There is a free version which you can use for executing SQL queries, ofcourse you will be stripped of some advanced features. You can run multiple queries at once using shift+F5. I suggest you to try this out once. the no longer supported Mysql Control Center, can do it as well. It supports single/multiple query without tricks. You can even directly edit your dataset (and not do the annoying : edit+ do your stuff + apply changes of MysqlQuery Browser ) sujay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 7:14 PM To: Rob Agar Cc: 'Mysql ' Subject: RE: MySQL Query Browser Rob Agar [EMAIL PROTECTED] wrote on 09/29/2005 08:27:37 PM: hi Scott How do I run more than 1 queries in MySQL Query Browser? The only way I've found is to put the queries in a .sql file and load it via File Open Script. It doesn't accept multiple typed in queries, even if they are separated by semicolons. hth Rob He can just start a new script tab, can't he? That's how I do it. I don't know why they have two different types of tabs (one for multi-statements and one for single statements) but they do. File - New Script Tab Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP - Group_Concat broken after update
I meant 600k not 6meg. Sorry Ed Reed [EMAIL PROTECTED] 9/30/05 9:46:32 AM That's not true. Group_Concat can already return more than 6meg. It's set by group_concat_max_len variable. The fact is that I've been told there are security issues corrected in the 4.1.14a update and I can't apply them because it will break my already existing apps. Is there a fix? - Thanks Dan Nelson [EMAIL PROTECTED] 9/27/05 11:54:35 PM Probably to allow for a result longer than 255 characters, I guess. -- Dan Nelson [EMAIL PROTECTED]
Re: HELP - Group_Concat broken after update
Will someone please respond to this? - Ed Ed Reed [EMAIL PROTECTED] 9/22/05 1:30 PM Bump! Ed Reed [EMAIL PROTECTED] 9/21/05 2:09:58 PM I just upgraded from 4.1.11 to 4.1.14a and now Group_Concat returns garbage. In the previous version Group_Concat returned a text string and after the upgrade they are returning a blob.
Re: HELP - Group_Concat broken after update
Bump! Ed Reed [EMAIL PROTECTED] 9/21/05 2:09:58 PM I just upgraded from 4.1.11 to 4.1.14a and now Group_Concat returns garbage. In the previous version Group_Concat returned a text string and after the upgrade they are returning a blob.
HELP - Group_Concat broken after update
I just upgraded from 4.1.11 to 4.1.14a and now Group_Concat returns garbage. In the previous version Group_Concat returned a text string and after the upgrade they are returning a blob.
Simple Count Query
I know this has to be a simple query but its really kickin' my butt. I have the table below where fld1 is the year, fld2 is a number, fld 3 is a subnumber, and fld4 is the user. I need to know the count of all the records for user am without the sub number getting in the way. For example, the first record for user am shows in the 3rd year number 1 with two sub records was for user am . That needs to be counted as one item. So when all the items are counted I should have a total of 5 items for user am and not 17 like you'd normally get Any thoughts? thanks +--+--+--+--+ | fld1 | fld2 | fld3 | fld4 | +--+--+--+--+ | 3 | 1 | a | am | | 3 | 1 | b | am | | 3 | 2 | a | am | | 3 | 3 | a | pm | | 3 | 3 | b | pm | | 3 | 3 | c | pm | | 4 | 1 | a | pm | | 4 | 2 | a | pm | | 4 | 3 | a | am | | 4 | 3 | b | am | | 4 | 3 | c | am | | 4 | 3 | d | am | | 4 | 3 | e | am | | 4 | 3 | f | am | | 4 | 4 | a | am | | 4 | 4 | b | am | | 5 | 1 | a | pm | | 5 | 1 | b | pm | | 5 | 1 | c | pm | | 5 | 1 | d | pm | | 5 | 1 | e | pm | | 5 | 2 | a | am | | 5 | 2 | b | am | | 5 | 2 | c | am | | 5 | 2 | d | am | | 5 | 2 | e | am | | 5 | 2 | f | am | +--+--+--+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert Into problem
Can anyone please tell me why this fails, I've done this type of thing before without a problem Insert Into tbl1 Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114 tbl1 has five fields all ints with fld5 being the autoincrement index. I've also tried it as, Insert Into tbl1 (fld1, fld2, fld3, fld4) Values(Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114) I think its obvious what I want but just in case, I want to duplicate all the records where fld1 matches my value and assign all the duplicate records a new value for fld1. When I do it the first way I get a Column Count doesn't match value count at row 1 error. When I do it the second way I just get a syntax error. Thanks!
Re: Insert Into problem
Thanks guys! [EMAIL PROTECTED] 8/30/05 1:22 PM The correct syntax for the second method doesn't use VALUES(). I would always use this form if I were going to exclude any columns from receiving data. It's just safer and relies less on the SQL engine to correctly divine what it is you want to do. Insert Into tbl1 (fld1, fld2, fld3, fld4) Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eamon Daly [EMAIL PROTECTED] 8/30/05 1:25 PM Drop the VALUES part in your second attempt: INSERT INTO tbl1 (fld1, fld2, fld3, fld4) SELECT 10306, fld2, fld3, fld4 FROM tbl1 WHERE fld1 = 8114 Note that you'll need to be running MySQL 4.0.14 or above to insert into the same table you're selecting from. Eamon Daly
Group Summary by Month
I have a table that contains all the part number and cost transactions over time. I wanna get a get the summary cost of each part number for each month. For example, when I supply a single part number to this query the results should be something like this. +--+--+ | Month | Cost | +--+--+ | Oct 04 | 19521.00 | | Nov 04 | 854.00 | | Dec 04 | 191.00 | | Jan 05 | 7015.00 | | Feb 05 |21.00 | | Mar 05 |25.00 | | Apr 05 | 13571.00 | | May 05 | 1015.00 | +--+--+ Can anyone point me in a direction to start on this?
Re: How to do in one SELECT
select USERS.Name, Count(WINS.user_id) From USERS inner join WINS on WINS.user_id = USERS.id Group By USERS.Name René Fournier [EMAIL PROTECTED] 7/29/05 4:40 PM Let's say I have two tables: USERS id name 1 John 2 Mary 3 Sue 4 Paul 5 David WINS id user_id 1 2 2 4 3 3 4 5 5 1 6 4 7 4 8 2 9 3 10 1 How canin one SELECT statementfetch and display all the users, along with the number of games they each one, e.g.: User Games Won John 2 Mary 2 Sue 2 Paul 3 David 1 Is this a job for Subselects? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
I do this all the time and it works flawlessly. Just like your example and even
more extreme. I use this technique to provide search mechamisms for my
applications. ex,
SELECT ID
FROM table
WHERE concat(field1, field2, field3,...{all the fields in the table}) Like
'%searchstring%';
This works great for me. Is 'user' your actually table name and is it possible
that the table name is being confused with the 'user' table in the MySQL
database?
Good Luck
Matt Babineau [EMAIL PROTECTED] 6/23/05 1:50:12 PM
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Subselect in an Update query
Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks
Re: Subselect in an Update query
Sorry, I thought it was easy to understand. I wanna update a field in a table with a value from the same field but from a different record of the same table. For example, using the query in my original message, +--+ |RecID |FIELD1 |FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value| +--+ | 3 | GHI | Another Value | +--+ | 4 | JKL | More Values | +--+ Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; This query should set FIELD1 of Record 3 to 'DEF' +--+ |RecID |FIELD1 |FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value| +--+ | 3 | DEF | Another Value | +--+ | 4 | JKL | More Values | +--+ That's it! Should be easy but I get an error that says You can't specify target table 'table1' for update in FROM clause - Thanks [EMAIL PROTECTED] 6/17/05 11:03:40 AM Ed Reed [EMAIL PROTECTED] wrote on 06/17/2005 01:35:40 PM: Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks OK, your example must be broken (or it wouldn't be here) and since that's all you posted I have a hard time divining your intent. Would you please provide some sample data and explain what it is you want to do _in words_? I can't speak for everyone but I know it would sure help me figure out what you want to do. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Ordinal number within a table
Is there way to return the ordinal position of a value within a table? Let's say I have a table of phone numbers. Over time the table has had additions and deletions. The table has an autonumber ID field. If I sort by the ID field I'd like to know what position the number '555-1212' is in the table. Any thoughts?
Re: Subselect in an Update query
Thanks [EMAIL PROTECTED] 6/17/05 2:03:02 PM Hi, There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. this phrase is from http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html solution * create table t as Select field1 From table1 Where field2=Some Value Update table1 Set field1=(Select field1 From t Where field2=Some Value) Where field2 =Another Value; ** mysql select * from upd; +--+--+ | a | b | +--+--+ | 1 | one | | 1 | two | | 2 | one | | 2 | two | | 1 | Un | +--+--+ 5 rows in set (0.09 sec) mysql create table t as select * from upd where b='Un'; mysql update upd set a=(select a from t where b='Un') where b='one'; mysql select * from upd; +--+--+ | a | b | +--+--+ | 1 | one | | 1 | two | | 1 | one | changed | 2 | two | | 1 | Un | +--+--+ 5 rows in set (0.00 sec) Mathias Selon Ed Reed [EMAIL PROTECTED] : Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect in an Update query
Thanks for the reply. What do you mean by 'self join'? Eric Bergen [EMAIL PROTECTED] 6/17/05 4:39:28 PM Could you accomplish this with an update and self join? Ed Reed wrote: Sorry, I thought it was easy to understand. I wanna update a field in a table with a value from the same field but from a different record of the same table. For example, using the query in my original message, +--+ |RecID |FIELD1 | FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value | +--+ | 3 | GHI | Another Value | +--+ | 4 | JKL | More Values | +--+ Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; This query should set FIELD1 of Record 3 to 'DEF' +--+ |RecID |FIELD1 | FIELD2 | +--+ | 1 | ABC | A Value | +--+ | 2 | DEF | Some Value | +--+ | 3 | DEF | Another Value | +--+ | 4 | JKL | More Values | +--+ That's it! Should be easy but I get an error that says You can't specify target table 'table1' for update in FROM clause - Thanks [EMAIL PROTECTED] 6/17/05 11:03:40 AM Ed Reed [EMAIL PROTECTED] wrote on 06/17/2005 01:35:40 PM: Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks OK, your example must be broken (or it wouldn't be here) and since that's all you posted I have a hard time divining your intent. Would you please provide some sample data and explain what it is you want to do _in words_? I can't speak for everyone but I know it would sure help me figure out what you want to do. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
difficulty with UCASE and UPPER
Is there anyway to force UCASE and UPPER to convert the alpha characters of a string even if there's a number in the string somewhere? I have a field that contains large amounts of user entered text. I want to convert the whole thing to upper case so I can do a case insensitive compare against it. But if the user happens to put a numeric value within the text somewhere then UCASE and UPPER both fail to convert the text to upper case. Is there a way to force it to do so for the alpha characters? Thanks
Re: User Variables
Does anyone else have any ideas about this topic?
Thanks
Ed Reed [EMAIL PROTECTED] 04/23/05 1:29 pm
Thanks for the reply,
I realize that user variables disappear when the connection closes but I don't
understand why what I'm trying to accomplish can't be done. Doesn't it make
since that if you can load a single file with multiple SQL commands and have
that work succesfully then you should be able to have a single call with
multple SQL commands work just as succesfully? Is there any way to do what I
asked in my original post?
Thanks again for the reply.
Paul DuBois [EMAIL PROTECTED] 4/23/05 12:12:32 PM
At 22:04 -0700 4/22/05, Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection.
All my apps are in VB6 and VBA. They all take a query, open a
connection, run query, fill array from query results, close
connection and pass back the array. Because of backward
compatibility there's no way I can change them to do otherwise.
User variables disappear when the connection closes.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error
as the Query Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables
Thanks for the reply,
I realize that user variables disappear when the connection closes but I don't
understand why what I'm trying to accomplish can't be done. Doesn't it make
since that if you can load a single file with multiple SQL commands and have
that work succesfully then you should be able to have a single call with
multple SQL commands work just as succesfully? Is there any way to do what I
asked in my original post?
Thanks again for the reply.
Paul DuBois [EMAIL PROTECTED] 4/23/05 12:12:32 PM
At 22:04 -0700 4/22/05, Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection.
All my apps are in VB6 and VBA. They all take a query, open a
connection, run query, fill array from query results, close
connection and pass back the array. Because of backward
compatibility there's no way I can change them to do otherwise.
User variables disappear when the connection closes.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error
as the Query Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: I need help with a Select Query
Try this SELECT * FROM applications LEFT JOIN rejected ON application.appid = rejected.appid WHERE rejected.appid Is Null ORDER BY application.appid DESC; Wade [EMAIL PROTECTED] 4/23/05 3:12:37 PM 04232005 1707 GMT-6 I need to do something like this: $sql = SELECT * FROM application WHERE appid != rejected.appid ORDER BY appid DESC; I want to select all from application where the appid is not located in the table rejected. Im having trouble getting this to work. Can somebody help me here? The rejected table has appid's that are rejected. If its in that list then we dont want to call it. Wade
User Variables
I'm having trouble using user variables and I hope someone can help, My test environment is MySQL 4.1.11 on WindowsXP with MyODBC 3.51.11 If I open a command line client, I can do this mysql SET @A='Test'; Query OK, 0 rows affected (0.00 sec) mysql Select @A; +--+ | @A | +--+ | Test | +--+ 1 row in set (0.00 sec) If I run the following in MySQLFront v3.1 Set @A='Test'; Select @A; I get back same result +--+ | @A | +--+ | Test | +--+ If I run the same query in MySQL Query Browser v1.1.6 I get this, ErrNo 1060, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; select @A' at line 1 and If I run the same query in my application I get the same error as the Query Browser. Anyone know how I can get my application to give me what I'm looking for? Thanks!
Re: User Variables
Thanks for the reply,
So is there anyway to use User Variables with a single connection. All my apps
are in VB6 and VBA. They all take a query, open a connection, run query, fill
array from query results, close connection and pass back the array. Because of
backward compatibility there's no way I can change them to do otherwise.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error as the
Query Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a very tricky string extraction
This is an interesting problem that I hope someone can help me with. I have a varchar field that contains data like this, 01/01/05 SG Reviewed this 12/15/03 DSD Reviewed that 10/24/02 EWW Worked on that and tested this then stop to do something else 05/02/01 AW Did something 08/31/98 DSD Tested this 07/22/97 EWW Worked on that and did something I need a Select statement that returns the Date for the first occurance of the 'Tested this' substring So far what I've come up with doesn't quite get what I need and it's already pretty hairy. I wonder if there's a more elegant way that I'm unaware of. Thanks
Re: a very tricky string extraction
Sorry everyone for not being more clear. The field IS in a multiline varchar field. The example data was all from one record in the table. Unfortunately, this is a database that has been around for many years and backward compatibility with other apps limits redesigning the table. It is a Comments field and this is the first time anyone has ever tried to mine any data from it. My app is a generic report writer that simply takes and query string and returns the results. No processing of the data can be done in the app. I need the result to come directly from MySQL. Thanks again. - Ed Keith Ivey [EMAIL PROTECTED] 3/24/05 12:04 PM Dan Nelson wrote: How about: SELECT LEFT(description, 8) FROM mytable WHERE description LIKE %tested this% LIMIT 1 Hmm, I assumed he was talking about a multi-line VARCHAR, but now that I look again Dan's interpretation is probably the right one. My previous message doesn't apply (except for the bit about breaking it into columns if you're doing it regularly). -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC
Re: a very tricky string extraction
Thanks Shawn, The idea I've been working with on this is to use an InStr to find the point where the require substring appears. Then I need to search backwards from there to the point where the first \n\r is found. Then the Date that I want would be 8 characters from that position. The obvious problem is that there doesn't seem to be an easy way to search backwards through a string. Any ideas on that line of thought? Thanks - Ed [EMAIL PROTECTED] 3/24/05 1:18 PM Ed Reed [EMAIL PROTECTED] wrote on 03/24/2005 04:02:28 PM: Sorry everyone for not being more clear. The field IS in a multiline varchar field. The example data was all from one record in the table. Unfortunately, this is a database that has been around for many years and backward compatibility with other apps limits redesigning the table. It is a Comments field and this is the first time anyone has ever tried to mine any data from it. My app is a generic report writer that simply takes and query string and returns the results. No processing of the data can be done in the app. I need the result to come directly from MySQL. Thanks again. - Ed Keith Ivey [EMAIL PROTECTED] 3/24/05 12:04 PM Dan Nelson wrote: How about: SELECT LEFT(description, 8) FROM mytable WHERE description LIKE %tested this% LIMIT 1 Hmm, I assumed he was talking about a multi-line VARCHAR, but now that I look again Dan's interpretation is probably the right one. My previous message doesn't apply (except for the bit about breaking it into columns if you're doing it regularly). -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC Then I think you are stuck. What you are trying to find is a minimum value from a certain kind of row within a block of undelimited text. That's like hiding a whole table within a field and trying to write a query to find a field within the table within the field. Unless your text happens to be extremely well formatted, you have no chance of doing an extract in pure SQL and I would say this is definitely not possible using a single SQL statement. It may be possible in a single statement if you create a custom UDF that parses through that comments field. Suppose you wrote the UDF to use this API FIND_IN_COMMENTS(part you want,field to search) Then you could program the UDF to find various parts like first test date, last test date, first review date, First review person, etc. in any field that looks like your comments block. However, I believe that this kind of text manipulation and searching is more complex than can be easily achieved through just SQL and defintely too complex for a single statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: a very tricky string extraction
WHOLLY COW!!!
That was awesome. Thanks
- Ed
Eamon Daly [EMAIL PROTECTED] 3/24/05 1:48 PM
SELECT
LEFT(SUBSTRING_INDEX(LEFT(log, LOCATE('\n', log, LOCATE('tested this',
log)) - 1), '\n', -1), 8)
FROM test WHERE log LIKE '%tested this%'
Don't try this at home.
Eamon Daly
- Original Message -
From: Ed Reed [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, March 24, 2005 3:37 PM
Subject: Re: a very tricky string extraction
Thanks Shawn,
The idea I've been working with on this is to use an InStr to find
the
point where the require substring appears. Then I need to search
backwards from there to the point where the first \n\r is found.
Then
the Date that I want would be 8 characters from that position. The
obvious problem is that there doesn't seem to be an easy way to
search
backwards through a string.
Any ideas on that line of thought?
Thanks
- Ed
[EMAIL PROTECTED] 3/24/05 1:18 PM
Ed Reed [EMAIL PROTECTED] wrote on 03/24/2005 04:02:28 PM:
Sorry everyone for not being more clear. The field IS in a
multiline
varchar field. The example data was all from one record in the
table.
Unfortunately, this is a database that has been around for many
years
and backward compatibility with other apps limits redesigning the
table.
It is a Comments field and this is the first time anyone has ever
tried
to mine any data from it. My app is a generic report writer that
simply
takes and query string and returns the results. No processing of
the
data can be done in the app. I need the result to come directly
from
MySQL.
Thanks again.
- Ed
Keith Ivey [EMAIL PROTECTED] 3/24/05 12:04 PM
Dan Nelson wrote:
How about:
SELECT LEFT(description, 8) FROM mytable WHERE description LIKE
%tested this% LIMIT 1
Hmm, I assumed he was talking about a multi-line VARCHAR, but
now that I look again Dan's interpretation is probably the right
one. My previous message doesn't apply (except for the bit
about breaking it into columns if you're doing it regularly).
--
Keith Ivey [EMAIL PROTECTED]
Smokefree DC
http://www.smokefreedc.org
Washington, DC
Then I think you are stuck. What you are trying to find is a minimum
value from a certain kind of row within a block of undelimited text.
That's like hiding a whole table within a field and trying to write
a
query to find a field within the table within the field. Unless your
text
happens to be extremely well formatted, you have no chance of doing
an
extract in pure SQL and I would say this is definitely not possible
using
a single SQL statement.
It may be possible in a single statement if you create a custom UDF
that
parses through that comments field. Suppose you wrote the UDF to
use
this API
FIND_IN_COMMENTS(part you want,field to search)
Then you could program the UDF to find various parts like first
test
date, last test date, first review date, First review person,
etc.
in any field that looks like your comments block. However, I believe
that
this kind of text manipulation and searching is more complex than
can
be
easily achieved through just SQL and defintely too complex for a
single
statement.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Multiple record Insert limit
Can anyone tell me the limit for the number of records that can be inserted in a single call? I'm trying to insert multiple records like this, INSERT INTO t1 (f1, f2, f3, f4) VALUES (test, 1, some data, Done), (testing, 21, some more data, Still Done),(tested, 50, no more data, Not Done),(tester, 201, Lots of data, Finished); This works when I've done 10 records but fails when I do 3. The error I get when it fails is [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]MySQL server has gone away. So what's the max number that I can insert in a single statement? Thanks!
Re: Multiple record Insert limit
That did it. Thanks for the quick response! [EMAIL PROTECTED] 2/16/05 11:19 AM Ed Reed [EMAIL PROTECTED] wrote on 02/16/2005 02:13:40 PM: Can anyone tell me the limit for the number of records that can be inserted in a single call? I'm trying to insert multiple records like this, INSERT INTO t1 (f1, f2, f3, f4) VALUES (test, 1, some data, Done), (testing, 21, some more data, Still Done),(tested, 50, no more data, Not Done),(tester, 201, Lots of data, Finished); This works when I've done 10 records but fails when I do 3. The error I get when it fails is [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]MySQL server has gone away. So what's the max number that I can insert in a single statement? Thanks! In my experience, the max# of inserts depends on the setting of the MAX_ALLOWED_PACKET variable. If you create a packet that is too large (exceeds that value) you will run into problems like you are seeing. To check the current value for your server, you can do SHOW VARIABLES like 'max%'; Make sure when you are creating your INSERT statements that you do not exceed that value for each statement. If these are generated by mysqldump, it has an option where you can set the maximum length of a generated INSERT statement. Check the docs ( http://dev.mysql.com/doc/mysql/en/mysqldump.html ) or run: mysqldump --help for specifics. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Unique index on two fields
Can someone explain how I can make a combination of two fields be a unique index. For example, in my table I have an OrderID field and a LineItems field. Individually the fields are not unique but when combined they are. I'd like to create a unique index of the two together but not individually. Thanks for the help.
IFNULL returns NULL when it shouldn't
How do I prevent IFNULL and ISNULL from returning a null? I have the following query where this is occurring, Select IFNULL(sum(qty),0) from inventory where partnumber=111 group by partnumber; If the partnumber has never been in inventory then the sum and ifnull functions both return null. If the partnumber has been in inventory then the sum returns the correct sum but I need it to return a zero if it's null. It also doesn't matter if I use the ISNULL funtion or the Is Null operation with a IF statement. Any suggestions?
RE: Rename database
Is there anything wrong with just stopping the server and renaming the database's directory in the DATA directory? I've used that method without any problems. It also works very well for making a copy of the database to a new differently named database. Are there any pitfalls that I haven't encountered yet? Thanks Bartis, Robert M (Bob) [EMAIL PROTECTED] 5/20/04 1:37:20 PM http://dev.mysql.com/doc/mysql/en/RENAME_TABLE.html -Original Message- From: Ngim Ngau - Kzresults [mailto:[EMAIL PROTECTED] Sent: Thursday, May 20, 2004 4:50 PM To: [EMAIL PROTECTED] Subject: Rename database Hi, Is there a way I can rename a database? or at least copy an old database with existing tables into a new one? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
User Conference Presentations
Where's the presentations? They were supposed to be on the website at the end of last week. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Conference Presentations
Where's the presentations? They were supposed to be on the website at the end of last week. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Conference Presentations
Where's the presentations? They were supposed to be on the website at the end of last week. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Conference Presentations
I'm trying to get the presentations too. I found one of them. the session was in the Orlando room and it was called Crack-Proofing MySQL. I found the presentation on the authors site at http://www.appsecinc.com/presentations/Hackproofing_MySQL.pdf. I thought the session was pretty good and luckily the presentation has most of the material. I'd really like to get the presentations for the Stored Procedures workshop by Peter Gulutzan and the Advanced SQL Techniques for Analysis of MySQL Data by Hemant Kirperkar. Although I can't recall how much of Hemant's electronic presentation would be benificial since he did most of the talk on the poster boards. Overall I thought it was a great conference and I can't wait till next year. I just wish the presentations were easier to find. Later Robert J Taylor [EMAIL PROTECTED] 4/21/04 3:39:46 PM You don't mean at the Discovery Cove near the Jimmy Buffet cover band do you? Speaking of which, Lars... there's an English expression that uses the word Cluster...but has nothing to do with databases... :) (Kidding! Just kidding!) Robert Reed wrote: Yes, I'm anxious to get my hands on some of the presentations myself. I think I saw a couple of brief mentions of a url but it wasn't written down anywhere. I'd also like to see some of the pictures I saw David snapping everywhere. :) --- David Perron [EMAIL PROTECTED] wrote: Does anyone know if the presentations from the User Conference are available online? Thanks dp = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash
Re: Access to MySQL query problem
I already specified the version, 4.1.1. My problem query is this, SELECT Item FROM StoredProc GROUP BY Item HAVING (First(User)=Gus); my StoredProc query is a simple one, SELECT Item, Op, User FROM tblSource ORDER BY Item, Op; Any Thoughts? Thanks Nitin Mehta [EMAIL PROTECTED] 3/29/04 12:18:37 AM please include the query, you are using with no success and also specify the version of your mysql. Nitin - Original Message - From: Ed Reed [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 29, 2004 1:27 PM Subject: Access to MySQL query problem I'm coverting an Access database to MySQL 4.1.1 and I need help with a near impossible query. In the Access DB I used a stored procedure; I think I should be able to solve this problem with a subselect in MySQL but so far I'm not having any luck. Here's the problem; In my stored precedure query I get results that look like this Item Op User 2751 2 Dude 2751 3 Aguy 4785 1 Dude 4785 2 Gus 5623 1 Dude 5623 2 Gus 5654 1 Gus 5654 2 Aguy I then query these results to get only the Items for a user when the specified user is the first person for that Item ID, for example. If I query the results for Dude Items 2751, 4785 5623 are returned. If I query the results for Gus Item 5654 are return but not Items 4785 or 5623. If I query the results for Aguy no Items are returned. Does anyone have any thoughts on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Access to MySQL query problem
I'm coverting an Access database to MySQL 4.1.1 and I need help with a near impossible query. In the Access DB I used a stored procedure; I think I should be able to solve this problem with a subselect in MySQL but so far I'm not having any luck. Here's the problem; In my stored precedure query I get results that look like this Item OpUser 2751 2 Dude 2751 3 Aguy 4785 1 Dude 4785 2 Gus 5623 1 Dude 5623 2 Gus 5654 1 Gus 5654 2 Aguy I then query these results to get only the Items for a user when the specified user is the first person for that Item ID, for example. If I query the results for Dude Items 2751, 4785 5623 are returned. If I query the results for Gus Item 5654 are return but not Items 4785 or 5623. If I query the results for Aguy no Items are returned. Does anyone have any thoughts on this? Thanks
Re: Access to MySQL query problem
I already specified the version, 4.1.1. My problem query is this, SELECT Item FROM StoredProc GROUP BY Item HAVING (First(User)=Gus); my StoredProc query is a simple one, SELECT Item, Op, User FROM tblSource ORDER BY Item, Op; Any Thoughts? Thanks Nitin Mehta [EMAIL PROTECTED] 3/29/04 12:18:37 AM please include the query, you are using with no success and also specify the version of your mysql. Nitin - Original Message - From: Ed Reed [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 29, 2004 1:27 PM Subject: Access to MySQL query problem I'm coverting an Access database to MySQL 4.1.1 and I need help with a near impossible query. In the Access DB I used a stored procedure; I think I should be able to solve this problem with a subselect in MySQL but so far I'm not having any luck. Here's the problem; In my stored precedure query I get results that look like this Item Op User 2751 2 Dude 2751 3 Aguy 4785 1 Dude 4785 2 Gus 5623 1 Dude 5623 2 Gus 5654 1 Gus 5654 2 Aguy I then query these results to get only the Items for a user when the specified user is the first person for that Item ID, for example. If I query the results for Dude Items 2751, 4785 5623 are returned. If I query the results for Gus Item 5654 are return but not Items 4785 or 5623. If I query the results for Aguy no Items are returned. Does anyone have any thoughts on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Performing Hardware/OS/MySQL?
We like it on Netware 6.5, with memory equal to 10 times the size of your largest DB. ie; DB=100mb RAM=1gb. Fastest proc you can afford. Chad Attermann [EMAIL PROTECTED] 3/29/04 1:56:05 PM Hello All, I have been a MySQL user for some time and have always run MySQL on older generation Sun servers running Solaris 8. I now seem to be outgrowing my setup and I (and I'm sure others on the list) would appreciate input from the MySQL community as to which hardware, OS, and MySQL flavor/version combinations are best for running MySQL. I expect responses from you all to be subjective, and that's OK. Any information about experiences by seasoned MySQL professionals, especially those that have experiemtned with many different combinations of hardware and OS, will save those of us planning ahead a lot of time and grief. Thanks a lot in advance for your input. Chad Attermann [EMAIL PROTECTED]
Access to MySQL query problem
I'm coverting an Access database to MySQL 4.1.1 and I need help with a near impossible query. In the Access DB I used a stored procedure; I think I should be able to solve this problem with a subselect in MySQL but so far I'm not having any luck. Here's the problem; In my stored precedure query I get results that look like this Item OpUser 2751 2 Dude 2751 3 Aguy 4785 1 Dude 4785 2 Gus 5623 1 Dude 5623 2 Gus 5654 1 Gus 5654 2 Aguy I then query these results to get only the Items for a user when the specified user is the first person for that Item ID, for example. If I query the results for Dude Items 2751, 4785 5623 are returned. If I query the results for Gus Item 5654 are return but not Items 4785 or 5623. If I query the results for Aguy no Items are returned. Does anyone have any thoughts on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access to MySQL problem
I'm coverting an Access database to MySQL 4.1.1 and I need help with a near impossible query. In the Access DB I used a stored procedure; I think I should be able to solve this problem with a subselect in MySQL but so far I'm not having any luck. Here's the problem; In my stored precedure query I get results that look like this Item OpUser 2751 2 Dude 2751 3 Aguy 4785 1 Dude 4785 2 Gus 5623 1 Dude 5623 2 Gus 5654 1 Gus 5654 2 Aguy I then query these results to get only the Items for a user when the specified user is the first person for that Item ID, for example. If I query the results for Dude Items 2751, 4785 5623 are returned. If I query the results for Gus Item 5654 are return but not Items 4785 or 5623. If I query the results for Aguy no Items are returned. Does anyone have any thoughts on this? Thanks
RE: Query across two databases on the same server
yea, I thought it would be that easy too but it doesn't work. Any other ideas? Peter Lovatt [EMAIL PROTECTED] 3/24/04 3:53:03 PM select db1.table.field, db2.table.field where db1.table.someotherfield = db2.table.someotherfield Peter -Original Message- From: Ed Reed [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 23:45 To: [EMAIL PROTECTED] Subject: Query across two databases on the same server Is there any way to have a single select statement that can do a join across two databases on the same server? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
