2

I would like to execute that query with MySQL (v. 5.5.41) that works fine with Oracle:

CLEAR BREAKS;
BREAK ON mq;

SELECT mq, im, pf, kmct 
FROM vehicules 
WHERE mq='Renault' 
ORDER BY mq;

CLEAR BREAKS;

This is the output Oracle builds, and what I'm looking for with MySQL :

mq       | im          | pf   | kmct
---------+-------------+------+----------
Renault  | AA-888-AA   | 4    | 3424
---------+-------------+------+----------
         | AA-999-AA   | 2    | 2042
---------+-------------+------+----------
         | BB-888-BB   | 3    | 1580

The error I get is :

#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 'CLEAR BREAKS' at line 1

It seems like there is no implementation of the command "BREAK" in MySQL. Is that true? In that case, is there any way to simulate it?

Char siu
  • 159
  • 1
  • 12
  • I think you're looking for `LEAVE`, take a look [here](http://www.herongyang.com/MySQL/Procedure-Basic-ITERATE-and-LEAVE-Statement.html) – EternalHour Jun 11 '15 at 09:44
  • No, i'm looking for an instruction that makes the tables the DBMS' output table fancyer by avoiding the repetition of an item in a cell. On my instance, I will get a few lines with "Renault" as a content of the first cell of all lines. BREAK ON mq; in Oracle will only put "Renault" on the [1][1] cell, and let all the [1][2-n] cells empty. I will edit my post to avoid any misunderstandings. Anyway, thank you for your answer. – Char siu Jun 11 '15 at 09:53

1 Answers1

1

MySQL does not support BREAK ON. AFAIK it is a reporting style command in ORACLE.

To achieve your required output, you have to remember the previously read row's column value and compare with value read in next row's column. When they match, you can set an empty string as output, else retain the new value.

Example: 

SELECT 
       ( CASE WHEN @prev_mq != mq THEN @prev_mq := mq ELSE '' END ) AS mq
     , im, pf, kmct 
  FROM vehicules, ( SELECT @prev_mq := '' ) AS initializer
 WHERE mq = 'Renault' 
-- ORDER BY mq;

Use of ORDER BY mq has no meaning here. Because, as you are replacing repeating occurrences with an empty string, ORDER BY would cause unexpected results.

Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
  • Please refer to http://stackoverflow.com/questions/1241856/illegal-mix-of-collations-error-in-mysql. It may help further. – Ravinder Reddy Jun 11 '15 at 12:46
  • 1
    I had the `#1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '<>'`, and I just had to write `( CASE WHEN @prev_mq != mq COLLATE 'utf8_general_ci' THEN @prev_mq := mq ELSE '' END )` instead of your `CASE`. Thank you very much ! – Char siu Jun 11 '15 at 12:55