Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Sybase IQ 15.4 : Problem using UDF in update statement (Urgent)

7 posts in General Discussion Last posting was on 2012-10-26 10:31:02.0Z
Vicky Chan Posted on 2012-08-20 04:14:57.0Z
Sender: 561a.5031b6b4.1804289383@sybase.com
From: Vicky Chan
Newsgroups: sybase.public.iq
Subject: Sybase IQ 15.4 : Problem using UDF in update statement (Urgent)
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5031b9c1.56ed.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 19 Aug 2012 21:14:57 -0700
X-Trace: forums-1-dub 1345436097 172.20.134.41 (19 Aug 2012 21:14:57 -0700)
X-Original-Trace: 19 Aug 2012 21:14:57 -0700, 172.20.134.41
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4534
Article PK: 246674

I created a UDF (in SQL) in my database and would like to
update a table according to the return value of the UDF. The
UDF returns a varchar(50) string.

This is my update statement:
update TableA
set TableA.col_01 = my_UDF(TableA.col_01, TableB.col_02)
from TableB
where TableA.sec_type = TableB.sec_type and TableA.year =
2008

However, the following SQL error returned:
Could not execute statement.
Update operation attempted on non-updatable remote query
SQLCODE=-728, ODBC 3 State="42000"

Please Help!


Manish Negandhi [TeamSybase] Posted on 2012-08-20 18:27:45.0Z
From: "Manish Negandhi [TeamSybase]" <negandhi.manish_nosmap@gmail.com>
Newsgroups: sybase.public.iq
References: <5031b9c1.56ed.1681692777@sybase.com>
Subject: Re: Sybase IQ 15.4 : Problem using UDF in update statement (Urgent)
Lines: 29
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <503281a1$1@forums-1-dub>
Date: 20 Aug 2012 11:27:45 -0700
X-Trace: forums-1-dub 1345487265 172.20.134.152 (20 Aug 2012 11:27:45 -0700)
X-Original-Trace: 20 Aug 2012 11:27:45 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4535
Article PK: 246675


<Vicky Chan> wrote in message news:5031b9c1.56ed.1681692777@sybase.com...
>I created a UDF (in SQL) in my database and would like to
> update a table according to the return value of the UDF. The
> UDF returns a varchar(50) string.
>
> This is my update statement:
> update TableA
> set TableA.col_01 = my_UDF(TableA.col_01, TableB.col_02)
> from TableB
> where TableA.sec_type = TableB.sec_type and TableA.year =
> 2008
>
> However, the following SQL error returned:
> Could not execute statement.
> Update operation attempted on non-updatable remote query
> SQLCODE=-728, ODBC 3 State="42000"
>
> Please Help!

Can you try changing my_UDF to a a literal value just to isolate UDF
specific problem ? for example..
what happens if you use something like .... set TableA.col_01 = dummy_value
?

Manish Negandhi
[TeamSybase]


Manish Negandhi [TeamSybase] Posted on 2012-08-20 18:30:08.0Z
From: "Manish Negandhi [TeamSybase]" <negandhi.manish_nosmap@gmail.com>
Newsgroups: sybase.public.iq
References: <5031b9c1.56ed.1681692777@sybase.com> <503281a1$1@forums-1-dub>
Subject: Re: Sybase IQ 15.4 : Problem using UDF in update statement (Urgent)
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50328230@forums-1-dub>
Date: 20 Aug 2012 11:30:08 -0700
X-Trace: forums-1-dub 1345487408 172.20.134.152 (20 Aug 2012 11:30:08 -0700)
X-Original-Trace: 20 Aug 2012 11:30:08 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4536
Article PK: 246676


"Manish Negandhi [TeamSybase]" <negandhi.manish_nosmap@gmail.com> wrote in
message news:503281a1$1@forums-1-dub...
>
> <Vicky Chan> wrote in message news:5031b9c1.56ed.1681692777@sybase.com...
>>I created a UDF (in SQL) in my database and would like to
>> update a table according to the return value of the UDF. The
>> UDF returns a varchar(50) string.
>>
>> This is my update statement:
>> update TableA
>> set TableA.col_01 = my_UDF(TableA.col_01, TableB.col_02)
>> from TableB
>> where TableA.sec_type = TableB.sec_type and TableA.year =
>> 2008
>>
>> However, the following SQL error returned:
>> Could not execute statement.
>> Update operation attempted on non-updatable remote query
>> SQLCODE=-728, ODBC 3 State="42000"
>>
>> Please Help!
>
> Can you try changing my_UDF to a a literal value just to isolate UDF
> specific problem ? for example..
> what happens if you use something like .... set TableA.col_01 =
> dummy_value ?
>
> Manish Negandhi
> [TeamSybase]

can you also post UDF code ?

Manish Negandhi
[TeamSybase]




>
>


mcchan Posted on 2012-08-24 09:13:30.0Z
Sender: 2b8c.503742d3.1804289383@sybase.com
From: mcchan
Newsgroups: sybase.public.iq
Subject: Re: Sybase IQ 15.4 : Problem using UDF in update statement (Urgent)
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <503745ba.2c06.1681692777@sybase.com>
References: <50328230@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 24 Aug 2012 02:13:30 -0700
X-Trace: forums-1-dub 1345799610 172.20.134.41 (24 Aug 2012 02:13:30 -0700)
X-Original-Trace: 24 Aug 2012 02:13:30 -0700, 172.20.134.41
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4538
Article PK: 246678

Hi Manish,

My UDF code:

ALTER FUNCTION "PIPITS"."my_UDF"(
IN as_perc_sign varchar(1),
IN as_item numeric)
RETURNS varchar(20)
BEGIN
return as_perc_sign|| if as_item is null then 'NULL' else
convert(varchar(10), as_item) end if;
END

> "Manish Negandhi [TeamSybase]"
> <negandhi.manish_nosmap@gmail.com> wrote in message
> news:503281a1$1@forums-1-dub... >
> > <Vicky Chan> wrote in message
> news:5031b9c1.56ed.1681692777@sybase.com... >>I created a
> UDF (in SQL) in my database and would like to >> update a
> table according to the return value of the UDF. The >> UDF
> returns a varchar(50) string. >>
> >> This is my update statement:
> >> update TableA
> >> set TableA.col_01 = my_UDF(TableA.col_01,
> TableB.col_02) >> from TableB
> >> where TableA.sec_type = TableB.sec_type and TableA.year
> = >> 2008
> >>
> >> However, the following SQL error returned:
> >> Could not execute statement.
> >> Update operation attempted on non-updatable remote
> query >> SQLCODE=-728, ODBC 3 State="42000"
> >>
> >> Please Help!
> >
> > Can you try changing my_UDF to a a literal value just to
> > isolate UDF specific problem ? for example..
> > what happens if you use something like .... set
> > TableA.col_01 = dummy_value ?
> >
> > Manish Negandhi
> > [TeamSybase]
>
> can you also post UDF code ?
>
> Manish Negandhi
> [TeamSybase]
>
>
>
>
> >
> >
>
>


Vicky Chan Posted on 2012-08-24 09:22:56.0Z
Sender: 2b8c.503742d3.1804289383@sybase.com
From: Vicky Chan
Newsgroups: sybase.public.iq
Subject: Re: Sybase IQ 15.4 : Problem using UDF in update statement (Urgent)
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <503747f0.2c6a.1681692777@sybase.com>
References: <503745ba.2c06.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 24 Aug 2012 02:22:56 -0700
X-Trace: forums-1-dub 1345800176 172.20.134.41 (24 Aug 2012 02:22:56 -0700)
X-Original-Trace: 24 Aug 2012 02:22:56 -0700, 172.20.134.41
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4539
Article PK: 246679

Hi Manish,

I have tried replacing "my_UDF" to a literal value and
records can be updated.
My UDF code as follows:
ALTER FUNCTION "PIPITS"."my_UDF"(
IN as_perc_sign varchar(1),
IN as_item numeric)
RETURNS varchar(20)
BEGIN
return as_perc_sign|| if as_item is null then 'NULL' else
convert(varchar(10), as_item) end if;
END

And for your information, when i replace "my_UDF" with the
code inside the function the update also works. That is:
update TableA
set TableA.col_01 = TableA.col_01||if TableB.col_02 is null
then 'NULL' else convert(varchar(10), TableB.col_02) end if
from TableB
where TableA.sec_type = TableB.sec_type and TableA.year =
2008

Thanks!
Vicky

> > "Manish Negandhi [TeamSybase]"
> > <negandhi.manish_nosmap@gmail.com> wrote in message
> > news:503281a1$1@forums-1-dub... >
> > > <Vicky Chan> wrote in message
> > news:5031b9c1.56ed.1681692777@sybase.com... >>I created
> > a UDF (in SQL) in my database and would like to >>
> > update a table according to the return value of the UDF.
> > The >> UDF returns a varchar(50) string. >>
> > >> This is my update statement:
> > >> update TableA
> > >> set TableA.col_01 = my_UDF(TableA.col_01,
> > TableB.col_02) >> from TableB
> > >> where TableA.sec_type = TableB.sec_type and
> > TableA.year = >> 2008
> > >>
> > >> However, the following SQL error returned:
> > >> Could not execute statement.
> > >> Update operation attempted on non-updatable remote
> > query >> SQLCODE=-728, ODBC 3 State="42000"
> > >>
> > >> Please Help!
> > >
> > > Can you try changing my_UDF to a a literal value just
> > > to isolate UDF specific problem ? for example..
> > > what happens if you use something like .... set
> > > TableA.col_01 = dummy_value ?
> > >
> > > Manish Negandhi
> > > [TeamSybase]
> >
> > can you also post UDF code ?
> >
> > Manish Negandhi
> > [TeamSybase]
> >
> >
> >
> >
> > >
> > >
> >
> >


Manish Negandhi [TeamSybase] Posted on 2012-08-29 04:34:25.0Z
From: "Manish Negandhi [TeamSybase]" <negandhi.manish_nosmap@gmail.com>
Newsgroups: sybase.public.iq
References: <503745ba.2c06.1681692777@sybase.com> <503747f0.2c6a.1681692777@sybase.com>
Subject: Re: Sybase IQ 15.4 : Problem using UDF in update statement (Urgent)
Lines: 80
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <503d9bd1@forums-1-dub>
Date: 28 Aug 2012 21:34:25 -0700
X-Trace: forums-1-dub 1346214865 172.20.134.152 (28 Aug 2012 21:34:25 -0700)
X-Original-Trace: 28 Aug 2012 21:34:25 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4542
Article PK: 246682

Can you try adding from iq_dummy within the function ?

as_perc_sign|| if as_item is null then 'NULL' else
convert(varchar(10), as_item) end if; from iq_dummy

-HTH
Manish Negandhi
[TeamSybase]

<Vicky Chan> wrote in message news:503747f0.2c6a.1681692777@sybase.com...
> Hi Manish,
>
> I have tried replacing "my_UDF" to a literal value and
> records can be updated.
> My UDF code as follows:
> ALTER FUNCTION "PIPITS"."my_UDF"(
> IN as_perc_sign varchar(1),
> IN as_item numeric)
> RETURNS varchar(20)
> BEGIN
> return as_perc_sign|| if as_item is null then 'NULL' else
> convert(varchar(10), as_item) end if;
> END
>
> And for your information, when i replace "my_UDF" with the
> code inside the function the update also works. That is:
> update TableA
> set TableA.col_01 = TableA.col_01||if TableB.col_02 is null
> then 'NULL' else convert(varchar(10), TableB.col_02) end if
> from TableB
> where TableA.sec_type = TableB.sec_type and TableA.year =
> 2008
>
> Thanks!
> Vicky
>
>> > "Manish Negandhi [TeamSybase]"
>> > <negandhi.manish_nosmap@gmail.com> wrote in message
>> > news:503281a1$1@forums-1-dub... >
>> > > <Vicky Chan> wrote in message
>> > news:5031b9c1.56ed.1681692777@sybase.com... >>I created
>> > a UDF (in SQL) in my database and would like to >>
>> > update a table according to the return value of the UDF.
>> > The >> UDF returns a varchar(50) string. >>
>> > >> This is my update statement:
>> > >> update TableA
>> > >> set TableA.col_01 = my_UDF(TableA.col_01,
>> > TableB.col_02) >> from TableB
>> > >> where TableA.sec_type = TableB.sec_type and
>> > TableA.year = >> 2008
>> > >>
>> > >> However, the following SQL error returned:
>> > >> Could not execute statement.
>> > >> Update operation attempted on non-updatable remote
>> > query >> SQLCODE=-728, ODBC 3 State="42000"
>> > >>
>> > >> Please Help!
>> > >
>> > > Can you try changing my_UDF to a a literal value just
>> > > to isolate UDF specific problem ? for example..
>> > > what happens if you use something like .... set
>> > > TableA.col_01 = dummy_value ?
>> > >
>> > > Manish Negandhi
>> > > [TeamSybase]
>> >
>> > can you also post UDF code ?
>> >
>> > Manish Negandhi
>> > [TeamSybase]
>> >
>> >
>> >
>> >
>> > >
>> > >
>> >
>> >


Trevor Moore Posted on 2012-10-26 10:31:02.0Z
Sender: 3214.508a65c5.1804289383@sybase.com
From: Trevor Moore
Newsgroups: sybase.public.iq
Subject: Re: Sybase IQ 15.4 : Problem using UDF in update statement (Urgent)
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <508a6666.3243.1681692777@sybase.com>
References: <503d9bd1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 26 Oct 2012 03:31:02 -0700
X-Trace: forums-1-dub 1351247462 172.20.134.41 (26 Oct 2012 03:31:02 -0700)
X-Original-Trace: 26 Oct 2012 03:31:02 -0700, 172.20.134.41
Lines: 91
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4562
Article PK: 246702

The problem is that UDF's run via the catalog store. You
cannot combine an update/delete query across stores so
although all tables may be in the IQ store if you use a UDF
then the data is pushed via the catalog thus making the data
go across stores. The only way round it is to select the
data into a temp table in the IQ store and perform the
update from there. Or as you have done use a native function
instead of a UDF.

Ta
Trevor

> Can you try adding from iq_dummy within the function ?
>
> as_perc_sign|| if as_item is null then 'NULL' else
> convert(varchar(10), as_item) end if; from iq_dummy
>
> -HTH
> Manish Negandhi
> [TeamSybase]
>
> <Vicky Chan> wrote in message
> > news:503747f0.2c6a.1681692777@sybase.com... Hi Manish,
> >
> > I have tried replacing "my_UDF" to a literal value and
> > records can be updated.
> > My UDF code as follows:
> > ALTER FUNCTION "PIPITS"."my_UDF"(
> > IN as_perc_sign varchar(1),
> > IN as_item numeric)
> > RETURNS varchar(20)
> > BEGIN
> > return as_perc_sign|| if as_item is null then 'NULL'
> > else convert(varchar(10), as_item) end if;
> > END
> >
> > And for your information, when i replace "my_UDF" with
> > the code inside the function the update also works. That
> > is: update TableA
> > set TableA.col_01 = TableA.col_01||if TableB.col_02 is
> > null then 'NULL' else convert(varchar(10),
> > TableB.col_02) end if from TableB
> > where TableA.sec_type = TableB.sec_type and TableA.year
> > = 2008
> >
> > Thanks!
> > Vicky
> >
> >> > "Manish Negandhi [TeamSybase]"
> >> > <negandhi.manish_nosmap@gmail.com> wrote in message
> >> > news:503281a1$1@forums-1-dub... >
> >> > > <Vicky Chan> wrote in message
> >> > news:5031b9c1.56ed.1681692777@sybase.com... >>I
> created >> > a UDF (in SQL) in my database and would like
> to >> >> > update a table according to the return value of
> the UDF. >> > The >> UDF returns a varchar(50) string. >>
> >> > >> This is my update statement:
> >> > >> update TableA
> >> > >> set TableA.col_01 = my_UDF(TableA.col_01,
> >> > TableB.col_02) >> from TableB
> >> > >> where TableA.sec_type = TableB.sec_type and
> >> > TableA.year = >> 2008
> >> > >>
> >> > >> However, the following SQL error returned:
> >> > >> Could not execute statement.
> >> > >> Update operation attempted on non-updatable remote
> >> > query >> SQLCODE=-728, ODBC 3 State="42000"
> >> > >>
> >> > >> Please Help!
> >> > >
> >> > > Can you try changing my_UDF to a a literal value
> just >> > > to isolate UDF specific problem ? for
> example.. >> > > what happens if you use something like
> ... set >> > > TableA.col_01 = dummy_value ?
> >> > >
> >> > > Manish Negandhi
> >> > > [TeamSybase]
> >> >
> >> > can you also post UDF code ?
> >> >
> >> > Manish Negandhi
> >> > [TeamSybase]
> >> >
> >> >
> >> >
> >> >
> >> > >
> >> > >
> >> >
> >> >
>
>