db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jayaram Subramanian <rsjay1...@gmail.com>
Subject Re: Buddy Testing of Using Clause
Date Thu, 06 May 2010 10:35:19 GMT
Hi
Thanks for clarification.I validated both scenarios mentioned
yesterday and verified that the code going through without issues..
Also as a testing on aggregate functions i was trying the following

When i tried
ResultSet rs=s.executeQuery("SELECT country FROM COUNTRIES JOIN CITIES
USING (COUNTRY) where cities.country='UNITED STATES'"), the qiuery
went through without issues

When i tried
ResultSet rs=s.executeQuery("SELECT country,count(country) FROM
COUNTRIES JOIN CITIES USING (COUNTRY) group by country") the query
went through

But when i tried
ResultSet rs=s.executeQuery("SELECT country,count(country) FROM
COUNTRIES JOIN CITIES USING (COUNTRY) group by cities.country")

It gave
"Column reference 'COUNTRY' is invalid, or is part of an invalid
expression.  For a SELECT list with a GROUP BY, the columns and
expressions being selected may only contain valid grouping expressions
and valid aggregate expressions."

\Just curious in finding out the reason for this behaviour...

With Regards
Jayaram


On Wed, May 5, 2010 at 7:43 AM, Knut Anders Hatlen <Knut.Hatlen@sun.com> wrote:
> On 05/05/10 11:48, Jayaram Subramanian wrote:
>> Hi,
>> As per the review comments i used toursdb and executed the following
>> queries and found them going through without issues
>>
>> SELECT countries.* FROM COUNTRIES JOIN CITIES USING (COUNTRY)
>> SELECT distinct countries.country,cities.country FROM COUNTRIES JOIN
>> CITIES USING (COUNTRY)
>> SELECT distinct countries.country FROM COUNTRIES LEFT JOIN CITIES
>> USING (COUNTRY) WHERE CITIES.COUNTRY IS NULL
>>
>> But i have a few clairifications in the following 2 scenarios
>> Scenario 1
>> =================
>> When i tried doing
>> SELECT  * FROM FLIGHTAVAILABILITY JOIN FLIGHTS ON (FLIGHT_ID) WHERE
>> FLIGHT_DATE > '2004-04-20'
>> I got an error message stating "Column name 'FLIGHT_ID' is in more
>> than one table in the FROM list.  Do we need to issue the query
>> differently?
>
> I think there's a typo in the query. It says ON instead of USING. If I
> change it from ON to USING, it works.
>
>> Scenario 2
>> ============
>> Given a scenario where we have to get flights originating from a given
>> city (Ex:Seattle)
>> How can we use the "using" clause in this scenario given that the
>> column name is "ORIG_AIRPPORT" in flights table and "AIPRORT" in the
>> cities table?...
>
> That's trickier... :) But you can write such a query with USING if you
> assign a correlation name to the column with an AS clause:
>
> select flight_id from
>  flights AS fl(flight_id, segment_number,
>                airport, depart_time,
>                dest_airport, arrive_time,
>                meal, flying_time,
>                miles, aircraft)
>  join cities using (airport)
>  where city_name = 'Seattle'
>
> or
>
> select flight_id from
>  (select flight_id, orig_airport AS airport from flights) f
>  join cities using (airport)
>  where city_name = 'Seattle'
>
>
> --
> Knut Anders
>

Mime
View raw message