Function LEFT invoked with wrong number or type of argument
Emil
whats wrong in this command?
update TABLE set FIELD1 =cast (left(255,FIELD2) as varchar)
I get:
Function LEFT invoked with wrong number or type of
argument(s). FIELD1 is of type text.
thx
Mark A. Parsons
arguments to the left() function switched. The expression/string is first, with the numeric value coming second.
Carl Kayser
"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:499ef824$1@forums-1-dub...
I really love the COBOL, er, ANSI syntax of cast (). Is "varchar" valid?
Doesn't it have to be "varchar (NNN)"?
Rob V
You can indeed use varchar without a length, also as "convert(varchar,
...)". The advantage of this is that the resulting string will only be as
long as needed to contain the resulting value, i.e. when converting an int
to a string you don't have to strip off spaces etc. handy when formatting
data.
I'm not sure this is documented, but it's been working like this for as long
as I can remember...
HTH,
Rob V.
-----------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------
"Carl Kayser" <kays...@bls.gov> wrote in message
news:499f053c$1@forums-1-dub...
Emil
I got the same error:
1> update TABLE1 set new_F=cast (left(F,1) as varchar)
2> go
Function LEFT invoked with wrong number or type of
argument(s).
1> update TABLE1 set new_F=cast (left(1,F) as varchar)
2> go
Function LEFT invoked with wrong number or type of
argument(s).
1>
any idea?
thank you,
Mark A. Parsons
The following works for me:
========================
create table TABLE1 (F varchar(10), new_F varchar(10))
go
insert TABLE1 values ('123456','abcdef')
go
select * from TABLE1
go
F new_F
---------- ----------
123456 abcdef
1> update TABLE1 set new_F = cast(left(F,1) as varchar)
2> go
1> select * from TABLE1
2> go
F new_F
---------- ----------
123456 1
========================
I ran this example with the following version of Sybase ASE:
Adaptive Server Enterprise/15.0.2/EBF 15963 ESD#6/P/NT (IX86)/Windows 2000/ase1502/2537/32-bit/OPT/Wed Oct 01 21:41:43 2008
So, what version of Sybase ASE are you running? Please post the following to the newsgroup:
========================
select @@version
go
========================
Derek Asirvadem
>
> You can indeed use varchar without a length, also as "convert(varchar,
> ...)". The advantage of this is that the resulting string will only be as
> long as needed to contain the resulting value, i.e. when converting an int
> to a string you don't have to strip off spaces etc. handy when formatting
> data.
That may be true. But it is hideously bad coding practice: after said
conversion the calling routine has to PUT the data somewhere, if you
use this technique, you will get code that "works" in development and
testing, and which is guaranteed to blow one day in the future when the
string length exceeds the container.
Always use explicit lengths and explicit conversions.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
--
"Patient, normalise thyself"
Derek Asirvadem
You're most probably on 12.5. The 15.0 Parser is brilliant, much more
aware of context, the error msg is precise. 12.5 isn't that smart;
there is no "cast" function, it is blowing up on that, but reporting
something else, we are supposed to look "near" the reported error for
anything else untoward.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright Š 2008 Software Gems Pty Ltd
--
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability
Emil
yes I am using a different version: 12.5.4 esd 3
what is the correct syntax in this version?
thanks.
Mark A. Parsons
versions:
ASE 12.5.3/ESD #6
ASE 12.5.4/ESD #4
ASE 15.0.2/ESD #6
Since ASE 12.5.4/ESD #3 falls between the versions I've mentioned, then this would sound like a bug in your version of
ASE. In this case I'd suggest either a) open a case with Sybase TechSupport, b) upgrade to an ASE version that doesn't
have this issue (eg, ASE 12.5.4/ESD #4) or c) rewrite your code to eliminate the problem.
Regarding option c, you could replace:
new_F = cast(lef(F,1) as varchar)
with one of the following:
new_F = convert(varchar,left(F,1))
new_F = left(F,1)
If these replacements still cause errors then please post the following back here to the newsgroup:
1 - the complete output from running 'select @@version'
2 - a complete example to reproduce the problem (ie, create table, insert, update, etc)
ThanksButNo
> > On 2009-02-21 07:21:22 +1100, "Rob V" said:
>
> > You can indeed use varchar without a length, also as "convert(varchar,
> > ...)". The advantage of this is that the resulting string will
> > only be as long as needed to contain the resulting value,
I may be missing something here, but it has always been my
understanding that the database only stores whatever actual
data is in the column, up to the defined limit. E.g., a column
defined as "varchar(255)" that contains the data "Ciao, baby"
will use up considerably less than 255 bytes on disk.
Thus, the string is *always* only as long as needed to contain
the value.
On Feb 21, 5:44 pm, Derek Asirvadem <derek.asirva...@gmail.com> wrote:
> That may be true. But it is hideously bad coding practice: after said
> conversion the calling routine has to PUT the data somewhere, if you
> use this technique, you will get code that "works" in development and
> testing, and which is guaranteed to blow one day in the future when the
> string length exceeds the container.
>
> Always use explicit lengths and explicit conversions.
Forgive my ignorance, but I would have thought that leaving
the max-length off would mean that the database will adjust
the size of the column to *whatever* data is placed in there,
up to the absolute maximum supported.
/:-/
Derek Asirvadem
>> That may be true. But it is hideously bad coding practice: after said
>> conversion the calling routine has to PUT the data somewhere, if you
>> use this technique, you will get code that "works" in development and
>> testing, and which is guaranteed to blow one day in the future when the
>> string length exceeds the container.
>>
>> Always use explicit lengths and explicit conversions.
>
> Forgive my ignorance, but I would have thought that leaving
> the max-length off would mean that the database will adjust
> the size of the column to *whatever* data is placed in there,
> up to the absolute maximum supported.
Yes. That is the point. The effect has to be understood. If you put a
270-char string in a 255-char column, it will be truncated without
error msgs. In the client program, if you use varchar, or char with no
specific len, then during development and testing it may SEEM to work
(less than 255 chars loaded), but the day someone loads more than 255
chars into it, it bombs (data is lost). If it were table-to-table and
there were no display, this can be difficult to find. If you use fixed
length, or specify lengths [as per the db definition] on varchar
columns, which is a good coding standard to implement, this will not
occur, you will not have to "find" the code that caused the intended
data to be truncated and fix it (by implementing some form of the
standard).
The principle that needs to be understood is, SQL is considered a
loosely-cast [datatypes] language. That is only true if the language
is considered in isolation, and compared with other languages. In
fact, SQL is a query language for databases; databases are
tightly-cast; SQL therefore has many datatype casting issues; the most
common error in SQL coding IS datatype mismatches, and it affects every
area. These issues can be easily and entirely avoided by good type
casting in SQL. Therefore, a good shop standard to have (to avoid all
such avoidable issues), is to demand SQL coders to cast columns and
variable explicitly, and to convert explicitly.
The len or perceived len or input len or max len issue is within this.
Code that is clearly aware of the datatype and len and does explicit
conversions, simply does not have the problems that unaware code has.
Particularly "it has been working for a while, how come it is now
broken" when the 20-char len is exceeded. In my book, such code was
always sub-standard, it just did not get noticed until now. You can
fix it piece-meal, as each bug shows up; or you can eliminate the
possibility with a shop standard.
>> You can indeed use varchar without a length, also as "convert(varchar,
>> ...)". The advantage of this is that the resulting string will
>> only be as long as needed to contain the resulting value,
>
> I may be missing something here, but it has always been my
> understanding that the database only stores whatever actual
> data is in the column, up to the defined limit. E.g., a column
> defined as "varchar(255)" that contains the data "Ciao, baby"
> will use up considerably less than 255 bytes on disk.
And if it is loaded with more than 255 chars, it will get truncated
(without error)
> Thus, the string is *always* only as long as needed to contain
> the value.
Correct. You are talking about the column on disk; Rob is talking
about the string produced by the function, in memory (which may or may
not end up in a column on disk). Yes, I agree, in the OP, it is an
update statement, but Rob may have been making a generic statement.
But that is a reasonably good example of what I have discussed above;
why I posted the caution. The function as Rob presented could produce
a 270-char string, without error ... which may or may not be painted on
the screen ... which is truncated when saved to disk. If the coder is
aware of such issues [the 255 is relevant to the target not the
source], and it is not allowed by standard, that can be eliminated.
This is not allowed due to the possible truncation/lost data:
UPDATE table SET column_1 = CONVERT(VARCHAR, column_2)
But if I understand where you are going (good example for OP; not the
perfect example for what you are discussing), the following is enough:
UPDATE table SET column_1 = CONVERT(VARCHAR(255), column_2)
UPDATE table SET column_1 = LEFT(column_2, 255)
UPDATE table SET column_1 = column_2 -- truncated to 255
The standard would dictate that the coder checks before loading:
IF DATALENGTH(column_2) > 255
____BEGIN
____SET @err = 20123 -- datatype or datalength error
____GOTO EXIT_ERR -- which does a RAISERROR
____END
UPDATE table SET column_1 = column_2 -- no truncation/lost data
The difference is the enforced awareness in the coder, of
datatypes/lengths and conversion.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
--