I have this query in ACCESS VBA:
SELECT DISTINCT qryRCP.RCP_VendorID, Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1))
AS fldNbrRcpts,
Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
fldNbrRejScrap
FROM qryRCP
WHERE qryRCP.RCP_ReceiptQty >=0
GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
qryRCP.VEN_StatusCode
Can anyone help to convert it in sql?
I tried to change nz to isnull, but still hitting syntaz error.
I need to run in sql query analyzer first to find out the problem.
Thanks lotCheck out the ISNULL() and COALESCE functions in BooksOnLine.
Andrew J. Kelly SQL MVP
"Sql Fren" <SqlFren@.discussions.microsoft.com> wrote in message
news:16728166-FF7F-4BEB-86E1-D045B4801F0F@.microsoft.com...
>I have this query in ACCESS VBA:
> SELECT DISTINCT qryRCP.RCP_VendorID,
> Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1))
> AS fldNbrRcpts,
> Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
> fldNbrRejScrap
> FROM qryRCP
> WHERE qryRCP.RCP_ReceiptQty >=0
> GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
> qryRCP.VEN_StatusCode
> Can anyone help to convert it in sql?
> I tried to change nz to isnull, but still hitting syntaz error.
> I need to run in sql query analyzer first to find out the problem.
> Thanks lot|||
SELECT
RCP_VendorID,
SUM(RCP_ReceiptQty), -- because of WHERE clause,
-- this can't possibly be NULL
SUM(COALESCE(SumOfRCQ_RejectQty,0) + COALESCE(SumOfRCQ_ScrapQty,0))
FROM qryRCP
WHERE RCP_ReceiptQty >= 0
GROUP BY RCP_VendorID
-- your other GROUP BY columns are illegal here, since they're
-- not part of the query at all!
On 3/17/05 10:49 PM, in article
16728166-FF7F-4BEB-86E1-D045B4801F0F@.microsoft.com, "Sql Fren"
<SqlFren@.discussions.microsoft.com> wrote:
> SELECT DISTINCT qryRCP.RCP_VendorID, Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1
))
> AS fldNbrRcpts,
> Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
> fldNbrRejScrap
> FROM qryRCP
> WHERE qryRCP.RCP_ReceiptQty >=0
> GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
> qryRCP.VEN_StatusCode|||On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:
(snip)
>-- your other GROUP BY columns are illegal here, since they're
>-- not part of the query at all!
Hi Aaron,
That doesn't make them illegal. There's no requirement to include all
group by columns in the select list (though omitting then might make the
output of the query useless - I have a hard time imagining a scenario
where it'd be useful).
use pubs
go
select max(zip)
from authors
group by state
go
95688
46403
66044
20853
48105
97330
37215
84152
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:
> -- your other GROUP BY columns are illegal here, since they're
> -- not part of the query at all!
Say what?
From SQL2K Books Online:
|| GROUP BY Clause
|| Specifies the groups into which output rows are to be placed and, if
|| aggregate functions are included in the SELECT clause <select list>,
|| calculates a summary value for each group. When GROUP BY is specified,
|| either each column in any non-aggregate expression in the select list
|| should be included in the GROUP BY list, or the GROUP BY expression
|| must match exactly the select list expression.
I read this as saying that you can't have a non-aggregate expression in the
SELECT list that isn't in the GROUP BY clause - but it says nothing about
having an expression in the GROUP BY clause that isn't in the SELECT list!
Of course, the result isn't very meaningful - how can you tell which rows
correspond to which group by value if the group by value isn't returned -
but I just ran this in query analyzer
select job, count(map) maps, sum(qty) qtys
from ttOrdClubItem
group by job,club
and got the same result as this:
select job, maps, qtys
from (
select job, club, count(map) maps, sum(qty) qtys
from ttOrdClubItem
group by job,club
)
so the construction is definitely "legal", at least|||Oh gosh, semantics. Sorry, I should have said useless, stupid, meaningless,
bizarre, weird, unexpected... any others I'm missing?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7jql31po7vvbvsu0q5eo2bgdmmus870567@.
4ax.com...
> On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:
> (snip)
> Hi Aaron,
> That doesn't make them illegal. There's no requirement to include all
> group by columns in the select list (though omitting then might make the
> output of the query useless - I have a hard time imagining a scenario
> where it'd be useful).
> use pubs
> go
> select max(zip)
> from authors
> group by state
> go
>
> --
> 95688
> 46403
> 66044
> 20853
> 48105
> 97330
> 37215
> 84152
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
