Tags: access, database, distinct, fldnbrrcpts, iif, isnull, microsoft, mysql, oracle, qryrcp, query, rcp_receiptqty, rcp_vendorid, server, sql, sum, transact, vbaselect

IIF,ISNULL in transact sql

On Database » Microsoft SQL Server

5,878 words with 5 Comments; publish: Wed, 30 Apr 2008 16:09:00 GMT; (25078.13, « »)

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

All Comments

Leave a comment...

  • 5 Comments
    • Check out the ISNULL() and COALESCE functions in BooksOnLine.

      Andrew J. Kelly SQL MVP

      "Sql Fren" <SqlFren.sqlserver.todaysummary.com.discussions.microsoft.com> wrote in message

      news:16728166-FF7F-4BEB-86E1-D045B4801F0F.sqlserver.todaysummary.com.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

      #1; Wed, 30 Apr 2008 16:10:00 GMT
    • 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.sqlserver.todaysummary.com.microsoft.com, "Sql Fren"

      <SqlFren.sqlserver.todaysummary.com.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

      #2; Wed, 30 Apr 2008 16:11:00 GMT
    • 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)

      #3; Wed, 30 Apr 2008 16:12:00 GMT
    • 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

      #4; Wed, 30 Apr 2008 16:13:00 GMT
    • 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.sqlserver.todaysummary.com.pe_NO_rFact.in_SPAM_fo> wrote in message

      news:7jql31po7vvbvsu0q5eo2bgdmmus870567.sqlserver.todaysummary.com.

      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)

      #5; Wed, 30 Apr 2008 16:14:00 GMT