SQL The isnull function requires 2 arguments.

[HotDog61]

I want to concatenate 5 address fields into 'address'. I want to add a comma between each field. I want to bypass 'prstr2' if it is nulls. Here is my statement:

select prcnm, rtrim(prstr1) + ', ' + IsNull(prstr2, '', rtrim(prstr2) + ', ') + rtrim(prcty1)
+ ', ' + rtrim(prsta1) + ' ' + rtrim(przip1) as address, prjob1, prsrg, jbjob, spspnm
from prpms, prpjb, prpsp
where prjob1 = '01118' and jbjob = prjob1 and prtec = ' ' and pren = spen
order by pren desc

I'm getting the 'The isnull function requires 2 arguments' error.

 

[xlbo]

If this is for SQL Server Reporting Services, you are better off doing this in a UDF in the report itself IMHO

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GeertVerhoeven]

The ISNULL works with two parameters. The definition is ISNULL(field, replacementValue).

If the value is null, the value of replacementValue will be returned, otherwise the original value is returned.

So for your example you can better use a case structure:
select prcnm, rtrim(prstr1) + ', ' +

CASE WHEN prstr2 IS NOT NULL THEN RTRIM(prstr2) + ', ' END

+ rtrim(prcty1)
+ ', ' + rtrim(prsta1) + ' ' + rtrim(przip1) as address, prjob1, prsrg, jbjob, spspnm
from prpms, prpjb, prpsp
where prjob1 = '01118'

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog