AnsweredAssumed Answered

Using "WITH-AS" construct in nsql?

Question asked by samos on Dec 1, 2009
Latest reply on Dec 3, 2009 by samos
Hi All,  I'm trying to construct a clarity-nsql-acceptable version of my working sqlserver query, which uses the 'with-as' construct at the beginning of the query. (ref With on MSDN site)  The reason for the with-as construct is to TRY to avoid the use of a custom function to be written that returns this same table of data. Why? I'd like to try make this portlet nsql as upgrade-safe as i can possibly think of (*)  The overall basic construct of my working sql is as follows:    with temp_table_name (col1, col2,... ,coln) as
(
select ...
union
select ...
union ...
select ...
)

select ttn.*, count(ttn2.coly)
from temp_table_name ttn
left join temp_table_name ttn2 ON (ttn.colx = ttn2.coly)
group by ttn.col1, ttn.col2, ...., ttn.coln   I'm trying to then put all the NSQL constructs around this, but the combinations that I've tried so far all give an error of some sort... eg:-must start with "select" statement  Has anyone else successfully got a "with-as" construct working with nsql for a portlet?  Any pointers appreciated  Regards,Sam.

Outcomes