Обсуждение: having problems with a simple query

Поиск
Список
Период
Сортировка

having problems with a simple query

От
"Tad Naworal"
Дата:
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

Re: having problems with a simple query

От
"Thalis A. Kalfigopoulos"
Дата:
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
theWHERE clause conditions. So you cannot use this alias to refer to the evaluated expression while you are still
tryingto 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