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)

Multiple Datasets

I have created several reports that use multiple datasets. I have
found two errors and I need help.
First, is there a way to define the order in which the datasets are
executed? In some of my reports, the first dataset populates a table
that the remaining datasets query from. However, when I run the
report, it is obvious that it executes some of the datasets that query
from the table BEFORE it runs the dataset that actually populates the
table.
Second, it seems like when my users run a report with multiple datasets
from the front-end, it only runs the one of the datasets and uses the
cashed results for the remaining datasets. For example, I have a
report that counts calls and mail received per a certain account. The
account is a parameter that the user selects. If they run the report
for Account A, then dataset 1 returns a value of 500, dataset B returns
a value of 250 and dataset C returns a value of 100. Then, when the
user runs the report for Account B, dataset 1 returns a value of 999
(which is the correct result), but dataset 2 returns a value of 500
(the result for Account A, not Account B) and dataset 3 returns a value
of 100 (the result for Account A, not Account B). The only way to get
the report to execute all three datasets, is by running the report
once, then hitting the refresh button. Anyone else have this problem?
I would appreciate any responses.
Thanks!You can not count on order of execution. RS is not setup to have
dependencies between datasets like you are trying to do. If that is what you
want then you should create subreports and use them instead. Everything
should work exactly as you want if you do that.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ronni" <rlnjones@.yahoo.com> wrote in message
news:1106243121.671668.240810@.c13g2000cwb.googlegroups.com...
> I have created several reports that use multiple datasets. I have
> found two errors and I need help.
> First, is there a way to define the order in which the datasets are
> executed? In some of my reports, the first dataset populates a table
> that the remaining datasets query from. However, when I run the
> report, it is obvious that it executes some of the datasets that query
> from the table BEFORE it runs the dataset that actually populates the
> table.
> Second, it seems like when my users run a report with multiple datasets
> from the front-end, it only runs the one of the datasets and uses the
> cashed results for the remaining datasets. For example, I have a
> report that counts calls and mail received per a certain account. The
> account is a parameter that the user selects. If they run the report
> for Account A, then dataset 1 returns a value of 500, dataset B returns
> a value of 250 and dataset C returns a value of 100. Then, when the
> user runs the report for Account B, dataset 1 returns a value of 999
> (which is the correct result), but dataset 2 returns a value of 500
> (the result for Account A, not Account B) and dataset 3 returns a value
> of 100 (the result for Account A, not Account B). The only way to get
> the report to execute all three datasets, is by running the report
> once, then hitting the refresh button. Anyone else have this problem?
> I would appreciate any responses.
> Thanks!
>