SQLite Forum

Pouvez-vous nous expliquer ce qui se passe ? avec ma sélection SQL
Login

Pouvez-vous nous expliquer ce qui se passe ? avec ma sélection SQL

(1.1) Originally by Didier Riche (driche) with edits by Richard Hipp (drh) on 2021-02-05 18:05:14 from 1.0 [source]

I am transferring my application from sqlanywhere to sqlite. Here is an example of a difference I noticed with this SQL query (automatic generation according to sqlanywhere rules).

Query reformatted by drh for readability

SELECT
   a.product_code,
   a.activity_code,
   (SELECT sum(b.nombre_de_repas_préparés) as nombre_de_repas_préparés
      FROM business_events_by_activity AS b
     WHERE  b.product_code =  a.product_code),
   sum(a.activity_cost) as activity_cost,
   activity_cost / nombre_de_repas_préparés  as coût_unitaire
FROM
   business_events_by_activity AS a
GROUP BY
   a.product_code,a.order_, a.activity_code
ORDER BY
   order by a.product_code,a.order_, a.activity_code

In sqlanywhere I get the unit_cost (coût_unitaire) for each line product_code / activity_code according to the formula 'activity_cost / nombre_de_repas_préparés', but in sqlite the value unit_cost (coût_unitaire) is only calculated on the first line of each new product_code.

Difficult to explain this without hardcopies of data. If someone interested in the case send me your e-mail to receive the complete explanation. Many thanks. Didier

(2) By anonymous on 2021-02-05 18:23:58 in reply to 1.1 [link] [source]

Why have you got two ORDER BY ... see last two lines of your SQL statement?

ORDER BY
   order by a.product_code,a.order_, a.activity_code

(3.2) By Keith Medcalf (kmedcalf) on 2021-02-05 19:13:41 edited from 3.1 in reply to 1.1 [link] [source]

Do you mean, perhaps

  select a.product_code,
         activity_code,
         nombre_de_repas_prepares,
         activity_cost,
         activity_cost / nombre_de_repas_prepares as cout_unitaire
    from (
            select product_code,
                   activity_code,
                   sum(a.activity_cost) as activity_cost
              from business_events_by_activity
          group by product_code, activity_code
         ) as a,
         (
            select product_code,
                   sum(nombre_de_repas_prepares) as nombre_de_repas_prepares
              from business_events_by_activity
          group by product_code
         ) as b
   where a.product_code == b.product_code
order by a.product_code, activity_code
;

Although I have absolutely no idea what the purpose of a.order_ is since it only appears in the group by and order by clauses to cause ill-conception.

(4) By Simon Slavin (slavin) on 2021-02-06 06:51:31 in reply to 1.1 [link] [source]

I think you have encountered one of the places where the SQL specification is not clear on what should be returned. SQL Anywhere does it one way. SQLite does it another. This is common when using a subselect which does its own processing.

Can you supply us a very small table, perhaps just a few rows, which gives different answers in the two systems ? We will be able to tell you exactly why SQLite gives the answer it does.

(5) By Didier Riche (driche) on 2021-02-06 07:47:29 in reply to 1.1 [link] [source]

Hi Richard, I just sent you harhcopy on your mail address found on internet. Best regards, Didier

(6) By Didier Riche (driche) on 2021-02-06 07:49:54 in reply to 2 [link] [source]

it is an error. Just one order by of course. My mail is riche.dbb@orange.fr send me a messahe to receive harcopy of the data and differnces between Sqlite and sqlanywhere, best regards. Didier Riche

(7.1) Originally by Didier Riche (driche) with edits by Richard Hipp (drh) on 2021-02-06 12:56:02 from 7.0 in reply to 4 [link] [source]

Merci. voici mon mail riche.dbb@orange.fr Ecrivez-moi, je vous adresserai en retour une hardcopy des données et des résultats différents obtenus entre sqlite et sqlanywhere. Bonne journée, Didier Riche


Translation:
Thanks. My email is riche.dbb@orange.fr. Write to me, I will send you a hardcopy of the database and the different results obtained between sqlite and sqlanywhere. Good day, Didier Riche

(8) By Richard Hipp (drh) on 2021-02-06 12:59:36 in reply to 7.1 [link] [source]

You sent me a PDF. More useful would be an actual database file so that I could see the database schema and run the query myself.

(9) By Didier Riche (driche) on 2021-02-06 16:57:27 in reply to 3.2 [link] [source]

Many many thanks for the direction you provide to me. Here is the query which works well (that is to say the result I expected).

select a.product_code, activity_code, nombre_de_repas_préparés, activity_cost, activity_cost / nombre_de_repas_préparés as cout_unitaire from ( select product_code,order_, activity_code, sum(activity_cost)as activity_cost from business_events_by_activity group by product_code, order_, activity_code ) as a,

     (select product_code,
               sum(nombre_de_repas_préparés) as nombre_de_repas_préparés
          from business_events_by_activity
      group by product_code
     ) as b

where a.product_code == b.product_code order by a.product_code, order_, activity_code ;

It seems that resolution is quit different than Sqlanywhere which is:

select a.product_code, a.activity_code, (select sum(b.nombre_de_repas_préparés) as nombre_de_repas_préparés from BUSINESS_EVENTS_BY_ACTIVITY b where b.product_code = a.product_code), sum(a.activity_cost) as activity_cost, activity_cost / nombre_de_repas_préparés as coût_unitaire from BUSINESS_EVENTS_BY_ACTIVITY a group by a.product_code,a.order_,a.activity_code order by a.product_code,a.order_,a.activity_code

but the important thing is that I have the solution in Sqlite (I have to adapt my own SQL generator). However, if SOMEONE has another SOLUTION, I would greatly appreciate to know it. In fact I thought that SQL language was more 'universal', but it seems it is not exactly true !

Thank you to all of us for your help, and I would be pleased to communicate again with you. On linkedIn be in contact and if you want come to my profil or click on key word #expertizers Best regards.

Didier Riche riche.dbb@orange.fr (Paris and Saint Jean de Luz). To get copy of the different results write me a mail. Thanks again.

(10) By Keith Medcalf (kmedcalf) on 2021-02-06 22:33:51 in reply to 9 [link] [source]

SQLAnywhere would seem to have different and looser usage of column aliases (name) rules.

Note that the following is invalid in the current version (tip of trunk) of SQLite but might just work fine in SQLAnywhere despite being invalid SQL:

sqlite> create table x(a,b,c);
sqlite> select a, sum(b) as sumb, sum(c) as sumc, sumb / sumc as boverc from x group by a;
Error: no such column: sumb
sqlite>

The correct SQL that should be recognized by anything claiming SQL compliance is:

select a, sumb, sumc, sumb / sumc as boverc from (select a, sum(b) as sumb, sum(c) as sumc from x group by a);

The "loosey goosey"ness of column alias handling in SQLite probably varies by version.

(11) By anonymous on 2021-02-07 17:52:54 in reply to 10 [link] [source]

Many thanks for your reply and examples. Your observations are very important and helpfull for me. Best regards Didier Riche riche.dbb@orange.fr