Thanks for the info. That indeed clarified the problem.
My code now works as intended. :-)
Ryan Williams
[EMAIL PROTECTED]
----- Original Message -----
From: "Andy Ewings" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 17, 2000 12:59 PM
Subject: RE: SQL query problem...
> If you are using SQL 6.5 then I think I know what's causing this.........
>
> This part of your code: (len(wire_id)-2) could well be producing a
negative
> result and hence when you wrap the right function around it you will get a
> 536 error....for more info have a look at what Microsoft say about update
> statements using the substring function (same thing will happen with the
> right function)
>
>
> BUG: Update Query Causes an Access Violation if a SUBSTRING Encounters
Error
> 536
> --------------------------------------------------------------------------
--
> ----
> BUG #: 17963 (SQLBUG_65)
>
> SYMPTOMS
> An Update query may cause an Access Violation (AV) if a SUBSTRING function
> generates this error:
>
> Msg 536, Level 16, State 1
> Invalid length parameter passed to the substring function.
>
> WORKAROUND
> Make sure that you pass a valid length to the SUBSTRING function. When you
> pass a valid length to the SUBSTRING function, the Access Violation no
> longer occurs.
>
> STATUS
> Microsoft has confirmed this to be a problem in SQL Server version 6.5.
>
> MORE INFORMATION
> This problem is further illustrated by this example:
>
> create table tbl_1
> (col1 varchar(16) NULL,
> col2 varchar(16) NULL
> )
> go
> insert into tbl_1 values('123:456:789', null)
> insert into tbl_1 values('123', null)
> go
>
> UPDATE tbl_1
> SET col2 = substring(col1,1,charindex(":",col1)-1)
> If you look at the details of the preceding UPDATE statement, you find
that
> the "charindex(":",col1)" returns a value of 0 for one of the rows. You
then
> proceed to subtract 1 from this value, which results in a value of -1.
This
> is the value that you end up passing to the SUBSTRING function, which then
> creates the 536 error.
>
>
>
>
>
> -----Original Message-----
> From: Ryan Williams [mailto:[EMAIL PROTECTED]]
> Sent: 17 August 2000 17:45
> To: [EMAIL PROTECTED]
> Subject: SQL query problem...
>
>
> I have a SQL problem. I know what information I want to retrieve from
> the DB,
> however, I do not know what precisely is the proper SQL syntax/function
is
> in order to
> achieve it.
>
> The query as it is currently written in my CF template is:
> select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.*
> from wire_list wl
> left join P_officers p on wl.reg_by = p.off_id
> left join wire_status ws on wl.status_code = ws.status_code
> left join wire_log wlg on wl.wire_id = wlg.wire_id
> left join wire_senders wsnd on wl.sender_id = wsnd.sender_id
> left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode
> left join wire_banks wb on wl.bank_id = wb.bank_id
> where wl.wire_id = (
> select max(right(wire_id,(len(wire_id)-2)))
> from wire_list)
>
> The error this code generates when I attempt to run it in SQL query
> analyzer:
> Server: Msg 536, Level 16, State 1, Line 1
> Invalid length parameter passed to the substring function.
>
> The content of the wire_id column looks like this format:
> ab999 and is of varchar datatype.
>
> I did not code these tables and I am in the middle of re-coding the
> entire site to be FuseBox compliant. I am trying to select the top (max)
> wire_id from the wire_list table, and display all records related
> to the returned value of the max function, but the result that
areretrieved
> to date,
> with the code "Select max(wire_id)" and no WHERE clause,
> is ab99 when I know there are wire_id's in the table that have values
> of ab999, etc. It seems that SQL does not recognize that ab999 is the
> larger value when compared to ab99.
>
> The solution I am trying to apply with the code at the top of this email
is
> to "get"
> the "number" part of the wire_id column, minus the two letters, and then
use
> the max function on
> the remainder to get the ab999 wire_id i know is in the table. I looked
> through the
> SQL books online and it seems that SQL Sever does not have the ability to
> convert
> the varchar datatype (of the wire_id) column to the int data type that
> the max function seems to need in order to work properly.
>
> Does anyone know of a work around for this problem? Or am I going totally
> in the wrong
> direction with this logic?
>
> Any help/ideas/comments would be greatly appreciated. Thanks.
>
> Ryan Williams
> [EMAIL PROTECTED]
>
> -------------------------------------------------------------------------
--
> ---
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
>
> --------------------------------------------------------------------------
--
> --
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
> --------------------------------------------------------------------------
----
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
