Multiple IF statements

I am getting syntax errors on the following SQL. Can anyone help? Thanks.
IF COALESCE(dbo.People.Address1,'') = ''
BEGIN
FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) +
COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
ELSE
IF COALESCE(dbo.People.Address2,'') = ''
BEGIN
FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
ELSE
BEGIN
FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo.People.City,'') +
N', ' + COALESCE(dbo.People.State,'') + N' ' +
COALESCE(dbo.People.ZipCode,'')
END,
DavidThere's no context for the IF statement. It refers to dbo.People.Address1, b
ut with not SELECT
statement. For which row do you want to perform these operations?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:eQ4GrdEGFHA.3336@.TK2MSFTNGP10.phx
.gbl...
>I am getting syntax errors on the following SQL. Can anyone help? Thanks.
> IF COALESCE(dbo.People.Address1,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) + COALESCE(dbo
.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' ' + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> IF COALESCE(dbo.People.Address2,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo
.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' ' + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo
.People.Address1,'') +
> Chr(13) + COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State
,'') + N' ' +
> COALESCE(dbo.People.ZipCode,'')
> END,
> David
>|||David C wrote:
> I am getting syntax errors on the following SQL. Can anyone help? Thanks
.
> IF COALESCE(dbo.People.Address1,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) +
> COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N'
'
> + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> IF COALESCE(dbo.People.Address2,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
> COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N'
'
> + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
> COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo.People.City,'')
+
> N', ' + COALESCE(dbo.People.State,'') + N' ' +
> COALESCE(dbo.People.ZipCode,'')
> END,
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
This is in the SELECT clause of a query? In that case use a CASE
instead of the If..Else:
CASE WHEN COALESCE(People.Address1,'') = ''
THEN FullAddress = COALESCE(People.Address2,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
WHEN COALESCE(People.Address2,'') = ''
THEN FullAddress = COALESCE(People.Address1,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
ELSE FullAddress = COALESCE(People.Address1,'') + Chr(13)
+ COALESCE(People.Address2,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
END,
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQho7HIechKqOuFEgEQKg9wCfe3HzCvdQbhvK
e0SY8QfpdGvXj10AoKzV
QM1G41rQNsUIxqDkCYbAxIza
=J+7B
--END PGP SIGNATURE--|||I just wanted the IF statement to refer to a returned field named
FullAddress.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Your code failed but code below worked:
FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
WHEN COALESCE(dbo.People.Address2,'') = ''
THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David wrote:

> FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
> THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> WHEN COALESCE(dbo.People.Address2,'') = ''
> THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> END
or simply
FullAddress =
COALESCE(dbo.People.Address1 + Char(13) + Char(10),'')
+ COALESCE(dbo.People.Address2 + Char(13) + Char(10),'')
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
Dieter|||On Mon, 21 Feb 2005 13:00:35 -0800, David wrote:

>Your code failed but code below worked:
>FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
> THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> WHEN COALESCE(dbo.People.Address2,'') = ''
> THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
>END
Hi David,
You can simplify this:
FullAddress = COALESCE(dbo.People.Address1 + Char(13) + Char(10), '')
+ COALESCE(dbo.People.Address2 + Char(13) + Char(10), '')
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
By the way: if city is NULL, the last line will look like this:
", IL 12345"
If State is NULL, the last line will look like this:
"Smallville, 12345" (note the two spaces)
I'm not sure if that is really what you intend...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I don't think the single line will work because it will always bring
back something from 1st 2 lines and I don't want that. I think your
examples will always bring back Char(13) + Char(10) if either Address1
or Address2 is Null.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||On Mon, 21 Feb 2005 14:41:58 -0800, David wrote:

>I don't think the single line will work because it will always bring
>back something from 1st 2 lines and I don't want that. I think your
>examples will always bring back Char(13) + Char(10) if either Address1
>or Address2 is Null.
Hi David,
No, it won't (unless you have changed your settings to non-ANSI-standard
NULL handling). Did you try it?
There's a big difference between
(a) COALESCE (columnname + char(13) + char(10), '')
and
(b) COALESCE (columnname, '') + char(13) + char(10)
In (a), the CrLf (Carriage Return [char(13)] + Line Feed [char(10)]) will
be concatenated to the column's value first, then the result is checked
against NULL and if it is, it's replaced by an empty string. Since
concatenation of CrLf to a NULL string results in a NULL string, the end
result of (a) will be the empty string if the column holds a NULL.
In (b), the column's value is first checked against NULL and replaced by
the empty string, then CrLf gets added. Concatenation of CrLf to the empty
string will result in a string holding just CrLf.
This being said, I must add that there will be a difference if your data
actually holds rows where Address1 or Address2 is filled with an empty
string. If that's the case, I'd strongly suggest you to change that - you
should represent unknown or missing data in one consistent way, not mix up
various ways. Decide to use either the empty string or NULL if an address
line is missing, then clean up data and introduce either a NOT NULL
constraint or a CHECK (Addres1 <> '') constraint to prevent future entry
of malformed data.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)