RE: sql help needed

Douglas,
        If you see my earlier post, you cannot group by a column name if
that column is a formula, you must group by the formula itself (note
that the opposite is true for order by). If you replace 

Group by period

With the following

group by 
case
    when ilninvdate between '02/01/2005' and '10/31/2005' then 1
    when ilninvdate between '11/01/2005' and '11/30/2005' then 2
 ...
    else 0
 end

it should run ok.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
Sent: Wednesday, November 30, 2005 3:18 PM
To: Midrange Systems Technical Discussion
Subject: Re: sql help needed

One thing I have not quite figured out is that when I run this in the
sql 
interactive query I get an error 

COLUMN ILNINVDATE or EXPRESSION in SELECT list not valid.

any idea what would be causing that?


On Wed, 30 Nov 2005 16:50:07 -0300, Raul A. Jager W. wrote
> select sum(ilneprice) suma, case
>    when ilninvdate between '02/01/2005' and '10/31/2005' then 1
>    when ilninvdate between '11/01/2005' and '11/30/2005' then 2
> ...
>    else 0
> end as period
> from...
> where..
> group by period
> 
> You will get a row for each period.
>
________________________________________________________________________
__
> Douglas W. Palme wrote:
> 
> >Its been a few weeks since I last posted, and its been a over a week 
since I 
> >was even at work....actually I kind of missed a lot of you :) Trying
to 
get 
> >some rest and recoup time in a hospital bed just does not seem to
ever 
work.
> >
> >Alas, I am back, working on another stupid SQL problem.  If someone
could 
> >point me in the right direction on this I would appreciate it.
> >
> >I need to extract a sum of our line items for two separate date
ranges, 
IE: 
> >
> >select sum(ilneprice) as current from speclf where ilninvdate 
>'02/01/2005' 
> >and ilninvdate <='10/31/2005'
> >
> >That works just fine, but I also need to extract a sum for the same
field 
> >(ilneprice) for a different date range....is it possible to do this 
without 
> >having to run the query two separate times? Because I also need to 
extract 
> >some other information and group it by location codes and item
numbers....
> >
> >Any help would be appreciated.
> >
> >Douglas
> >
> >
> >If you bought it, it was hauled by a truck - somewhere, sometime.
> >
> >  
> >
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/midrange-l or email: 
> MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment 
> to review the archives at http://archive.midrange.com/midrange-l.


If you bought it, it was hauled by a truck - somewhere, sometime.