Oops. I just stumbled onto the answer by accident. I just removed the
"-2"
and
associated parentheses from the sub-query and I got my results. My
sub-query
now looks like:
select max(right(wire_id,len(wire_id))) from wire_list)
and it works too !! :-)
My sincerest apologies to everyone in the list.
Ryan Williams
[EMAIL PROTECTED]
> ----- Original Message -----
> From: "Ryan Williams" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, August 17, 2000 12:45 PM
> 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.
