Aggregate Query product -> Table LookUp 1

[Laeg]

SELECT CONVERT(BIGINT, (1000000000+9999999999)*RAND()) as NewPIN
WHERE NewPIN NOT IN (SELECT PIN FROM CustomerPins)

Invalid column name 'NewPIN'

How can I take the product of this aggregate query and then use it to do a lookup on another table?

 

[RiverGuy]

SELECT a.*
FROM
(SELECT CONVERT(BIGINT, (1000000000+9999999999)*RAND()) as NewPIN) a
WHERE NewPIN NOT IN (SELECT PIN FROM CustomerPins)

 

[Laeg]

Ta

 

[bborissov]

That query is not aggregated
But you can't use a newly created column in the WHERE clause, just because it didn't exists when WHERE clause is executes.

So, one option is to use derived table (as RiverGuy show you).
Other is to double the exact same expression in WHERE.

Opps!
I didn't saw that you use RAND(). So your ONLY option is to use RiverGuy solution

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.