IIF,ISNULL in transact sql

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)

IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown', DATEPART(yyyy.SalesAnalyse.Verz

Hello,
I think i am missing something. This code give's an error... Can someone
tell me what it is'
IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown',
DATEPART(yyyy.SalesAnalyse.Verzenddatum))
tnx in advance..
eric1) IIF is not a function is T-SQL.
2) ISNULL accepts 2 arguments, not 1.
3) DATEPART accepts 2 arguments; separate the datepart (yyyy) from the date
with a comma, not a period.
I assume you want this?
ISNULL(CAST(DATEPART(yyyy, SalesAnalyse.Verzenddatum) AS VARCHAR(8)),
'unknown')
Jacco Schalkwijk
SQL Server MVP
"Judith van der Niet" <jniet@.mit.com> wrote in message
news:e5QpjKfDFHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I think i am missing something. This code give's an error... Can someone
> tell me what it is'
> IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown',
> DATEPART(yyyy.SalesAnalyse.Verzenddatum))
> tnx in advance..
> eric
>

IIF in SQL

hello!
In MS ACCESS 2000, I can use the IIF function like (
SELECT IIF(x>0, "True","False") from x_table.
I tried the ISNULL but got a wrong ouput.
How can I do this also in MS SQL?
Please help...
Any effort is higly appreciated.
Thanksselect case when x>0 then 'True' else 'False' end
from x_table

rudy

IIF & IsNull Functions

Below in "ACCESS SQL CODE" is a query that I use in Access. It uses the IIf
and IsNull functions to concatenate the full name. If there is a middle
initial, my statement returns a comma after the LastName field, the middle
initial followed by a period. If it doesn't exist, sql returns nothing.
I'm upsizing the access database to an access adp project file and
converting my queries into views. When I try to run the below "SQL VIEW
CODE", I get an error saying "The isnull function requires 2 arguments".
I thought my syntax was right. What am I doing wrong? Is the IIf function
causing the problem in my Access ADP view?
ACCESS SQL CODE *************
SELECT LastName, FirstName, MiddleInit, LastName & ", " & FirstName &
IIf(IsNull(MiddleInit),""," " & MiddleInit & ".") AS Name
FROM myTable
SQL VIEW CODE ****************
SELECT FirstName, t_Users.MiddleInit,
LastName + ", " + FirstName + IIf(IsNull(MiddleInit),'',' ' + MiddleInit &
'.') AS Name
FROM myTableOn Wed, 26 Apr 2006 16:54:07 -0500, scott wrote:

>SELECT FirstName, t_Users.MiddleInit,
>LastName + ", " + FirstName + IIf(IsNull(MiddleInit),'',' ' + MiddleInit &
>'.') AS Name
>FROM myTable
Hi Scott,
IIf and IsNull are Access-specific functions that won't work in SQL
Server or in any ANSI-compliant relational database.
SQL Server also has an ISNULL function; it's function is roughly the
same as Access' Nz function. And it''s allso non-ANSI-compliant.
The ANSI-compliant version of yoour query (that will work on SQL Server
and other ANNSI-compliant databases, but not on Access) is
SELECT FirstName, t_Users.MiddleInit,
LastName + ', ' + FirstName + COALESCE(' ' + MiddleInit + '.'),
'') AS Name
FROM myTable
Hugo Kornelis, SQL Server MVP|||thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:5ssv42tvikim4oi8cnl3b6taln8bq76hhf@.
4ax.com...
> On Wed, 26 Apr 2006 16:54:07 -0500, scott wrote:
>
> Hi Scott,
> IIf and IsNull are Access-specific functions that won't work in SQL
> Server or in any ANSI-compliant relational database.
> SQL Server also has an ISNULL function; it's function is roughly the
> same as Access' Nz function. And it''s allso non-ANSI-compliant.
> The ANSI-compliant version of yoour query (that will work on SQL Server
> and other ANNSI-compliant databases, but not on Access) is
> SELECT FirstName, t_Users.MiddleInit,
> LastName + ', ' + FirstName + COALESCE(' ' + MiddleInit + '.'),
> '') AS Name
> FROM myTable
> --
> Hugo Kornelis, SQL Server MVP