Re: Alias query problem in 4.1.7?
Ok, when you have a GROUP BY clause, you can specifiy the column name, the column alias or an ordinal number representing the column position. That's why `AssignedTo` works -- it is the column alias. `AssignedToAgent`.`AssignedTo` doesn't work because AssignedTo is not a column name in table `Agent`. So, you have three choices on writing your GROUP BY: GROUP BY 1,2 GROUP BY `Product`.`Product`, `AssignedTo` GROUP BY `Product`.`Product`, `AssignedToAgent`.`AgentName` Use the statement that makes most sense to you. It will save you time if you need to change the statement later. MySQL doesn't act any differently no matter which way you do it. Randy Clamons Systems Programming Astro-auction.com Original Message From: Rhino [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Date: Tue, Nov-23-2004 10:25 AM Subject: Re: Alias query problem in 4.1.7? Try changing your GROUP BY to use the column name of the second column in the SELECT, not the alias of the second column name, i.e. GROUP BY `Product`.`Product`, `AssignedToAgent`.`AgentName` That should work on any version of MySQL. I don't think you're allowed to use aliases in a GROUP BY, only actual column names. Then again, I am mostly a DB2 person and the MySQL dialect of SQL may allow aliases in a GROUP BY for all I know. I'm suprised that the alias worked on 3.2.3: are you sure you have reproduced the exact query that works on 3.2.3? I'm afraid I don't have either a 3.2.3 or 4.1.7 system so I can't try any of this for myself to see. By the way, did you realize that your query is substantially longer than it needs to be? You really only need to qualify column names with table names if you are doing a join of two or more tables and even then, you only need to qualify column names that occur in two or more of the tables read by the query. That would also eliminate the need for you to write aliases for some of your table names at all, further shortening the query. In your query, it appears that only the 'ProductKey' column occurs in more than one of the tables so your query could be as short as this: SELECT `Product`, `AgentName` AS `AssignedTo`, sum(`Inquiries`) AS `Inquiries` FROM `Inquiry` INNER JOIN `Product` ON `Inquiry`.`ProductKey` = `Product`.`ProductKey` INNER JOIN `Agent` ON `AssignedToKey` = `AgentKey` INNER JOIN `DateDim` ON `DateOpenKey` = `DateDimKey` WHERE `Year` = '2003' GROUP BY `Product`, `AssignedTo`; Then again, perhaps it is your shop standard to fully qualify all column names in queries; if so, you should follow your shop standard ;-) More likely, you are probably using some sort of query generating tool in which case you probably don't have a choice in the matter. Rhino - Original Message - From: Geoffrey R. Thompson [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 12:11 AM Subject: Alias query problem in 4.1.7? I have an interesting problem that I cannot find any clues to in the MySQL documentation. The following query works in 3.2.3, but does not work in 4.1.7: SELECT `Product`.`Product`, `AssignedToAgent`.`AgentName` AS `AssignedTo`, sum(`Inquiries`) AS `Inquiries` FROM `Inquiry` INNER JOIN `Product` ON `Inquiry`.`ProductKey` = `Product`.`ProductKey` INNER JOIN `Agent` AS `AssignedToAgent` ON `Inquiry`.`AssignedToKey` = `AssignedToAgent`.`AgentKey` INNER JOIN `DateDim` AS `DateOpen` ON `Inquiry`.`DateOpenKey` = `DateOpen`.`DateDimKey` WHERE `DateOpen`.`Year` = '2003' GROUP BY `Product`.`Product`, `AssignedToAgent`.`AssignedTo`; It appears that if I take the table alias AssignedToAgent out of the GROUP BY clause (leaving just the column alias AssignedTo), the query will then work in 4.1.7 - even though the table alias does not present a problem in 3.2.3. Any ideas why? Any help would be greatly appreciated. -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 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: MySQL v ASP problem
J.R.,
The difference in the way the two statements function is in your code. In the first
example, you set the cursor type (rs.Open sql, conn,3,3) as updatable. In the second
example, you set the cursor to the default type (non-updatable: rs.Open sql, conn).
I haven't used ASP extensively recently, but it seems likely that if you add the
,3,3 to the second rs.Open statement your app will behave properly.
Randy Clamons
Systems Programming
Astro-auction.com
Original Message
From: J.R. Bullington [EMAIL PROTECTED]
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
Date: Thu, Oct-7-2004 1:45 PM
Subject: MySQL v ASP problem
I have searched high and low and I know that you guys can help out ( as
you
have helped me before ).
First, I will list table descriptions, then the problem, then I will
list
the code, and finally the permissions.
The Descriptions:
2 tables - both MyISAM.
Table 1 has 9 fields, 1 index PID (PK, Index);
Table 2 has 36 fields, 1 index EcnID (PK, index). PID is a FK in
Table 2;
ASP and IIS 6 on a Win2k3 server
MySQL 4.0.20 on a Linux RHEL AS
The Problem:
I am trying to do an rs.update using ASP. In Table 1, code works
perfectly, retrieves and updates without issue. In Table 2, same
code,
doesn't work. NOTE: I have to use rs.update and not UPDATE tablename
SET
... due to the large amount of data that needs to be pushed. I get the
old
Query-based update failed because the row to update could not be
found.
So here we go with the code:
BEGIN NECESSARY CODE
*
%
Dim Conn
Conn = DRIVER={MySQL ODBC 3.51 Driver}; SERVER=IPADDRESS;_
'MyODBC driver is 3.51.9
DATABASE=DBNAME; UID=UID;PWD=PWD; OPTION=3
'
dim resdata(36), resflds(36), reschks(36), resnote(36), resfrmt(36),
errtext
Set rs = Server.CreateObject(ADODB.Recordset)
'
if request.querystring(EcnID) then
session(EcnID)=request.querystring(EcnID)
** CODE JUMP **
'
if request.form(B1)=Exit Without Changes then
response.redirect(SOMEOTHERPAGE.ASP)'Handle bail-out
'
if request.form(B1)=COMPLETE REVISION then
' START HERE TO STORE UPDATES
'
vararray=session(resdata)
'retrieve the session data provided by
vararra1=session(resflds)
'database query
vararra2=session(reschks)
vararra3=session(resnote)
'
for x=0 to ubound(vararray)
'Parse the session data into usable arrays
resdata(x)=vararray(x)
resflds(x)=vararra1(x)
reschks(x)=vararra2(x)
resnote(x)=vararra3(x)
next 'x
'
sql = Select * from tblEncounter where EcnID= session(EcnID)
response.write sql
response.flush
rs.Open sql, conn,3,3
'
For Each objItem in request.form
'look at form field
for x=0 to ubound(resflds)
'search all the field name array
if ucase(objitem)= ucase(resflds(x)) then
'update the data
resdata(x)=request.form(objitem)
'resdata array now contains newest data
end if
next 'x
next 'objitem
rs(Compdate)=now()
'
rs.update
response.redirect(SOMEOTHERPAGE.ASP)
end if
'
if request.form(B1)=Update Information then
' START HERE TO DO UPDATE / ERROR CHECK
'
vararray=session(resdata)
'retrieve the session data provided by the
vararra1=session(resflds)
'database query
vararra2=session(reschks)
vararra3=session(resnote)
'
for x=0 to ubound(vararray)
'Parse the session data into usable arrays
resdata(x)=vararray(x)
resflds(x)=vararra1(x)
reschks(x)=vararra2(x)
resnote(x)=vararra3(x)
next 'x
'
CODE JUMP *
Else
' START HERE FOR NEW DATA PULL-UP
'
dim resname
dim rs
dim sql
sql = Select * from tblEncounter where EcnID=session(EcnID)
rs.Open sql, conn
rs.MoveFirst
'
x=0
for each fld in rs.Fields
'Load RS into an session array
resflds(x) = fld.name
'Load field names from database
resdata(x) = fld.value
'Initialize the fields to null
reschks(x) = 1
'set field status to good
resnote(x) =
'set field comment to null
x=x+1
next
'
rs.close
'
session(resdata)=resdata
session(resflds)=resflds
session(reschks)=reschks
session(resnote)=resnote
end if
'
%
*** END NECESSARY CODE **
Now, permissions:
MySQL - FULL CONTROL FOR THIS DATABASE ( ALL PRIVLIGES WITH GRANT
OPTION )
IUSR - Read, Read Execute, Write, Modify, List Folder Entries
Please, if any help can be offered I would greatly appreciate it. If
you
need anything else from me, please don't hesitate to ask!
TIA
J.R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Long Running Queries
Take a look at the docs for EXPLAIN (manual.html#IDX853). Use the 'possible_keys' column of the results to determine which column indexes will improve your query performance. Make sure your table has a primary key. Whenever possible, use the primary key in your WHERE clause when you SELECT or UPDATE a single row. You might get better results from this list if you include your table structure and your queries along with your question. Randy Clamons Systems Programming Astro-auction.com Original Message From: Danny Willis [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Date: Wed, Oct-6-2004 1:02 PM Subject: RE: Long Running Queries What do you mean by appropriate indexes? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 3:43 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Long Running Queries You need Appropriate Indexes on the tables. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 1:11 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Long Running Queries Hello, I recently began experiencing issues that I would like some assistance with. Server Details: - Windows 2003 - MySQL Max 4.0.20a I have a table with 450,000+ records in it. When I try to run a single query, such as selecting 1 row or deleting 1 row using the WHERE clause, the query can take forever to run. In a specific case, it took 57 seconds to complete the SELECT query. Any ideas on how to speed this up? -- Thank You, Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
