Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <20979455.post@talk.nabble.com>
Date: Fri, 12 Dec 2008 08:58:53 -0800 (PST)
From: MassimoH <MassimoHeitor@gmail.com>
To: derby-user@db.apache.org
Subject: Re: VALUES Query Question
In-Reply-To: <wjo8abb1hf3q.fsf@clustra.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <20970481.post@talk.nabble.com>
 <wjo8abb1hf3q.fsf@clustra.norway.sun.com>


Thanks so much to both of you!

I actually wanted to do a cumulative YTD type query (each row has cumulative
data for all months before it), so it was hard to do that with just a simple
GROUP BY. This syntax works perfectly:

SELECT MONTHS.V
	, (SELECT SUM(SALES.QUANTITY)
	FROM SALES
	WHERE YEAR(BEGINDATE) = 2008
	AND MONTH(BEGINDATE) <= MONTHS.V) AS Sales
FROM (VALUES 1,2,3,4,5,6,7,8,9,10,11,12) AS MONTHS(V);


Knut Anders Hatlen-4 wrote:
> 
> MassimoH <MassimoHeitor@gmail.com> writes:
> 
>> I want to do a query like this:
>>
>> SELECT Months.????, (SELECT SUM(Amount) FROM Sales WHERE SaleMonth =
>> Months.????) AS Sales
>> FROM (VALUES '2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01',
>> '2008-05-01', '2008-06-01', '2008-07-01') AS Months;
>>
>> However, when I use VALUES in a sub-query, I don't see how to reference
>> the
>> column. Basically, how can I get the above query to work?
> 
> Hi Massimo,
> 
> You can also rename columns with the AS clause. For instance, you can
> replace "AS Months" with "AS Months(x)", and then you can reference the
> column as Months.x.
> 
> -- 
> Knut Anders
> 
> 

-- 
View this message in context: http://www.nabble.com/VALUES-Query-Question-tp20970481p20979455.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.