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

[Maxime Richez]

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