BOL ABS Overflow error?

Ugh... Why does books online state that ABS can cause an overflow error?

The example given:

The ABS function can produce an overflow error. For example, the tinyint data type can hold only values that range from 0 to 255:

Copy Code SELECT ABS(CONVERT(tinyint, -256)); GO

It's the convert function that causes the overflow error, not ABS itself, right?

Hi Robert,

You're correct. It is the CONVERT function that is causing the overflow error in this example. Thanks for pointing that error out. We'll update the topic with a corrected example in a future update to Books Online.

Here's a better example.

DECLARE @.si smallint

SET @.si = -32768

SELECT @.si = ABS(@.si)

Regards,

Gail

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error
?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.http://support.microsoft.com/defaul...kb;en-us;309392
"Joe K." wrote:

> What could cause the error listed below. Is there away to resolve this err
or?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.|||Have a look here:
http://support.microsoft.com/defaul...b;en-us;Q309392
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
http://support.microsoft.com/default...b;en-us;309392
"Joe K." wrote:

> What could cause the error listed below. Is there away to resolve this error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
|||Have a look here:
http://support.microsoft.com/default...;en-us;Q309392
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.http://support.microsoft.com/default.aspx?scid=kb;en-us;309392
"Joe K." wrote:
> What could cause the error listed below. Is there away to resolve this error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.|||Have a look here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q309392
--
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

Blocking and return parameters

Is it possible the existence of a return parameter on an SP running
insert statements would cause blocking to happen (any removing it
might help eliminate it?)
The SPs in question have three INSERT INTO statements using an
@.@.IDENTITY to help populate the second and third tables based on the
PK of the newly inserted record in the first INSERT INTO Statement.
I have recently stared to receive timeout errors in a multi-user
enviroment and wonder if that's part of the cause. In this
environment, users are requerying a table that is also being written
to. The writes are infrequent but the reads of that table are very
frequent.
On the read side I have added Select from TableX WITH (READPAST)...
On the write side I have elimnated the return parameters. I'm hoping
these two approaches help.
Help is greatly appreciated.
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> Is it possible the existence of a return parameter on an SP running
> insert statements would cause blocking to happen (any removing it
> might help eliminate it?)
> The SPs in question have three INSERT INTO statements using an
> @.@.IDENTITY to help populate the second and third tables based on the
> PK of the newly inserted record in the first INSERT INTO Statement.
> I have recently stared to receive timeout errors in a multi-user
> enviroment and wonder if that's part of the cause. In this
> environment, users are requerying a table that is also being written
> to. The writes are infrequent but the reads of that table are very
> frequent.
> On the read side I have added Select from TableX WITH (READPAST)...
> On the write side I have elimnated the return parameters. I'm hoping
> these two approaches help.
> Help is greatly appreciated.
> lq

I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> Is it possible the existence of a return parameter on an SP running
> insert statements would cause blocking to happen (any removing it
> might help eliminate it?)
> The SPs in question have three INSERT INTO statements using an
> @.@.IDENTITY to help populate the second and third tables based on the
> PK of the newly inserted record in the first INSERT INTO Statement.
> I have recently stared to receive timeout errors in a multi-user
> enviroment and wonder if that's part of the cause. In this
> environment, users are requerying a table that is also being written
> to. The writes are infrequent but the reads of that table are very
> frequent.
> On the read side I have added Select from TableX WITH (READPAST)...
> On the write side I have elimnated the return parameters. I'm hoping
> these two approaches help.
> Help is greatly appreciated.
> lq

I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon|||Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:

*** START SP CODE:

Alter Procedure "mySPName"
@.par1 nvarchar(15) = null,
@.par2 int = null,
@.par3 nvarchar(50) = null,
@.par4 nvarchar(50) = null,
@.par5 nvarchar(125) = null,
@.par6 smallint = null,
@.par7 ntext = null,
@.par8 smalldatetime = null,
@.par9 smalldatetime = null,
@.par10 smalldatetime = null,
@.par11 smalldatetime = null,
@.par12 datetime = null,
@.par13 datetime = null,
@.par14 smallint = null,
@.par15 smalldatetime = null,
@.par16 tinyint = null,
@.par17 tinyint = null,
@.par18 tinyint = null,
@.par19 nvarchar(100) = null,
@.par20 int = null,
@.par21 int = null,
@.par22 int = null,
@.PKID int OUTPUT

AS
set nocount on
set xact_abort on

/* Insert new event into tblAppointments */

INSERT INTO
tblAppointments (fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8,
fld9, fld10, fld11, fld12, fld13, fld14, fld15, fld16, fld17, fld18,
fld19, fld20, fld21,fld22,fld23, fld24)
VALUES
(@.par1, @.par2, @.par3, @.par4, @.par5, @.par6, @.par7,
@.par8, @.par9, @.par10, @.par12,@.par13, @.par14, @.par15, @.par16, @.par17,
@.par18,
@.par19, @.par20, @.par21,@.par22, @.par22,@.par18, @.par18)

/* Note: Some columns above receive data from the same parameters */

SELECT @.PKID = @.@.IDENTITY

/* Create an entry in tblAppointmentHistory */

Insert Into
tblAppointmentsHistory (fldA, fldB, fldC, fldD, fldE)
Values
(@.PKID, @.par2, @.par3,'New Entry','Appt Type = ' + @.par4 + ' ;
AppTitle= ' + @.par5 + ' ; AppDescription= ' + isnull(Cast(@.par7 AS
nvarchar(4000)),'<none>') + ' ; Location = ' + @.par19 + ' ; Start
Date = ' + CAST(@.par12 AS nvarchar(25)) + ' ; End Date = ' +
IsNull(CAST(@.par13 AS nvarchar(25)),'<none'))

***END SP CODE

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<407af642$1_2@.news.bluewin.ch>...
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> > Is it possible the existence of a return parameter on an SP running
> > insert statements would cause blocking to happen (any removing it
> > might help eliminate it?)
> > The SPs in question have three INSERT INTO statements using an
> > @.@.IDENTITY to help populate the second and third tables based on the
> > PK of the newly inserted record in the first INSERT INTO Statement.
> > I have recently stared to receive timeout errors in a multi-user
> > enviroment and wonder if that's part of the cause. In this
> > environment, users are requerying a table that is also being written
> > to. The writes are infrequent but the reads of that table are very
> > frequent.
> > On the read side I have added Select from TableX WITH (READPAST)...
> > On the write side I have elimnated the return parameters. I'm hoping
> > these two approaches help.
> > Help is greatly appreciated.
> > lq
> I'm not entirely sure I follow your description - stored procedures can have
> return values, and output parameters, but not "return parameters" so I'm not
> clear as to which one you mean here.
> As a general comment, if your three INSERTs are inside a transaction, and
> that transaction runs for too long, or requires a lot of locks, then it's
> possible that the readers will be affected. But without some more
> information, such as the piece of code containing the INSERTs, and some
> background information about the tables, it's hard to say.
> By the way, READPAST is potentially dangerous, because it means the client
> will not see data which it should normally see, so the results returned may
> be incomplete. Locking hints are not often required, so you should try other
> solutions first.
> Simon|||Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:

*** START SP CODE:

Alter Procedure "mySPName"
@.par1 nvarchar(15) = null,
@.par2 int = null,
@.par3 nvarchar(50) = null,
@.par4 nvarchar(50) = null,
@.par5 nvarchar(125) = null,
@.par6 smallint = null,
@.par7 ntext = null,
@.par8 smalldatetime = null,
@.par9 smalldatetime = null,
@.par10 smalldatetime = null,
@.par11 smalldatetime = null,
@.par12 datetime = null,
@.par13 datetime = null,
@.par14 smallint = null,
@.par15 smalldatetime = null,
@.par16 tinyint = null,
@.par17 tinyint = null,
@.par18 tinyint = null,
@.par19 nvarchar(100) = null,
@.par20 int = null,
@.par21 int = null,
@.par22 int = null,
@.PKID int OUTPUT

AS
set nocount on
set xact_abort on

/* Insert new event into tblAppointments */

INSERT INTO
tblAppointments (fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8,
fld9, fld10, fld11, fld12, fld13, fld14, fld15, fld16, fld17, fld18,
fld19, fld20, fld21,fld22,fld23, fld24)
VALUES
(@.par1, @.par2, @.par3, @.par4, @.par5, @.par6, @.par7,
@.par8, @.par9, @.par10, @.par12,@.par13, @.par14, @.par15, @.par16, @.par17,
@.par18,
@.par19, @.par20, @.par21,@.par22, @.par22,@.par18, @.par18)

/* Note: Some columns above receive data from the same parameters */

SELECT @.PKID = @.@.IDENTITY

/* Create an entry in tblAppointmentHistory */

Insert Into
tblAppointmentsHistory (fldA, fldB, fldC, fldD, fldE)
Values
(@.PKID, @.par2, @.par3,'New Entry','Appt Type = ' + @.par4 + ' ;
AppTitle= ' + @.par5 + ' ; AppDescription= ' + isnull(Cast(@.par7 AS
nvarchar(4000)),'<none>') + ' ; Location = ' + @.par19 + ' ; Start
Date = ' + CAST(@.par12 AS nvarchar(25)) + ' ; End Date = ' +
IsNull(CAST(@.par13 AS nvarchar(25)),'<none'))

***END SP CODE

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<407af642$1_2@.news.bluewin.ch>...
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> > Is it possible the existence of a return parameter on an SP running
> > insert statements would cause blocking to happen (any removing it
> > might help eliminate it?)
> > The SPs in question have three INSERT INTO statements using an
> > @.@.IDENTITY to help populate the second and third tables based on the
> > PK of the newly inserted record in the first INSERT INTO Statement.
> > I have recently stared to receive timeout errors in a multi-user
> > enviroment and wonder if that's part of the cause. In this
> > environment, users are requerying a table that is also being written
> > to. The writes are infrequent but the reads of that table are very
> > frequent.
> > On the read side I have added Select from TableX WITH (READPAST)...
> > On the write side I have elimnated the return parameters. I'm hoping
> > these two approaches help.
> > Help is greatly appreciated.
> > lq
> I'm not entirely sure I follow your description - stored procedures can have
> return values, and output parameters, but not "return parameters" so I'm not
> clear as to which one you mean here.
> As a general comment, if your three INSERTs are inside a transaction, and
> that transaction runs for too long, or requires a lot of locks, then it's
> possible that the readers will be affected. But without some more
> information, such as the piece of code containing the INSERTs, and some
> background information about the tables, it's hard to say.
> By the way, READPAST is potentially dangerous, because it means the client
> will not see data which it should normally see, so the results returned may
> be incomplete. Locking hints are not often required, so you should try other
> solutions first.
> Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404121731.52fe5214@.posting.google.c om...
> Simon,
> Thank you for your response. I meant to write that in VBA I have
> appended an Output Parameters to the prm collection.
> Here is the entire SP:

<snip
At first glance, there doesn't seem to be anything obviously incorrect with
your procedure, assuming that you haven't removed anything significant from
it. Small issues are that you should probably use SCOPE_IDENTITY() instead
of @.@.IDENTITY (see Books Online), and you may want to review your use of SET
XACT_ABORT:

http://www.sommarskog.se/error-hand...html#XACT_ABORT

To go back to your original timeout issue, why do you think the timeouts are
connected to this specific procedure? Have you monitored what's happening on
the server when timeouts occur, especially locking? You can use Profiler to
capture what's happening on the server, and this KB article may be useful:

http://support.microsoft.com/defaul...9&Product=sql2k

Erland also has a tool for examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404121731.52fe5214@.posting.google.c om...
> Simon,
> Thank you for your response. I meant to write that in VBA I have
> appended an Output Parameters to the prm collection.
> Here is the entire SP:

<snip
At first glance, there doesn't seem to be anything obviously incorrect with
your procedure, assuming that you haven't removed anything significant from
it. Small issues are that you should probably use SCOPE_IDENTITY() instead
of @.@.IDENTITY (see Books Online), and you may want to review your use of SET
XACT_ABORT:

http://www.sommarskog.se/error-hand...html#XACT_ABORT

To go back to your original timeout issue, why do you think the timeouts are
connected to this specific procedure? Have you monitored what's happening on
the server when timeouts occur, especially locking? You can use Profiler to
capture what's happening on the server, and this KB article may be useful:

http://support.microsoft.com/defaul...9&Product=sql2k

Erland also has a tool for examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon

Blanks in columns

Quick Question I hope.

Does having a lot of blanks in a column cause errors ?

In 2 or 3 packages where I get 95% of the data in the Error Colomn I can find nothing wrong with the data at all ? it all either seems perfect or there is no data in the column in question usually I would have maybe 800 rows where data would have been inserted but in the other 40, 000 rows the column is blank

Using the "Retain null values from the source as null values in the destination" doesnt seem to make a differance.

This is the error description

The data value cannot be converted for reasons other than sign mismatch or data overflow.

Anyone know of a solution / reason why this keeps happening

Thanks

Without seeing the package, no. It would apear that there is an implicit conversion going on somewhere which is failing. You have the error files so it shouldn't be difficult to work out what is happening. Do you know which column is failing?

-Jamie

|||It would also help for us to know what type of source you are using. I would guess that your using a Flat File Source.

Please give us a little more info about the structure of the package and we'll be in a better position to offer suggestions.

Larry Pope|||

Will do, Thanks for the help

Package Details are as follows

Import data from a flat file source in csv format with semicolons as the delimiters

Change the date's using a derived column with an expression like
(DT_DBTIMESTAMP)[Column 21]

Then to a SQL Server Destination where that column wher e the table for that column would read [Column 2] DATETIME

I have also thrown in a ErrorDescription Package (Which is great Thanks Jamie :D) and also a package that tells me which column is failing (Which I got from Simon)

From these I can work out which column seems to be failing although when I switch that to Ignore errors another date column usually fails.

Is it REALLY bad practice to have all columns on "Ignore errors" ....when the data seems to be fine when I look over it after in the table I cant find anything wrong with it.

If there was an charactor such as '# 'or a random / ,\ surely that would just fail one row and not all the rows

|||Are you trying to cast nulls to a DT_DBTIMESTAMP. I think there

may be problems with this. Jamie might be able to confirm this.

You might want to change your derived column to

ISNULL([Column 21] ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)([Column

21]). And be aware that trying to cast invalid dates could also

raise errors (example '0000-00-00').

Have you put a data viewer between the source and the derived column to see how the Flat File Source is interpreting file?

Larry Pope|||

I've definately seen cases where an attempt to put a NULL into a DT_DBTIMESTAMP column resulted in a value of "0000-00-00 00:00:00" - which is just useless. This happened from inside a script task when setting the column to 'Nothing' but I guess the same symptoms could occur elsewhere as well!!

-Jamie

|||

Thanks guys ill try that,

Just realised aswell that 1 column is in the format MM/DD/YYYY while the other is DD/MM/YYYY

Should be fun trying to sort them out

|||

Can someone please elaborate on this occurrence. I believe we are experiencing a flavor of this.

We have a package that checks an amount field for values greater than 0.

When the value is greater than 0 then we populate with the processing date, else we place a Null Timestamp value.

We are having failed inserts because our target column is SmallDateTime and somehow the time value of "0000-00-00 00:00:00" is being created.

According to the logic, there should only be a valid date or a null date....no zeros. Does anyone have any information on this issue?

ChargeOffAmount > 0 ? (DT_DBTIMESTAMP)ReportingDate : NULL(DT_DBTIMESTAMP)

Thanks in advance...

Jamie Thomson wrote:

I've definately seen cases where an attempt to put a NULL into a DT_DBTIMESTAMP column resulted in a value of "0000-00-00 00:00:00" - which is just useless. This happened from inside a script task when setting the column to 'Nothing' but I guess the same symptoms could occur elsewhere as well!!

-Jamie

|||

Romeo,

Is there a default value specified in the column definition in the database?

|||

Phil, no, there is no default value specified for this column.

I also failed to mention that these values are not always consistently produced...meaning that we can try to recreate the occurrence and then the date will be generated correctly and load. Makes debugging not so easy and causes me to doubt my sanity.....well, that's never been in doubt, I get opinions on both sides....

blank cells cause trouble on join

I have a few tables that I am joining together and one of the joins is on a
field that almost always has a code in it. Sometimes it is a blank (not a
null) if the clerk didn't type anything into it. The table that I am joining
to this one, has many cells in the join field thhat are blank and those rows
are used for commenting the other records in the table (I know it is stupid
but I didn't design it).
If I do a regular join whenever it comes upon one of these blanks it joins
to all of the comment records in the 2nd table.
What would be the easiest way to get around this problem?
Below is the statement as far as I got befor the last join messed me up.
cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
blank in all rows where they are being used as comments.
SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
codentry
from osmccsdb.cdocket
left outer join osmccsdb.cparty
on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccharge
on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccodes
on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
order by osmccsdb.cdocket.cdevtdt descLinda,
A LEFT JOIN will return all of the records from the table on left side of
the JOIN keyword and any matching records on the right. I don't have any
sample data to work with but you might look at the NULLIF function and see
if it might help you in your situation.
HTH
Jerry
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I see this with some tools and it is kind of a pain. First thing I would
note is that it is usually a bad idea to have user inputted values being
what you are joining on (but I know you said you didn't design it)
I would probaby just add a condition to your join that says something like:
AND ccodes.codevent <> '' AND the other column too
This will eliminate them from the join. Or if you want the '' rows from one
side, but not the other, then change the '' in the join:
case when ccodes.codevent = '' then 'NOT POSSIBLE' else ccodes.codevent end
Or you could use NULL instead of 'NOT POSSIBLE'
It might not be great for performance, so you might have to do some
trickiness if you have scads of data, but that is just the price you pay :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I would prefer updating those columns with some value,say string and
eliminate that in the where clause. and make it default so that problem wil
l
not come again
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||You are a freaking genius!!! I am not sure that I will be allowed to do tha
t
but if I can what a relief! These are court records and I am not sure how
weird they will be about me changing data.
"R.D" wrote:
> I would prefer updating those columns with some value,say string and
> eliminate that in the where clause. and make it default so that problem w
ill
> not come again
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Linda Ibarra" wrote:
>|||It was also pointed out to me that if I just created a view of this data
where the records with blank fields were not included and used that for the
join, that that would also eliminate this problem.
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||yup, you can always have a view. yet what if you want to use data in another
sproc or function or in another view. To solve this permanently have
amechanism where either you dont allow such values(?) or such blanks are
automatically replace with something like 'No Data'
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:
> It was also pointed out to me that if I just created a view of this data
> where the records with blank fields were not included and used that for th
e
> join, that that would also eliminate this problem.
> "Linda Ibarra" wrote:
>