Complex SQL Statement

0.o · 05-20-2008, 08:08 AM

Hello,

I am trying to figure out a rather large SQL statement that is located in a script. It is complaining about the following:

Code:

ERROR at line 4: Unknown command '\"'.

Here is the statement:

Code:

use db;
create temporary table snrtable 
select mid(eventtime,1,10) as date,node,count(node) as Alarms from snrhist where to_days(now())-to_days(eventtime)<8 group by date,n
ode having count(node)>2;
create temporary table snrtable2
select date,sum(Alarms) as Alarms,count(distinct(node)) as Nodes,concat(truncate(count(distinct(node))/933*100,1),\"%\") as Percent 
from snrtable group by Date order by Date limit 7;
create temporary table snrtable3
select date,sum(Alarms) as Alarms,count(distinct(node)) as Nodes,concat(truncate(count(distinct(node))/933*100,1),\"%\") as Percent 
from snrtable group by Date order by Date limit 7;
alter table snrtable3 modify date varchar(32);
insert into snrtable3 (Date,Alarms,Nodes,Percent)
select \"Average\" as Date,truncate(avg(alarms),1) as Alarms,truncate(avg(Nodes),1) as Nodes,concat(truncate(avg(Nodes)/933*100,1),\
"%\") as Percent from snrtable2;
select Date,Alarms,Nodes,Percent from snrtable3;

If someone could point out what's wrong here, i would appreciate it. In addition, please explain what's going on in that statement...i am really not all that familiar with SQL.

Guttorm · 05-20-2008, 09:00 AM

Hi

It's not easy to tell what it does without more information. It basically makes a report from data in a table called "Alarms". The report is some math done on the data, like average, counts and some sums.

I guess you found it in a PHP script or something. To make it work from the command line, you need to change \" into " all over. It was probably inside quotes where you found it, and when it's inside, every " needs to be escaped as \".

0.o · 05-20-2008, 09:05 AM

I believe that all of the double quotes are already escaped ?

redgoblin · 05-20-2008, 09:10 AM

You're escaping your double quotes, therefore the strings aren't closing.

Make sure it runs cleanly within the SQL server (MySQL I assume?) first.

As for what it's doing, that's pretty hard to answer without have a good understanding of your data.

deesto · 05-20-2008, 10:13 AM

Just a technical note: this isn't "a rather large SQL statement"; it is a collection of multiple statements that are doing different things to your data, like choosing a database, creating multiple temporary tables, selecting data from other tables and adding them to your temp tables, modifying data, etc.

The other folks have already explained that the error is calling out the backslash on "line 4" of your "script" because it is being used to escape a character that is already escaped by default, which renders the rest of this line invalid. Just removing these backslashes may fix the problem, but hard to say without seeing more of the data.

As redgoblin said, it seems likely that this is being done in MySQL. Your best bet for learning what's going on here is to log into your MySQL instance, as whatever user this script is running as, entering each of these lines one by one, and reviewing the results. MySQL will give you direct feedback after each command, and either display the results of your query, or show you a success or fail message.

PAix · 05-20-2008, 11:51 AM

Hi, Contrary to your expectation you are not trying to figure out a large complex SQL statement, but several such statements. Might I suggest a couple of strategies that could benefit you in your quest for enlightenment.

Firstly, in case you are unable to get to the bottom of the problem yourself, please post the following to help others render assistance to you.

Can you identify the the shell script, Bourne shell (sh) or what that is in use?

What is the database in use MySQL, Postgres or another and it's version - personally my database experience was Ingres so I don't instantly recognise the code, but suspect MySQL?

The error at line 4 - is this error being reported by the shell script or the SQL interpreter, so is this the fourth line of the shell script or the fourth line of the SQL dialogue (as delimited by ';' characters)? If the former, it might be handy to display a few more lines of script.

You are using Solaris and mention that the SQL is contained within a script. This suggests that the script mentioned is probably a shell script containing embeded SQL statements within it.

You have to understand that as an SQL novice that here you are attempting to eat two different meals at once, satisfying not only the SQL requirement, but having to wrap it up to placate the shell scripting rules also. Not the easiest way to learn or understand SQL scripting, because you have this artificial overhead which is often significantly confusing, not to say absolutely distracting.

i believe your problem centres around the way you have chosen to escape the quotes around the % character. instead of \"%\" you might like to try ""%"" or '"%"' The problem is actually caused because the / character has a meaning to the shell which evaluates the statement before the SQL gets a look at it.

This link might be of help to you, if only to show that the subject of escaping characters isn't trivial, even when just considering the MySQL environment: honest, it gets no better!
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

What does it all mean - I honestly don't know, but I'm good for 90% of it and have more than a few suggestions to help you out.

Code:

USE db;
CREATE TEMPORARY TABLE snrtable
          SELECT mid(eventtime,1,10) as date, node, count(node) AS Alarms
          FROM snrhist
          WHERE to_days(now())-to_days(eventtime)<8
          GROUP BY date, node
          HAVING count(node)>2;

TEMPORARY TABLES are all well and good, but not for debugging. Their strength and weakness is that they are discarded automatically after use They have their uses, but not during development I suggest..
Try

Code:

DROP table snrtable;
CREATE TABLE snrtable  ((etc etc))

This leaves the table in place for later inspection if things don't go quite as planned or if you wish to figure out what is going on in each statement as you progress towards your goal.
The table is populated by the contents of the complete SELECT statement which ends at the semi-colon. It's always a good idea to put SQL reserved words in upper-case to aid readability. I also like to format my SQL code for readability - be aware that this might conflict with the shell script which interpret the newline character as end-of-statement.
WHERE as you know restricts the scope of the query, in this case to events in the last 7 days.
The first 10 characters of snrhist.eventtime creates the column date and snrhist.node remains with it's original column name "node". The aggregate function count(node) creates the column "Alarm"; use of an aggregate function also mandates the use of the GROUP BY clause where non aggregated columns appearing in the SELECT clause must appear. The order is significant, but does not replace the ORDER BY clause if the ordering of the result is important to you. Ignore it and it will bite you when you least expect it, often after an OS, DBMS or platform upgrade/change that disenfranches something.

In reviewing what I am typing, I notice that most of the good advice has been covered as I have typed. All the advice thus far has been good, but hopefully there is something here that will augment and not merely repeat the sage advice you have already received.
Please note the case conflicts in red and what I would change in blue. I have also suggested the quoting change, but I could be wrong there - I listen to others here , but it would have been my first shot. After listening, it would have been my next shot if bare " didn't do the job. I'll stop digging here. Google is your friend - MySQL manual <subject>, where the subject can be something like escape character, group by, create table, etc

At a guess I'd think it is reporting naughty nodes that have had more than 2 events each day over the last seven days. Not only that, but an indication of how greedy too.

From the reformatted code it's much easier to see what is going on.

Code:

USE db;

DROP snrtable;

CREATE TABLE snrtable
  SELECT mid(eventtime,1,10) AS date, node, count(node) AS Alarms
  FROM snrhist
  WHERE to_days(now())-to_days(eventtime)<8
  GROUP BY date, node
  HAVING count(node)>2;


DROP TABLE snrtable2;

CREATE table snrtable2
  SELECT date, sum(Alarms) AS Alarms, count(distinct(node)) AS Nodes,
   concat(truncate(count(distinct(node))/933*100,1),""%"") AS Percent
  FROM snrtable
  GROUP BY Date
  ORDER BY Date
  LIMIT 7;


DROP TABLE snrtable3;

CREATE TABLE snrtable3
SELECT date, sum(Alarms) AS Alarms, count(distinct(node)) AS Nodes,
 concat(truncate(count(distinct(node))/933*100,1),""%"") AS Percent
  FROM snrtable
  GROUP BY Date
  ORDER BY Date
  LIMIT 7;

ALTER TABLE snrtable3
MODIFY date varchar(32);

INSERT INTO snrtable3 (Date, Alarms, Nodes, Percent)
  SELECT ""Average"" AS Date,
  truncate(avg(alarms),1) AS Alarms,
  truncate(avg(Nodes),1) AS Nodes,
  concat(truncate(avg(Nodes)/933*100,1),""%"") AS Percent
  FROM snrtable2;

SELECT Date, Alarms, Nodes, Percent
FROM snrtable3;

Love SQL - power.

deesto · 05-20-2008, 02:00 PM

Quote:

Originally Posted by PAix

What does it all mean - I honestly don't know ...
In reviewing what I am typing, I notice that most of the good advice has been covered as I have typed...

No offense intended, but what is the specific intention of your post if you don't have an answer, and the rest of your post is simply restating everything that others have already said?

I understand that you're trying to help the OP, but the OP has already been asked for more information, and they've stated that they're not at all familiar with SQL. Reformatting their SQL statements may make it easier for other people to read, but that's likely about it.

Honestly, I'm not even sure why I care ... just marveling at why you'd double the size of the thread simply to reiterate the other posts. I guess if it helps the user somehow, it's all good.

PAix · 05-21-2008, 06:57 AM

Sorry to offend deesto, when I began typing, it was a zero response thread. Yes I became aware just before I posted that there had been quite a number of points already covered. I decided to quit then and whatever was duplicated was unfortunate. The alternatives, were to a). Just save my time or b). spend my life continually edit my post away to nothing as others cover an increasing number of points that I have to remove from my post.

I thank you for your concern. You seem similarly guilty as you said MAY in your reply, without any elaboration, such as the link that indicated exactly what a minefield it might be. Nice endorsement, but also little substance. The reformatting of the code, for the eyes of a Newbie, made it's function significantly clearer than the original. Did you explain how temporary tables would assist the OP debugging efforts or otherwise.

Duplicated, shambolic maybe, but not totally bereft of assistance. My sin was not editing it, for the reasons already stated. It had sufficient content to make it useful. I apologise to all for my lack of brevity.