Nest Query Question. - SQL Server Forums

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

Nest Query Question.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-21 : 10:51:50

Here is my code:

SELECT poitem.fpartno,
       poitem.frev,
       (If  EXISTS(SELECT poitem_ext.[SALESORDERNO]
                     FROM    [M2MData01].dbo.poitem_ext poitem_ext
                          INNER JOIN
                             [M2MData01].dbo.poitem IQpoitem
                          ON IQpoitem.[identity_column] =
                                poitem_ext.[FKey_ID]
                         AND IQpoitem.[identity_column] =
                                poitem.identity_column)
          THEN 1


       ELSE
             0
       END)  
			 
  FROM    M2MDATA01.dbo.pomast pomast
       INNER JOIN
          M2MDATA01.dbo.poitem poitem
       ON pomast.fpono = poitem.fpono
 WHERE (poitem.frelsno <> '  0' OR poitem.fmultirls <> 'Y')
   AND pomast.fstatus = 'OPEN'
   AND poitem.FLSTPDATE <= '01/01/2009 12:00:00 AM'
GROUP BY poitem.fpartno, poitem.frev

What I intend to do is to check if the subquery returns a value. If so, I will have a calculation to check the purchased dollar value. If not, it's a different calculation. I put in 1 and 0 as place holders for now. This doesn't work and SQL complains that I have incorrect syntax near the word 'If' and 'then'.

I also attempted it as a Case When statement but that didn't work either.

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 10:56:20

quote:

Originally posted by DavidChel

Here is my code:

SELECT poitem.fpartno,
       poitem.frev,
       (CASE WHEN  (SELECT COUNT(poitem_ext.[SALESORDERNO])
                     FROM    [M2MData01].dbo.poitem_ext poitem_ext
                          INNER JOIN
                             [M2MData01].dbo.poitem IQpoitem
                          ON IQpoitem.[identity_column] =
                                poitem_ext.[FKey_ID]
                         AND IQpoitem.[identity_column] =
                                poitem.identity_column))>0
          THEN 1


       ELSE
             0
       END)  
			 
  FROM    M2MDATA01.dbo.pomast pomast
       INNER JOIN
          M2MDATA01.dbo.poitem poitem
       ON pomast.fpono = poitem.fpono
 WHERE (poitem.frelsno <> '  0' OR poitem.fmultirls <> 'Y')
   AND pomast.fstatus = 'OPEN'
   AND poitem.FLSTPDATE <= '01/01/2009 12:00:00 AM'
GROUP BY poitem.fpartno, poitem.frev

Ty with case as above

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-21 : 11:12:10

Thank you Visakh16.

After I removed an extra offending ) I get a different error that I encounter all too frequently.

SQL Server Database Error: Column 'poitem.identity_column' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

However, when I try to sum it, I get the following error:

SQL Server Database Error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I'm working in SQL 2000. There's something fundamental that I don't understand here. How can I get a list of part numbers, revisions, and the dollars associated with them as returned by the sub query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 11:15:48

Can you post your full query please? i cant spot 'poitem.identity_column' in above query.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-21 : 11:21:35

This is the code with the SUM function. What I want is a list of something like


fpartno   frev  
Widget    000   300,000
Dohickey  001   250,000

SELECT poitem.fpartno,
       poitem.frev,
       sum(CASE WHEN  (SELECT COUNT(poitem_ext.[SALESORDERNO])
                     FROM    [M2MData01].dbo.poitem_ext poitem_ext
                          INNER JOIN
                             [M2MData01].dbo.poitem IQpoitem
                          ON IQpoitem.[identity_column] =
                                poitem_ext.[FKey_ID]
                         AND IQpoitem.[identity_column] =
                                poitem.identity_column)>0
          THEN 1


       ELSE
             0
       END)  
			 
  FROM    M2MDATA01.dbo.pomast pomast
       INNER JOIN
          M2MDATA01.dbo.poitem poitem
       ON pomast.fpono = poitem.fpono
 WHERE (poitem.frelsno <> '  0' OR poitem.fmultirls <> 'Y')
   AND pomast.fstatus = 'OPEN'
   AND poitem.FLSTPDATE <= '01/01/2009 12:00:00 AM'
GROUP BY poitem.fpartno, poitem.frev

When I run that code, I get the following error:

SQL Server Database Error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 11:27:25

[code]SELECT fpartno,frev,sum(YourValue) AS SumVal
FROM
(SELECT poitem.fpartno,
poitem.frev,
CASE WHEN (SELECT COUNT(poitem_ext.[SALESORDERNO])
FROM [M2MData01].dbo.poitem_ext poitem_ext
INNER JOIN
[M2MData01].dbo.poitem IQpoitem
ON IQpoitem.[identity_column] =
poitem_ext.[FKey_ID]
AND IQpoitem.[identity_column] =
poitem.identity_column)>0
THEN 1

ELSE
0
END AS YourValue

FROM M2MDATA01.dbo.pomast pomast
INNER JOIN
M2MDATA01.dbo.poitem poitem
ON pomast.fpono = poitem.fpono
WHERE (poitem.frelsno <> ' 0' OR poitem.fmultirls <> 'Y')
AND pomast.fstatus = 'OPEN'
AND poitem.FLSTPDATE <= '01/01/2009 12:00:00 AM'
)t
GROUP BY fpartno,frev[/code]

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-21 : 11:50:12

You're a genius man. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 11:54:18

quote:
Originally posted by DavidChel

You're a genius man. Thanks.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources