Subquery using EXIST error 2

[rougex]

Hi there
I am using Reportsmith, which is a tool that writes SQL based on table selections, unfortunately it is not a very dynamic tool and doesnt allow for a lot of direct SQL entry. I Have one major Select below and I am trying to add the second select statement as a subquery but it is returning the following error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Does anyone have any advice?

SELECT
SELECT
MED.PLAN_TYPE, MED.BENEFIT_PLAN, MED.COVRG_CD
FROM
PS_HEALTH_BENEFIT MED
WHERE
((((MED.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_HEALTH_BENEFIT INNERALIAS
WHERE INNERALIAS.EMPLID = MED.EMPLID
AND INNERALIAS.PLAN_TYPE = MED.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
(MED.PLAN_TYPE = '10') AND
(MED.COVERAGE_ELECT = 'E') AND
((MED.COVERAGE_END_DT IS NULL) OR
(MED.COVERAGE_END_DT > GetDate())) AND
(MED.EMPLID = JOB.EMPLID))) ,
EE.EMPLID, EE.NAME, JOB.PAYGROUP, JOB.EMPL_RCD_NBR
FROM
PS_PERSONAL_DATA EE , PS_JOB JOB
WHERE
((((JOB.EFFSEQ= (
SELECT MAX(INNERALIAS.EFFSEQ)
FROM PS_JOB INNERALIAS
WHERE INNERALIAS.EMPLID = JOB.EMPLID
AND INNERALIAS.EMPL_RCD_NBR = JOB.EMPL_RCD_NBR
AND INNERALIAS.EFFDT = JOB.EFFDT)
AND
JOB.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_JOB INNERALIAS
WHERE INNERALIAS.EMPLID = JOB.EMPLID
AND INNERALIAS.EMPL_RCD_NBR = JOB.EMPL_RCD_NBR
AND INNERALIAS.EFFDT <= GetDate()))) AND
(JOB.FULL_PART_TIME = 'F') AND
(JOB.EMPL_STATUS IN( 'A', 'L', 'S'))))
AND
(EE.EMPLID = JOB.EMPLID)
ORDER BY
JOB.PAYGROUP, EE.NAME



(SELECT
MED.PLAN_TYPE, MED.BENEFIT_PLAN, MED.COVRG_CD
FROM
PS_HEALTH_BENEFIT MED
WHERE
((((MED.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_HEALTH_BENEFIT INNERALIAS
WHERE INNERALIAS.EMPLID = MED.EMPLID
AND INNERALIAS.PLAN_TYPE = MED.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
(MED.PLAN_TYPE = '10') AND
(MED.COVERAGE_ELECT = 'E') AND
((MED.COVERAGE_END_DT IS NULL) OR
(MED.COVERAGE_END_DT > GetDate())) AND
(MED.EMPLID = JOB.EMPLID))) )

 

[SQLSister]

Instead of using a where exists, make a derived table of the query. Subselects cannot have more than one result as the message says, but derived tables can. then join it to the other tables.
example

select t.field1, a.field2, t.field3
from table1 t
join
(select field1, field2 from table2 where field4 = 'test')a
on a.field1 = t.field1

The critical thing is to remember to alias the derived table (in this case I named it a).

Questions about posting. See faq183-874