Chris Bowlby wrote:
> Hi Colin,
>
> Thanks for your response, if I remove the where clause from my example,
> I also am able to execute the query with out issue, as follows:
>
> test=# select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
> "substring"(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FROM
> my_lot_test ilch) AS tab limit 1;
> dr_prod_date
> --------------
> 2007-01-01
> (1 row)
>
That syntax is incorrect for substring(). Use:
substr(ilch.lot_id::text, 5, 1)
or:
substring(ilch.lot_id::text FROM 5 FOR 1)