Sparx Systems Forum

Enterprise Architect => Bugs and Issues => Topic started by: bITs.EA on July 14, 2016, 12:15:25 am

Title: SQL search with 'union'
Post by: bITs.EA on July 14, 2016, 12:15:25 am

Hi

I'm trying to do a UNION search, but I don't get any results. The 2 separate queries give me the expected results, but using UNION doesn't seem to work. EA isn't giving an error either.

Code: [Select]

(SELECT p.Name AS 'Process', o.stereotype AS 'ElementType', o.Name AS 'Element', null AS 'Opgenomen in Project?', null AS 'Owner?'
FROM t_object AS o INNER JOIN t_package AS p ON o.package_ID = p.package_ID
WHERE
o.package_ID IN (SELECT package_ID FROM t_package WHERE parent_ID = (SELECT package_ID FROM t_package WHERE name = 'Bedrijfsleiders'))
AND
o.StereoType <> ''
ORDER BY 'Process', 'ElementType')

UNION

(SELECT d.Name AS 'Process', 'Document' AS 'ElementType', o.name AS' Element', null AS 'Opgenomen in Project?', null AS 'Owner?'
FROM (t_object AS o INNER JOIN t_diagramobjects AS do ON o.object_ID = do.object_ID) INNER JOIN t_diagram AS d ON do.diagram_ID = d.diagram_ID
WHERE o.package_ID = (SELECT package_ID FROM t_package WHERE name = 'Documenten')
ORDER BY 'Process')
Can someone give me some advice?

Grts

S

Title: Re: SQL search with 'union'
Post by: qwerty on July 14, 2016, 12:37:57 am

Surely depends on the DB you use, I guess.

q.

Title: Re: SQL search with 'union'
Post by: bITs.EA on July 14, 2016, 12:59:45 am

It's SQL Server DB. I've found my error: ORDER BY can't be in the first query.

Working SQL query:

Code: [Select]

SELECT p.Name AS 'Process', o.stereotype AS 'ElementType', o.Name AS 'Element', null AS 'Opgenomen in Project?', null AS 'Owner?'
FROM t_object AS o INNER JOIN t_package AS p ON o.package_ID = p.package_ID
WHERE
o.package_ID IN (SELECT package_ID FROM t_package WHERE parent_ID = (SELECT package_ID FROM t_package WHERE name = 'Bedrijfsleiders'))
AND
o.StereoType <> ''

UNION

SELECT d.Name AS 'Process', 'Document' AS 'ElementType', o.name AS' Element', null AS 'Opgenomen in Project?', null AS 'Owner?'
FROM (t_object AS o INNER JOIN t_diagramobjects AS do ON o.object_ID = do.object_ID) INNER JOIN t_diagram AS d ON do.diagram_ID = d.diagram_ID
WHERE o.package_ID = (SELECT package_ID FROM t_package WHERE name = 'Documenten')

ORDER BY 'Process'