Subquery Problem when pulling two fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery Problem when pulling two fields

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-11-05 : 17:22:16

The following code works fine:

SELECT Inmast.fpartno,
       Inmast.fdescript,
       Inmast.frev,
       Inonhd.fbinno,
       Inonhd.flocation,
       Inonhd.flot,
       coalesce((SELECT    TOP 1
               (q.fcdoc)
          FROM M2MDATA01.dbo.qalotc q
         WHERE q.fcpartrev = 'REP'
           AND q.fctype = 'J'
           AND inonhd.flot = q.fclot
        ORDER BY q.fddate DESC),'00000-00000')
          AS JobNo
  FROM       m2mdata01.dbo.inmast inmast
          JOIN
             dbo.inonhd inonhd
          ON Inonhd.fac = Inmast.fac
         AND Inonhd.fpartno = inmast.fpartno
         AND Inonhd.fpartrev = Inmast.frev
       LEFT OUTER JOIN
          dbo.InOnHdDl
       ON InOnHdDl.fiInOnHdID = InOnHd.identity_column
 WHERE Inmast.fsource NOT IN ('P', 'F')
   AND inonhd.fpartrev = 'REP'
   AND inonhd.flocation = 'FR'

When I try to change the nested query to pull another value:

SELECT Inmast.fpartno,
       Inmast.fdescript,
       Inmast.frev,
       Inonhd.fbinno,
       Inonhd.flocation,
       Inonhd.flot,
       (SELECT    TOP 1
               coalesce((q.fcdoc), '00000-00000'), J.fsono
          FROM    M2MDATA01.dbo.qalotc q
               LEFT JOIN
                  M2MDATA01.dbo.jomast J
               ON J.fjobno = q.fcdoc
         WHERE q.fcpartrev = 'REP'
           AND q.fctype = 'J'
           AND inonhd.flot = q.fclot
        ORDER BY q.fddate DESC)
          AS JobNo
  FROM       m2mdata01.dbo.inmast inmast
          JOIN
             dbo.inonhd inonhd
          ON Inonhd.fac = Inmast.fac
         AND Inonhd.fpartno = inmast.fpartno
         AND Inonhd.fpartrev = Inmast.frev
       LEFT OUTER JOIN
          dbo.InOnHdDl
       ON InOnHdDl.fiInOnHdID = InOnHd.identity_column
 WHERE Inmast.fsource NOT IN ('P', 'F')
   AND inonhd.fpartrev = 'REP'
   AND inonhd.flocation = 'FR'

I get the following error:

SQL Server Database Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Suggestions?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-05 : 17:27:44

This


coalesce((SELECT    TOP 1
               (q.fcdoc)
          FROM M2MDATA01.dbo.qalotc q
         WHERE q.fcpartrev = 'REP'
           AND q.fctype = 'J'
           AND inonhd.flot = q.fclot
        ORDER BY q.fddate DESC),'00000-00000')
          AS JobNo

stands for exactly 1 Column in the result set and you cannot add another column here.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 23:08:36

seeme like what you want is this

SELECT Inmast.fpartno,
       Inmast.fdescript,
       Inmast.frev,
       Inonhd.fbinno,
       Inonhd.flocation,
       Inonhd.flot,tmp.col1,tmp.fsono
          AS JobNo
  FROM       m2mdata01.dbo.inmast inmast
          JOIN
             dbo.inonhd inonhd
          ON Inonhd.fac = Inmast.fac
         AND Inonhd.fpartno = inmast.fpartno
         AND Inonhd.fpartrev = Inmast.frev
       LEFT OUTER JOIN
          dbo.InOnHdDl
       ON InOnHdDl.fiInOnHdID = InOnHd.identity_column
INNER JOIN (SELECT    TOP 1
               coalesce((q.fcdoc), '00000-00000') as col1, J.fsono,q.fclot
          FROM    M2MDATA01.dbo.qalotc q
               LEFT JOIN
                  M2MDATA01.dbo.jomast J
               ON J.fjobno = q.fcdoc
         WHERE q.fcpartrev = 'REP'
           AND q.fctype = 'J'
        ORDER BY q.fddate DESC)tmp
ON tmp.fclot=inonhd.flot 
WHERE Inmast.fsource NOT IN ('P', 'F')
   AND inonhd.fpartrev = 'REP'
   AND inonhd.flocation = 'FR'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources