Re: sql help needed

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