Select dynamically a partition in an external table

[Etienne Dumoulin]

Hi,

I have an external table partitioned per day, I have currently one
partition.

When I launch:

SELECT max(day) from MY_EXTERNAL_TABLE GROUP BY DAY;

It returns nothing.

I would like to use this query to select a partition dynamically:

  SELECT ID,
                 PROPERTY
    FROM MY_EXTERNAL_TABLE
    WHERE day IN (
            SELECT max(day)
            FROM MY_EXTERNAL_TABLE
            WHERE day <= '2012-09-07'
            GROUP BY DAY
        )

Do I do something wrong?
Is there a way around?

Thanks,

Étienne