Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Help with SQL! |
Tue, Sep 11 2007 7:32 AM | Permanent Link |
Roger Oliveira - RS/Brasil | OBS: Sorry! I don't speak english very well! I have this SQL: ( IN THE LINE QUANTIDADE HAVE A ERROR: EXCEDIDO IS NON-AGGREGATED COLUMN ) How I can do this line, I need a non-aggregated field EXCEDIDO > 0 for each registry, in same sql? THANKS! SELECT ID_AREA, ID_OPERACAO, CAP.LIMITE, IF((AVG(DURACAO) - AVG(TRANSPORTE)) < 0 THEN 0 ELSE (AVG(DURACAO) - AVG(TRANSPORTE))) TRABALHO, CAP.TRANSPORTE,AVG(DURACAO) TOTAL, COUNT(EXCEDIDO) TOTALREG, IF(EXCEDIDO > 0 THEN COUNT(EXCEDIDO) ELSE NULL) QUANTIDADE, <= THIS LINE HAVE A ERROR IF(AVG(DURACAO) > AVG(LIMITE) THEN SUM(EXCEDIDO) ELSE NULL) MINUTOS, C.DESCRICAO FROM OPERACOES O LEFT OUTER JOIN PROCESSADOS P ON (O.ID_OPERACAO = P.OPERACAO_ID AND AREA_ID = 2) LEFT OUTER JOIN CAPTURAR CAP ON (O.ID_AREA = CAP.ID_CELULA) AND (O.ID_OPERACAO = CAP.ID_OPERACAO) LEFT OUTER JOIN CELULAS C ON (O.ID_AREA = C.ID) WHERE O.ID_AREA = 2 GROUP BY ID_OPERACAO ORDER BY ID_OPERACAO THANKS! |
Tue, Sep 11 2007 8:05 AM | Permanent Link |
"Robert" | "Roger Oliveira - RS/Brasil" <rogerhp@ibest.com.br> wrote in message news:E7EECD12-27D0-4A6D-B887-FD8379B9C54A@news.elevatesoft.com... > > OBS: Sorry! I don't speak english very well! > IF(EXCEDIDO > 0 THEN COUNT(EXCEDIDO) ELSE NULL) QUANTIDADE, should be count(if(excedido > 0 then excedido else 0)) quantidade but unless I'm missing something here, I don't see how this line can work either IF((AVG(DURACAO) - AVG(TRANSPORTE)) < 0 THEN 0 ELSE (AVG(DURACAO) - AVG(TRANSPORTE))) TRABALHO, seems to me you should get rid of the AVGs. Anyway, if after you fix the other line the SQL runs, please post it again here for others to take a look at. There could be a parsing type problem. Robert > I have this SQL: ( IN THE LINE QUANTIDADE HAVE A ERROR: EXCEDIDO IS > NON-AGGREGATED COLUMN ) > How I can do this line, I need a non-aggregated field EXCEDIDO > 0 for > each registry, in same sql? THANKS! > > SELECT ID_AREA, > ID_OPERACAO, > CAP.LIMITE, > IF((AVG(DURACAO) - AVG(TRANSPORTE)) < 0 THEN 0 ELSE > (AVG(DURACAO) - AVG(TRANSPORTE))) TRABALHO, > CAP.TRANSPORTE,AVG(DURACAO) TOTAL, > COUNT(EXCEDIDO) TOTALREG, > IF(EXCEDIDO > 0 THEN COUNT(EXCEDIDO) ELSE NULL) QUANTIDADE, > <= THIS LINE HAVE A ERROR > IF(AVG(DURACAO) > AVG(LIMITE) THEN SUM(EXCEDIDO) ELSE NULL) > MINUTOS, > C.DESCRICAO > > FROM OPERACOES O > > LEFT OUTER JOIN PROCESSADOS P ON (O.ID_OPERACAO = P.OPERACAO_ID AND > AREA_ID = 2) > LEFT OUTER JOIN CAPTURAR CAP ON (O.ID_AREA = CAP.ID_CELULA) AND > (O.ID_OPERACAO = CAP.ID_OPERACAO) > LEFT OUTER JOIN CELULAS C ON (O.ID_AREA = C.ID) > > WHERE O.ID_AREA = 2 > > GROUP BY ID_OPERACAO ORDER BY ID_OPERACAO > > > THANKS! > > |
Tue, Sep 11 2007 2:29 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roger,
<< I have this SQL: ( IN THE LINE QUANTIDADE HAVE A ERROR: EXCEDIDO IS NON-AGGREGATED COLUMN ) >> Robert is correct - rewrite the SELECT expression as he indicated and it will work. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Sep 12 2007 8:07 AM | Permanent Link |
Roger Oliveira - RS/Brasil | But, this sql don't result what I want, agregate field, dont count just excedido > 0 but all excedido all registry, I need just where excedido > 0. thanks! |
Wed, Sep 12 2007 9:45 AM | Permanent Link |
"Robert" | "Roger Oliveira - RS/Brasil" <rogerhp@ibest.com.br> wrote in message news:5655EFC3-42A3-4101-9C56-C29BC98C5A9B@news.elevatesoft.com... > > But, this sql don't result what I want, agregate field, dont count just > excedido > 0 but all excedido all registry, I need just where excedido > > 0. > Yeah, both your and my SQL were wrong. Should not use count. Total excedido > 0 sum(if(excedido > 0 then excedido else 0)) quantidade number of records with excedido > 0 sum(if(excedido > 0 then 1 else 0)) quantidade Robert > thanks! > |
Wed, Sep 12 2007 10:20 AM | Permanent Link |
Roger Oliveira - RS/Brasil | THANKS!!!!!!! |
Wed, Sep 12 2007 1:13 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roger,
<< But, this sql don't result what I want, agregate field, dont count just excedido > 0 but all excedido all registry, I need just where excedido > 0. >> Sorry, I missed that. I see that Robert has corrected this for you, however. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |