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 Dynamic Inserts with SQL

I'm try to a multiple insert from one database to another by using this code:

insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type)
(selecttop 30 acno,surname,name,id,type
from [PretoriaDB].[dbo].[WorkingDB])

but I keep on getting this error:

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Can any one help!!

If the target table will always be empty, you can just drop the table before the insert and use a "Select Into" statement. Otherwise, you will probably have to open a cursor for the "select top 30 ..." statement and iterate over it to insert each record to the table.

|||

tmametja:

String or binary data would be truncated.

the Error says that...

One of the fields on table [mpis].[dbo].[Residents] of type varchar/char is beingfed data that is too long. i.e. One of the Firleds in [PretoriaDB].[dbo].[WorkingDB] that you are usingto populate the [mpis].[dbo].[Residents] probably contains a string value that is too large for the field thatyou are trying to plug it into.

hope it helps to solve you problem./.

|||

i didn't know 2005 had an "INSERT...SELECT" statement... cool

Anyhow, looks like the syntax is INSERT [table_name] SELECT [colA], [colB] ... FROM [table_name_or_join]

I didn't see any "INTO" in the examples, or any mention of the target column list on the target table as in a tradional insert statement.

|||

tmametja:

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Make sure data types (in INSERT and SELECT) are match and field length as well.

Good luck.

|||

thanx

|||

thanxkaushalparik27

|||

tmametja:

thanxkaushalparik27

well pleasure to help you, and dont forget to mark the answers which helped you and resolve the thread. Thanx

multiple cascade

Hello,
I need to do a relation between 2 tables with this request (under SQL server
2005)
ALTER TABLE "dbo"."PrmBox"
ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
But I have the error : ... may cause cycles or multiple cascade paths.
SQL sever thinks there is a problem, but really there is no problem.
1) Is it possible to disabled this check ?
2) if no, I want to do this relation with a trigger, for the delete there is
no problem, but for the update I don't know how to do the relation between
the deleted and inserted tables to find the old data and the new data in the
table OrgSet.
ThanksAlan
CREATE TABLE GF --GRANDFATHERS
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO GF VALUES (1,'A')
INSERT INTO GF VALUES (2,'B')
INSERT INTO GF VALUES (3,'C')
CREATE TABLE F --FATHERS
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE NO ACTION ON
UPDATE NO ACTION,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO F VALUES (1,1,'AA')
INSERT INTO F VALUES (2,1,'AA')
INSERT INTO F VALUES (3,2,'BB')
INSERT INTO F VALUES (4,2,'BB')
INSERT INTO F VALUES (5,2,'BB')
INSERT INTO F VALUES (6,3,'CC')
DELETE FROM GF WHERE [ID]=1--Cause the error to be thrown
ALTER TABLE F NOCHECK CONSTRAINT FK__F__GFID__54968AE5
--SP_HELPCONSTRAINT 'F'
DELETE FROM GF WHERE [ID]=1-- No error
SELECT * FROM F
DROP TABLE F
DROP TABLE GF
"Alan" <Alan@.discussions.microsoft.com> wrote in message
news:85A0DB79-CACA-4050-B80A-66015BC87D93@.microsoft.com...
> Hello,
> I need to do a relation between 2 tables with this request (under SQL
> server
> 2005)
> ALTER TABLE "dbo"."PrmBox"
> ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
> REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
> But I have the error : ... may cause cycles or multiple cascade paths.
> SQL sever thinks there is a problem, but really there is no problem.
> 1) Is it possible to disabled this check ?
> 2) if no, I want to do this relation with a trigger, for the delete there
> is
> no problem, but for the update I don't know how to do the relation between
> the deleted and inserted tables to find the old data and the new data in
> the
> table OrgSet.
>
> Thanks|||There is a problem with the cascade, I need to change the value of the link
in the linked table (Father in your example).
"Uri Dimant" wrote:

> Alan
> CREATE TABLE GF --GRANDFATHERS
> (
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
> )
> INSERT INTO GF VALUES (1,'A')
> INSERT INTO GF VALUES (2,'B')
> INSERT INTO GF VALUES (3,'C')
> CREATE TABLE F --FATHERS
> (
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE NO ACTION ON
> UPDATE NO ACTION,
> [NAME]CHAR(2) NOT NULL
> )
>
> INSERT INTO F VALUES (1,1,'AA')
> INSERT INTO F VALUES (2,1,'AA')
> INSERT INTO F VALUES (3,2,'BB')
> INSERT INTO F VALUES (4,2,'BB')
> INSERT INTO F VALUES (5,2,'BB')
> INSERT INTO F VALUES (6,3,'CC')
> DELETE FROM GF WHERE [ID]=1--Cause the error to be thrown
> ALTER TABLE F NOCHECK CONSTRAINT FK__F__GFID__54968AE5
> --SP_HELPCONSTRAINT 'F'
> DELETE FROM GF WHERE [ID]=1-- No error
> SELECT * FROM F
> DROP TABLE F
> DROP TABLE GF
>
>
>
> "Alan" <Alan@.discussions.microsoft.com> wrote in message
> news:85A0DB79-CACA-4050-B80A-66015BC87D93@.microsoft.com...
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||On Thu, 2 Feb 2006 02:04:26 -0800, Alan wrote:

>Hello,
>I need to do a relation between 2 tables with this request (under SQL serve
r
>2005)
>ALTER TABLE "dbo"."PrmBox"
> ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
> REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
>But I have the error : ... may cause cycles or multiple cascade paths.
>SQL sever thinks there is a problem, but really there is no problem.
>1) Is it possible to disabled this check ?
Hi Alan,
No. I think that this limitation is a result of some limitation in the
internal mechanism used to perform cascading operations.

>2) if no, I want to do this relation with a trigger,
Make sure to use an INSTEAD OF trigger. Since constraints are checked
*bofore* executing a trigger, an AFTER trigger would never even be fired
on a DELETE or UPDATE that would violate the constraint.

>for the delete there is
>no problem, but for the update I don't know how to do the relation between
>the deleted and inserted tables to find the old data and the new data in th
e
>table OrgSet.
You can't - unless you have some other column (or combinations of
columns) that is certain to be unique and not to change. (An IDENTITY
column with a UNIQUE constraint would be great).
In an UPDATE trigger, there is no way to distinguish these two UPDATE
statements, unless other columns in the rows can be used.
UPDATE MyTable SET KeyColumn = 3 - KeyColumn WHERE KeyColumn IN (1, 2)
UPDATE MyTable SET KeyColumn = KeyColumn WHERE KeyColumn IN (1, 2)
In both cases, both the inserted and the deleted table will hold two
rows, with KeyColumn equal to 1 in one of the rows and 2 in the other
one.
If you can't correlate the rows by other columns, you'll have to limit
update operations on the primary key to single-row operations - start
the trigger with
IF UPDATE(KeyColumn)
AND ROWCOUNT > 1
BEGIN
RAISERROR (...)
ROLLBACK TRAN
RETURN
END
Hugo Kornelis, SQL Server MVP