SQL search with 'union'

[bITs.EA]

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

[qwerty]

Surely depends on the DB you use, I guess.

q.

[bITs.EA]

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'