× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.