SQL subquery getting multiple rows

Question

I have a query like this, where my sub query is fetching multiple rows.

    SELECT TOOL.ANUM,
       ASSET.ID,
       ASSET.DESCRIPTION,
       TOOL.DATE,
  (SELECT CASE frequnit
              WHEN 'WEEKS' THEN finish+(frequency*7)
              WHEN 'MONTHS' THEN add_months(finish, pfrequency)
              WHEN 'YEARS' THEN add_months(finish, (frequency * 12))
              ELSE actfinish+pluscfrequency
          END AS frequency
   FROM workorder
   WHERE worktype = 'CAL'
     AND pmnum IS NOT NULL
     AND assetnum = '6847'
     AND actfinish <
       (SELECT actfinish
        FROM workorder
        WHERE wonum = '2354850')) freq,
FROM tool,
     Asset
LEFT JOIN PM ON asset.num = pm.num
AND PM.status = 'ACV'
WHERE asset.anum = tool.anum
  AND tool.wo = '2354850'
ORDER BY tool.anum

My sub query results are:

SELECT wnum,finish
CASE frequnit
                  WHEN 'WEEKS' THEN finish+(frequency*7)
                  WHEN 'MONTHS' THEN add_months(finish, pfrequency)
                  WHEN 'YEARS' THEN add_months(finish, (frequency * 12))
                  ELSE actfinish+pluscfrequency
              END AS frequency
       FROM workorder
       WHERE worktype = 'CAL'
         AND pmnum IS NOT NULL
         AND assetnum = '6847'
         AND actfinish <
           (SELECT actfinish
            FROM workorder
            WHERE wonum = '2354850')

wnum    finish     frequency
10003   12-NOV-15  13-DEC-2015
10004   14-DEC-15  15-DEC-2015

But I need the freq value for max(finish) in the sub query. I am able to get this by using rownum as follows:

select * from (subquery order by finish ) where rownum=1;

But I am not supposed to use the above one as I am referring one of the main query column in the sub query

assetnum = '6847'(assetnum = TOOL.ANUM)

Is there any other way to do this? Thanks in advance.

Gordon Linoff · Accepted Answer · 2015-11-13 12:42:53Z

1

If you want the max, how about using this?

SELECT . . .
       (SELECT MAX(CASE frequnit
                     WHEN 'WEEKS' THEN finish+(frequency*7)
                     WHEN 'MONTHS' THEN add_months(finish, pfrequency)
                     WHEN 'YEARS' THEN add_months(finish, (frequency * 12))
                     ELSE actfinish+pluscfrequency
                   END) AS frequency
        FROM workorder
        WHERE worktype = 'CAL' AND
              pmnum IS NOT NULL AND
              assetnum = '6847' AND
              actfinish < (SELECT actfinish FROM workorder WHERE wonum = '2354850')
       ) freq,
. . .

answered Nov 13, 2015 at 12:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siva GV Over a year ago

Thanks Linoff. I need the freq value for max(finish) but not for max(finish+ freuency).

Collectives™ on Stack Overflow

SQL subquery getting multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related