Complicated Select Statement 2

[Imakeoil]

Here is a select statement I have been given by someone else (who is not available to answer why it doens't work).

select eqmtid
from ( select distinct eqmtid, eqmtid as sortorder
from hist_eqmtlist
where eqmtid is not null
union all select 'All Equipment' , NULL)

The error being returned is

Incorrect syntax near ')'.

I am creating an @param that will allow the end user to choose which eqmtid they woukd like to see the report for with the option of chosing 'All Equipment'

Thanks

 

[MarkXaml]

select eqmtid 
from	(select distinct eqmtid, eqmtid as sortorder           
		from hist_eqmtlist         
		where eqmtid is not null 
		union all 
		select 'All Equipment' , NULL) as x

try giving the subquery an alias.

Good luck!

Mark

 

[Imakeoil]

Thank you Mark. This worked. Iappreciate your quick response. Cheers

 

[SQLSister]

And just for the record, this is a derived tbale not a subquery, that's why it wants the alias, sql server needs to know what to call the table.

"NOTHING is more important in a database than integrity." ESquared

 

[Imakeoil]

Thanks for the info SQLSister. One of my biggest challenges is knowing the lingo, and therefore being able to do relevant searches etc.

 

[djj55]

SQLSister, Thanks for that explanation. I have known you need to have the alias but did not know why.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!