[Openroad-users] Group by date_trunc(...) in Openroad

[Maxime Richez]

Thank you Paul, now it works !!!
 

  _____  

De : openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] De la part de White, Paul
Envoyé : lundi 6 novembre 2006 14:22
À : 'International OpenROAD Users'
Objet : Re: [Openroad-users] Group by date_trunc(...) in Openroad



Have you tried execute immediate? 


-----Original Message----- 
From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com]On Behalf Of Maxime Richez 
Sent: Monday, November 06, 2006 11:55 PM 
To: openroad-users at peerlessit.com 
Subject: Re: [Openroad-users] Group by date_trunc(...) in Openroad 


I was on holiday... So no urgency ;-) 
Thanks Brigitte, Chris for your help... 

I hoped not to use a view but if it's the only way... 
But my query is working in sql.. Not a bug ??? 
  

-----Message d'origine----- 
De : Brigitte Duplenne [mailto:brigitte.duplenne at ingres.com] 
Envoyé : lundi 6 novembre 2006 3:34 
À : 'Chris Wallace' 
Cc : maxime.richez at saluc.com 
Objet : RE: [Openroad-users] Group by date_trunc(...) in Openroad 

Hi Chris, 

You are right. 
The result Maxime I guess wants to have will not be given by his request. 
I just focused on the compilation error. 
If you set II_4GL_DECIMAL to ',', you will get the compilation error Maxime 
is getting. 
As Maxime is working in Belgium he is probably using the ',' as decimal 
separator as we do in France also. (By experience I know the problem :-)) 

It was late last night when I replied so I didn't try to analyze further the

code, I thought Maxime was also working on the week-end and was stopped by 
that error. Then after I replied I realized the mail had been sent on Friday

so there was no urgency :-) 

Sorry by that. 

Brigitte 




-----Original Message----- 
From: Chris Wallace [mailto:chrisw at miswa.com.au] 
Sent: dimanche 5 novembre 2006 17:46 
To: brigitte.duplenne at ingres.com 
Subject: RE: [Openroad-users] Group by date_trunc(...) in Openroad 

Brigitte, 

I don't believe you can use the result of a function in a Group By. 

The workaround is to create a View and select from the View with a Group 
by: 

   CREATE VIEW VW_SALES AS 
        select date_trunc('day',date_mvt) as date_mvt, item, quantity 
        from sales; 

then 

   SELECT date_mvt AS :date_mvt, item AS :item, sum(quantity) AS :qty 
   FROM VW_SALES 
   GROUP BY date_mvt, item 
   ORDER BY 1,2; 

Regards 
Chris Wallace 

Management Information Systems (WA) Pty Ltd Level 9, MIS House 
231 Adelaide Terrace, PERTH  WA  6000 
Ph: (08) 9221-9221  Fax: (08) 9221-9224 
Email: chrisw at miswa.com.au 
Web: http://www.miswa.com.au 

Disclaimer:  Management Information Systems (MIS) This e-mail is private and

confidential. If you are not the intended recipient, please advise us by 
return e-mail immediately, and delete the e-mail and any attachments without

using or disclosing the contents in any way. The views expressed in this 
e-mail are those of the author, and do not represent those of MIS unless 
this is clearly indicated.  You should scan this e-mail and any attachments 
for viruses.  MIS accepts no liability for any direct or indirect damage or 
loss resulting from the use of any attachments to this e-mail. 
-----Original Message----- 
From: openroad-users-bounces at peerlessit.com 
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Brigitte 
Duplenne 
Sent: Sunday, 5 November 2006 2:27 PM 
To: 'International OpenROAD Users' 
Subject: Re: [Openroad-users] Group by date_trunc(...) in Openroad 

Hi Maxime, 

You are probably using ',' for decimal separator. 
In that case you to have a space before and after the comma. 
"... order by 1,2" will indeed not compile but "... Order by 1 , 2" will 
compile. 

Brigitte 
  

-----Original Message----- 
From: openroad-users-bounces at peerlessit.com 
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Maxime Richez 
Sent: vendredi 3 novembre 2006 01:00 
To: openroad-users at peerlessit.com 
Subject: [Openroad-users] Group by date_trunc(...) in Openroad 

Hi everybody, 
  
Here's my little problem with Openroad 4.1 and sql (ingres) 
  
procedure example= 
begin 
   select date_trunc('day',date_mvt) as :date_mvt, 
             item as :item, 
             sum(quantity) as :qty 
   from sales 
   group by date_mvt,item 
   order by 1,2 
end; 
  
  
How to use the group by with "date_trunc('day',date_mvt)" 
  
I can't use "group by 1,2" neither "group by date_trunc('day',date_mvt)" 
  
Openroad -> E_W400F0 Syntax error.  The last symbol read was '1' 
  
Thanks for your help ! 
  
Maxime Richez 
Saluc SA - Belgium 

_______________________________________________ 
Openroad-users mailing list 
Openroad-users at peerlessit.com 
http://peerlessit.com/mailman/listinfo/openroad-users 


_______________________________________________ 
Openroad-users mailing list 
Openroad-users at peerlessit.com 
http://peerlessit.com/mailman/listinfo/openroad-users 




_______________________________________________ 
Openroad-users mailing list 
Openroad-users at peerlessit.com 
http://peerlessit.com/mailman/listinfo/openroad-users 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://peerlessit.com/pipermail/openroad-users/attachments/20061106/ee7c7bcb/attachment.html