Evaluate a subquery to use it as a Filter

If you mean:

SELECT
  MONTH(sales_dt) AS mth
, SUM(amount) AS sales
FROM f_sales
WHERE cust_id IN (
  SELECT cust_id
  FROM d_cust
  WHERE age BETWEEN 55 AND 60
)
GROUP BY 1
;

that's how it's done. But what is the question, really?

Hi Marco, Mike,

I was actually wondering if there is a way to tell Vertica to evaluate the subquery beforehand and use a filter operator instead of a join.
so that if
SELECT cust_id
FROM d_cust
WHERE age BETWEEN 55 AND 60
returns 1,2,3

The optimizer will evaluate the subquery and will run this query:
SELECT
MONTH(sales_dt) AS mth
, SUM(amount) AS sales
FROM f_sales
WHERE cust_id IN (1,2,3)
GROUP BY 1
;
But knowing if there is a query hint to preform the subquery first would help too.
Many Thanks
Chaima

What is your goal here? Every query in Vertica is treated separately. So Vertica is running that query separately. If you're trying to change the explain plan to get a Merge join, or something like that, the IN() clause is going to screw that up, and a subquery is going to behave like an IN() clause.