The isnull function requires 2 arguments.

[zuza]

Dear all,
I will apreciate if somebody could help me on this one :

SELECT qryPubication.ClaimNo, qryPubication.StreetNameP AS StrA,
qryPubication.StreetNoP AS StrB,
IIf(IsNull([StrB]),[StrA],(LTrim([StrA])+", "+[StrB])) AS StrC,
IIf(IsNull(lTrim([BuildNo]))," ",(lTrim([Buildno]))) AS Buil,
IIf(IsNull(lTrim([Entrnop]))," ",(lTrim([Entrnop]))) AS Entra,
IIf(IsNull(lTrim([Floornop]))," ",(lTrim([Floornop]))) AS Flo,
IIf(IsNull(lTrim([Apartnop]))," ",(lTrim([Apartnop]))) AS Appno,
[Strc]+[buil]+","+[entra]+","+[flo]+"/"+[appno] AS Reslt,
qryPubication.CityTownVillageP, qryPubication.MunicipalityP,
qryPubication.ParcelNoP
FROM qryPubication
ORDER BY qryPubication.CityTownVillageP;

* This is working in Access mdb file - I can't make it working in sql server 2000. Can somebody find where is the problem by modifying this ! Is showing me 'The isnull function requires 2 arguments.'

Regards,
ZuZa

 

[vongrunt]

There is isNull() function in SQL2k but it behaves a bit different.
IIf doesn't exist - it can be replaced with CASE/WHEN construct:

M$ Access: IIf( isNull(blah), some_value, some_other_value ) as foo
SQL Server: CASE WHEN blah is null THEN some_value ELSE some_other_value END AS foo

If - and only if - blah and some_other_value are identical, you can use isNull(blah, 'some_value').

Also - use ' for string literals. " is reserved for object identifiers by default.

 

[JohnDTampaBay]

The SQL Server IsNull function only accepts 2 parameters. Parameter 1 is the value to check for null; parameter 2 is the value to substitute it parameter 1's value is null.

Also, SQL Server doesn't allow you to refer to expressions by name. You have to repeat the expression. How does this work for you? Good luck!

SELECT qryPubication.ClaimNo
  , qryPubication.StreetNameP AS StrA
  , qryPubication.StreetNoP AS StrB
  , qryPubication.StreetNameP +
      IsNull(qryPubication.StreetNoP + ', ', '')
    AS StrC
  , IsNull(LTrim(BuildNo), ' ') AS Buil
  , IsNull(LTrim(Entrnop), ' ') AS Entra
  , IsNull(LTrim(Floornop), ' ') AS Flo
  , IsNull(LTrim(Apartnop), ' ') AS Appno
  , qryPubication.StreetNameP +
      IsNull(qryPubication.StreetNoP + ', ', '')
    + IsNull(LTrim(BuildNo), ' ') + ','
    + IsNull(LTrim(Entrnop), ' ') + ','
    + IsNull(LTrim(Floornop), ' ') + '/'
    + IsNull(LTrim(Apartnop), ' ')
  AS Reslt
  , qryPubication.CityTownVillageP
  , qryPubication.MunicipalityP
  , qryPubication.ParcelNoP
FROM qryPubication
ORDER BY qryPubication.CityTownVillageP

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[ESquared]

IsNull in SQL server does not return a simple true or false like in Access. Instead, it does something similar to the Access construction:

[tt]Iif(IsNull(Expression),ValueIfNull,Expression)[/tt]

So you can change

[tt]IIf(IsNull(lTrim([Entrnop]))," ",(lTrim([Entrnop])))[/tt]

to

[tt]IsNull(LTrim([Entrnop]),'')[/tt]

Note that in SQL Server, string literals are enclosed in single quotes and not double quotes, unless you play with the QUOTED_IDENTIFIER setting.

-------------------------------------
A sacrifice is harder when no one knows you've made it.