2

I have an application that I wrote in JavaScript/HTML that utilizes a local machine database. It's a pretty complex problem but I just need help with the SQL query right now. More info on the local JavaScript database class is found here: JavaScript Access database class

The data consists of 3D values and a time component so the database is pretty large though I simplified the data from 200 time steps to 3 for testing. There are multiple values that describe the 3D model so I access different values using drop downs. Unfortunately I don't have a working version I can show anyone because it only works on a local machine. I'm trying to convert this version to a web-style database driven demo that runs off MySQL. I host at FatCow and if you know them you know they have a php-driven MySQL interface.

Data Setup

The way the data is set up, I have several columns that describe the 3D data table is described below. This is for one table called "dat":

  • Cell (the unique number for that cell in the matrix)
  • XVal (this is the X location inbn the matrix)
  • YVal (this is the Y location in the matrix)
  • Lift (This is the Z location in the matrix)
  • Oct-97 (this is one of three time components with values)
  • Oct-07 (this is one of three time components with values)
  • Oct-17 (this is one of three time components with values)

There are several tables that are called on to make grids, but let's just focus on the 'dat' table for now

Some data tables are set up similarly with the references XVal and YVal but some only refer to the unique Cell value. With the current application, I take the large columns of data and do a transformation pivot table to create grids of the model. The 3D matrix grid is 36x41x15. These grids are displayed as:

  • XY plan view
  • XZ cross section
  • YZ cross section

I have three SQL queries for each of the grids that work in my local application, but these do not work in MySQL. Keep in mind I'm teaching myself how to do this stuff so I'm a bit inexperienced. Don't kill me if it's an obvious mistake.

SQL Queries that work on local application

For the above three queries, here is what works:

  • XY grid: TRANSFORM ROUND(max(dat.[Oct-07]) *100,2) SELECT dat.Yval FROM dat WHERE (((dat.Lift)=7)) GROUP BY dat.Yval ORDER BY dat.Yval DESC , dat.Xval PIVOT dat.Xval;

  • XZ grid: TRANSFORM ROUND(max(dat.[Oct-07]) *100,2) SELECT dat.Lift FROM dat WHERE (((dat.Yval)=20)) GROUP BY dat.Lift, dat.Yval ORDER BY dat.Lift DESC PIVOT dat.Xval;

  • YZgrid: TRANSFORM ROUND(max(dat.[Oct-07]) *100,2) SELECT dat.Lift FROM dat WHERE (((dat.Xval)=20)) GROUP BY dat.Lift ORDER BY dat.Lift DESC , dat.Yval DESC PIVOT dat.Yval;

The application calls the XVal, YVal or Lift that is needed to display. So in the XY grid, [Oct-07] is a variable in the query as is dat.Lift (a number from 1 to 15).

Similarly in the two other grids, [Oct-07] is a variable in the query as is dat.YVal or dat.XVal respectively.

After I imported the data to MySQL I tried running these queries but they give an error:

MySQL said: Documentation

#1064 - 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 'TRANSFORM ROUND( max( dat . [ Oct - 07 ] ) * 100 , 2 ) SELECT dat . Yval FROM da' at line 1

I use the ROUND(MAX(... to return every value of the column and fix the decimal.

I think the problem is in the TRANSFORM statement, but I'm not sure how to translate this and get the same result.

The question is, how can I translate these three queries from the MS Access based query to a MySQL based query?

Thanks for help and insight

Community
  • 1
  • 1
Crimpy
  • 195
  • 20
  • 2
    There is no conversion. mysql doesn't have any support for pivot queries, other than hideous hacks for particular column sets. and those hacks get extremely ugly, extremely quickly – Marc B Dec 16 '14 at 20:26
  • Any suggestions on what I can do to achieve what I need? – Crimpy Dec 16 '14 at 20:29
  • do a standard query in mysql, then do the pivot conversion in client-side code. – Marc B Dec 16 '14 at 20:31
  • let me ask you this then, which would you think would be faster to execute: the SQL hack on the server side, or the conversion client side? – Crimpy Dec 16 '14 at 20:33
  • 1
    depends on exactly how your query transforms. it's rows -> fixed number of columns, then the hack will be "ok". but if it's rows -> arbitrary columns, then you're SOL. – Marc B Dec 16 '14 at 20:38

1 Answers1

1

There is no TRANSFORM in MySQL. TRANSFORM is really just an alias so MS Access will do the work for you in creating "cross-tab" queries and you can use less code. Instead use the ANSI SQL way.

http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

Example of ANSI SQL cross tab query:

Example ANSI-SQL Crosstab

SELECT 
    SUM(CASE WHEN purchase_date BETWEEN '2004-08-01' and   '2004-08-31' THEN amount ELSE 0 END) As m2004_08, 
    SUM(CASE WHEN purchase_date BETWEEN '2004-09-01' and   '2004-09-30'  THEN amount ELSE 0 END) As m2004_09,
    SUM(CASE WHEN purchase_date BETWEEN '2004-10-01' and   '2004-10-31' THEN amount ELSE 0 END) As m2004_10, 
SUM(amount) As Total
FROM purchases WHERE purchase_date BETWEEN '2004-08-01' AND '2004-10-31'

You'll likely need to use GROUP_CONCAT and it would be easiest if you can use stored procedures

Here's a quick tutorial on creating a crosstab query in MySQL: http://www.databasejournal.com/features/mysql/article.php/3855376/All-About-the-Crosstab-Query.htm

Don Rhummy
  • 24,730
  • 42
  • 175
  • 330
  • One thing that sets my problem out from the examples is that these examples you gave (and others I've seen) count the numbers of occurrences. I need the actual values to pass through. How can I do this? – Crimpy Dec 17 '14 at 16:34
  • @Crimpy you'll need to put the value into a variable and use mysql math functions http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html – Don Rhummy Dec 17 '14 at 17:04