RE: sql help needed

Thanks Charles that fixed it....mucho appreciative.


On Wed, 30 Nov 2005 15:33:43 -0500, Wilt, Charles wrote
> 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.
> > 
> >
> 
> -- 
> 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.