Author Topic: SQL search with 'union'  (Read 2240 times)

bITs.EA

  • EA User
  • **
  • Posts: 80
  • Karma: +2/-0
    • View Profile
SQL search with 'union'
« 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

qwerty

  • EA Guru
  • *****
  • Posts: 11202
  • Karma: +268/-248
  • I'm no guru at all
    • View Profile
Re: SQL search with 'union'
« Reply #1 on: July 14, 2016, 12:37:57 am »
Surely depends on the DB you use, I guess.

q.

bITs.EA

  • EA User
  • **
  • Posts: 80
  • Karma: +2/-0
    • View Profile
Re: SQL search with 'union'
« Reply #2 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'
« Last Edit: July 14, 2016, 01:03:56 am by bITs.EA »