Re: Subselect in SELECT

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Jan 2007 15:14:18 -0800
Message-ID: <1169334858.642407.267580@38g2000cwa.googlegroups.com>

Matthias Matker wrote:
> Hello together,
>
> SELECT
> persons.name
> ( SELECT COUNT(*) amount FROM person_cds WHERE ( person_cds.pname= persons.name))
> FROM
> persons
> WHERE ( amount< 280)
> ORDER by persons.name;
> EXIT;
>
>
> I want the name of the persons ( name is primary key of persons), which have fewer cds than 280.
>
> After executing this query, I got the Message,
>
> "amount" invalied identifier.
>
> Can you explain me, why?
>
> Thanks

Quick check:
SELECT
  *
FROM
  V$RESERVED_WORDS
WHERE
  KEYWORD='AMOUNT'; (No rows)

Next check, are we using a column alias in an ORDER BY, GROUP BY, or HAVING clause? It looks like that may be the problem.

How can you work around the problem? Join the two tables together, use the GROUP BY clause, and a HAVING clause.

For example, assume that I have a table with a list of months and lists every day in the month. I have another table with the number of cars sold per month. I want to find out how many of each car is sold in months with at least 30 days. I can set up a SQL statement like this:

CAR_SALES:

---

CAR_MODEL
SALES_MONTH
CARS_SOLD MONTH_DATES:

---

SALES_MONTH
DAY_NUMBER SELECT

  CS.CAR_MODEL,
  CS.SALES_MONTH,
  CS.CARS_SOLD,

  COUNT(*) NUMBER_OF_DAYS
FROM
  CAR_SALES CS,
  MONTH_DATES MD
WHERE
  CS.SALES_MONTH=MD.SALES_MONTH
GROUP BY

  CS.CAR_MODEL,
  CS.SALES_MONTH,
  CS.CARS_SOLD

HAVING
  COUNT(*) >= 30; Use the same logic to solve your problem.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jan 20 2007 - 17:14:18 CST