With clause in SQl statement

Transact-SQL
1

Hi,

I have a recursive with clause, which works fine. I need this clause to fill one column in my table.

simple example:

Select

a.name,

(with test AS (
Select b.data where b.field=a.field) AS test

from destr a

it seems that such a construct is not possible....is there another way ..construct to use with clause for a result table as base?

Best regards,

Lara

2

From your posting, it is not clear the construct you are trying to use, and what table is aliased to b. Perhaps the following? If that is not it, posting a more representative query with sample data would help.

SELECT
	a.name,
	b.data
FROM
	destr a
	OUTER APPLY
	(
		SELECT x.data
		FROM
			sometable AS x
		WHERE
			x.field = a.field
	) AS b
3

Hi,

thanks for your answer....yes it is kind of what I need....I show you my full example:

WITH Objects AS (

select

DISTINCT A2.RightID,
A3.Type,
A3.Name

from dtree A1

INNER JOIN dtreeacl A2 ON

A1.dataid=A2.dataid

INNER JOIN kuaf A3 ON

A3.id=A2.RightID

Where A1.dataid IN

(Select an.dataid from dtreeancestors an Where an.ancestorid=2000)

AND

(A2.See=3 OR A2.See=4)

AND

A3.Deleted=0

),

GroupMembers (ParentGroup, Name, ID, Type, Level) AS
(
SELECT KUAF.Name, KUAF.Name, KUAF.ID, KUAF.Type, 0 AS Level FROM KUAF WHERE ID IN (Select RightID from Objects)

UNION ALL

SELECT GP.Name, KUAF.Name, KUAF.ID, KUAF.Type, Level+1 AS Level FROM KUAF 
INNER JOIN KUAFChildren KC ON KC.ChildID = KUAF.ID
INNER JOIN GroupMembers GP ON GP.ID = KC.ID

)

Select count(RightID) AS "Number User Write Permissions" FROM

(

Select RightID from Objects where Type=0

UNION

Select ID AS RightID from GroupMembers where Type=0

) t

This works perfect, but only for one ID as you can see in this term: an.ancestorid=2000

No I have a list of Id's and want do the whole operation for each ID. This is the source...

Select ID from List

I hope you have another hint for me?

Best regards,

Lara

4 
WITH ancestors
AS (
SELECT an.dataid
FROM dtreeancestors an
WHERE an.ancestorid IN (SELECT ID FROM LIST)
)
, Objects AS (
...
...
{replace (Select an.dataid from dtreeancestors an Where an.ancestorid=2000) with (Select an.dataid From ancestors an)
...