More help for dummies needed please.
I have trying to get this work for a particular purpose.
I have got the query and both sides of the union seem to work in MS SQL enterprise manager.
When I preview the lookup I get one record with value 1 and blank in the next column (so I can't say if it returns null, but that part returns null in MS SQL tool)
In the attribute properties everyhing goes as instructed until I hit Save. Then I get the message that a read only attribute must have the default specified. I did select the record for default without problem, but apparently that is not considered to have a value. What might be the way to deal with that? My query is below
SELECT
@SELECT:1:DUMMY@
,@SELECT:X.SUCCESSOR_IDS:SUCCESSOR_IDS@
FROM
(( SELECT Distinct PPRED.PRPREDTASKID TASK_ID,
( SELECT cast(PRSUCCTASKID as varchar) + ', ' AS 'data()'
FROM PRDEPENDENCY SUCC
WHERE SUCC.PRPREDTASKID = PPRED.PRPREDTASKID
FOR XML PATH('') ) SUCCESSOR_IDS FROM PRDEPENDENCY PPRED WHERE cast(PPRED.PRPREDTASKID as varchar) = @WHERE:PARAM:USER_DEF:STRING:task_prid@)
UNION
SELECT 1 field2, cast(null as varchar) from DUAL
WHERE @WHERE:PARAM:USER_DEF:STRING:task_prid@ is NULL
) X
WHERE @FILTER@