Re: having problems with a simple query

On Thu, 24 May 2001, Tad Naworal wrote:

>
> This returns an Attribute 'acquisition' not found error
>
>
> SELECT trx_date,
> store_id || rgstr_id || trx_num AS invoice,
> substring(po_num from 1 for (position('/' in po_num) - 1)) AS po,
> substring(po_num from (position('/' in po_num) + 1) for (char_length(po_num) - position('/' in po_num))) AS acquisition
> FROM SS_AHST WHERE cus_id = '410BGEHD47' AND acquisition LIKE '%A1158%' ORDER BY trx_date DESC
>
>
> does anyone know what I'm doing wrong
>
> Thanks
> Tad

(I think that) the name acquisition is an alias you give to an expression that is evaluated for the rows that will pass the WHERE clause conditions. So you cannot use this alias to refer to the evaluated expression while you are still trying to figure out which rows to perform it on.
If you replicate the expression in you WHERE clause and it'll be fine.

WHERE ... AND substring(po_num from (position('/' in po_num) + 1) for (char_length(po_num) - position('/' in po_num))) LIKE '%A1158%'...

cheers,
thalis