Recursive Common Table Expressions on Clarity MS SQL 2005

[Anon Anon]

Can anyone confirm for me if Recursive Common Table Expressions work in NSQL portlets on a Clarity MS SQL 2005 based environment ?

Thanks,
Ben

[Deactivated User]

Not really familiar with those my understanding is that if you use a Wrapper and put standard sql inside it most of the things you can use in a query work.
See also
Table Functions - NSQL on steroids
2290223


Martti K.

[Anon Anon]

I was reading this thread that talks about it...

2295334

It appears that there is an issue with MS-SQL. It was not definitive though. Also my hope is that since it was back in 2009.... Maybe something has changed since then ? Hopefully in my favor.

We are currently on SQL server 2005 but should be going to 2008 some time this summer. My guess is that it is not so much an issue with SQL server becuase CTE is supported in 2005 and my query works when run against 2005. It's just when I try to wrap it in NSQL that I have issues. So I think it is more of a Clarity issue than a SQL server issue. We are on Clarity 12.1.1.1208 4 003. My hope is that maybe on this version of Clarity it works where the folks on the thread were talking back in 2009 and would have had a much earlier version of Clarity.

I'm thinking about maybe cutting a case with this question.

[Anon Anon]

Cut a case with CA Support a week or so ago and they have been less then helpful telling me it is a services question. All I was looking for was a yes or no answer if CTE can be used in Portlet NSQL in a SQL Server Clarity environment.. They said I should also check with the forum... Can anybody let me know if this is possible or not ?

Also.... seeing as the CTE approach seems to be headed for a dead end.... does anyone have any good way to roll up data from Task --> Project --> Program ? I would be open to any ideas !!!

Thanks,
Ben

[Anon Anon]

Hey Ben - funnily enough I hit this problem last week converting some Oracle portlets to MSSQL (2008 for the record)...

I failed to solve it though, I put this down to the way MSSQL processed the query.

What I found was where I had a NSQL portlet on Oracle using the "wrapper" technique like this

@SELECT some NSQL stuff@
FROM
(
WITH my_tab as
( SELECT some other SQL stuff FROM somewhere)
SELECT something from my_tab
)
WHERE @FILTER@

then that just did not parse in MSSQL - it seems that it does not let me wrap the "WITH" bit - (I can use the "WITH" clause in MSSQL but it seems to need to be the 'outermost' level, no use to me in a portlet). :sad

So I gave in and recoded the NSQL as

@SELECT some NSQL stuff@
FROM
(
SELECT something from ( SELECT some other SQL stuff FROM somewhere) my_tab
) my_select
WHERE @FILTER@

which makes my code really unreadable now :sad

So I too would be interested if someone else has some expert-MSSQL insight into this
(I'd never even logged into a SQLServer database until last week, so I disqualify myself from any expertise in this area! cool) )

--

EDIT - and reading that thread which you referenced above (which I had posted in originally!!!!) confirms what i was hitting last week. :unsure:

[Anon Anon]

Thanks Dave for confirming my suspicions. Sorry that we are in the same boat together... Although you seem like a good guy to hang out with ! :smile You have saved my bacon on a few occasions.

Now if someone that has been in this boat before could share their solution to the problem we would be very much in their debt.

Anyone have any solutions they could share ?

[Anon Anon]

I realize this thread is old, but it's the closest topic to what I'm trying to do as well. I tried Dave's suggestion of the nested select because WITH-AS doesn't work for me either (even though I think we are on Oracle 11g)

I tried to pare it down to something simple like the query below but I'm still getting an error building this query in Clarity:

select @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:name:obsname@,
from
(
select id, name, parent_id, depth
from PRJ_OBS_UNITS
where id = 5000205
) temptab
WHERE @FILTER@

Attached is a screen of the error. Any ideas on if i'm missing anything here?

[Anon Anon]

No - your problem here is just simple typo/syntax - you have a trailing comma at the end of your first line, remove the comma and you'll be ok.

--

The "WITH" stuff works just fine in Oracle, I use it all the time.

[Anon Anon]

That worked. Thank you!