When can you refer to columns by number in where clause? 1

[CapsuleCorpJX]

select count(*) from (
select <col1>
from <db>.<table1>
where <col2> between <val1> and <val2>
minus
select <col3>
from <db>.<table2>) z
where z.1 IS NOT NULL;
;

Basically I want to count the number of rows in the nested select, but only rows that are not null.

This query doesn't work, it does work when I replace z.1 with z.<col1>, however I'm not sure if its doing what I want, and if <col1> should be used.

I was wondering if refering columns by numbers only works with the 'group by' clause.

 

[dnoeth]

It works in Order By and Group By.

You have to use the column alias you provide in the Derived Table:

select count(*) from
(
select <col1> as x
from <db>.<table1>
where <col2> between <val1> and <val2>

minus

select <col3>
from <db>.<table2>
) z
where x IS NOT NULL

Dieter