RE: sql help needed

Yes,

Raul's method won't work.

When you use a column function, every column in the statement must
either be used in a column function or be in the group by clause.

Since you are using ilninvdate in the CASE stsatement, it expects to see
it in the group by function.  However, if you put it there you won't get
the results you want.

Instead, you'll need to use a common table expression:

With sumTbl as (select ilneprice, 
                       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 speclf
                Where ..
                )
Select sum(ilneprice) as current, period
>From sumTbl
Where period <> 0
Group by period


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----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.
> 
> -- 
> 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.
> 
>