Informix

On the recent Informix roadshow presentation, there was a query to showcase the CTE implementation, and I cannot get it to work ( the 'THANK YOU!' query ).
I am using "IBM Informix Dynamic Server Version 14.10.FC3DE" on linux and I query is failing with error 9700 .
I do not fully understand the query, but the following simpler example gives the 9700 error:

select
  sum( '#'::lvarchar )
from
  systables
where
  tabid = 1
;
9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

I am not sure if I can publish the original query, but for those that have been to the Informix roadshow and know what I am talking about, has anyone got the query to execute?

------------------------------
Luis Marques
------------------------------

#Informix

Original Message------

On the recent Informix roadshow presentation, there was a query to showcase the CTE implementation, and I cannot get it to work ( the 'THANK YOU!' query ).
I am using "IBM Informix Dynamic Server Version 14.10.FC3DE" on linux and I query is failing with error 9700 .
I do not fully understand the query, but the following simpler example gives the 9700 error:

select
  sum( '#'::lvarchar )
from
  systables
where
  tabid = 1
;
9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

I am not sure if I can publish the original query, but for those that have been to the Informix roadshow and know what I am talking about, has anyone got the query to execute?

------------------------------
Luis Marques
------------------------------

#Informix

Thank you Scott.

That was the piece that was missing. After creating the function you posted, the query works fine.

Best regards,

------------------------------
Luis Marques
------------------------------

Original Message:
Sent: Tue December 17, 2019 10:48 AM
From: Scott Pickett
Subject: 9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

Luis:

 

Per developer, this needs to be created as well:

 

create function plus(s1 lvarchar, s2 lvarchar)

            returning lvarchar;

            return s1 || s2;

   end function;

• export DBACCESS_COLUMNS=180
• produces an ASCII-art image of the Mandelbrot set

   dbaccess test Mandelbrot.sql 2>/dev/null |  grep -v '^$'

Scott Pickett
IBM Informix WW Technical Sales

IBM Informix WW Cloud Technical Sales

IBM Informix WW Cloud Technical Sales ICIAE

IBM Informix WW Informix Warehouse Accelerator Sales
Boston, Massachusetts USA
spickett@us.ibm.com
617-899-7549
33 Years Informix User
 

The Informix Roadshow page is here:

https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en_US#/wiki/Informix%20Roadshow%20-%20Informix%20is%20Everywhere

Shortcut to the Informix Roadshow Page:

http://bit.ly/ifmx_roadshow

All presentations and the agenda used by the Roadshow can be found there.
 

The current ZACS Informix Page can be found here:

https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix

 

The older Internal IMAZ CTP Informix Main Page can be found here:
https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix

Website for Internet Of Things
https://www.ibm.com/internet-of-things/

Website for Informix
https://www.ibm.com/analytics/us/en/technology/informix/

 

 

Original Message------

On the recent Informix roadshow presentation, there was a query to showcase the CTE implementation, and I cannot get it to work ( the 'THANK YOU!' query ).
I am using "IBM Informix Dynamic Server Version 14.10.FC3DE" on linux and I query is failing with error 9700 .
I do not fully understand the query, but the following simpler example gives the 9700 error:

select
  sum( '#'::lvarchar )
from
  systables
where
  tabid = 1
;
9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

I am not sure if I can publish the original query, but for those that have been to the Informix roadshow and know what I am talking about, has anyone got the query to execute?

------------------------------
Luis Marques
------------------------------

#Informix

Aha! Now you have the most useful query ever working! :)

------------------------------
FERNANDO NUNES
------------------------------

Original Message:
Sent: Tue December 17, 2019 11:47 AM
From: Luis Marques
Subject: 9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

Thank you Scott.

That was the piece that was missing. After creating the function you posted, the query works fine.

Best regards,

------------------------------
Luis Marques

Original Message:
Sent: Tue December 17, 2019 10:48 AM
From: Scott Pickett
Subject: 9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

Luis:

 

Per developer, this needs to be created as well:

 

create function plus(s1 lvarchar, s2 lvarchar)

            returning lvarchar;

            return s1 || s2;

   end function;

• export DBACCESS_COLUMNS=180
• produces an ASCII-art image of the Mandelbrot set

   dbaccess test Mandelbrot.sql 2>/dev/null |  grep -v '^$'

Scott Pickett
IBM Informix WW Technical Sales

IBM Informix WW Cloud Technical Sales

IBM Informix WW Cloud Technical Sales ICIAE

IBM Informix WW Informix Warehouse Accelerator Sales
Boston, Massachusetts USA
spickett@us.ibm.com
617-899-7549
33 Years Informix User
 

The Informix Roadshow page is here:

https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en_US#/wiki/Informix%20Roadshow%20-%20Informix%20is%20Everywhere

Shortcut to the Informix Roadshow Page:

http://bit.ly/ifmx_roadshow

All presentations and the agenda used by the Roadshow can be found there.
 

The current ZACS Informix Page can be found here:

https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix

 

The older Internal IMAZ CTP Informix Main Page can be found here:
https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix

Website for Internet Of Things
https://www.ibm.com/internet-of-things/

Website for Informix
https://www.ibm.com/analytics/us/en/technology/informix/

 

 

Original Message------

On the recent Informix roadshow presentation, there was a query to showcase the CTE implementation, and I cannot get it to work ( the 'THANK YOU!' query ).
I am using "IBM Informix Dynamic Server Version 14.10.FC3DE" on linux and I query is failing with error 9700 .
I do not fully understand the query, but the following simpler example gives the 9700 error:

select
  sum( '#'::lvarchar )
from
  systables
where
  tabid = 1
;
9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

I am not sure if I can publish the original query, but for those that have been to the Informix roadshow and know what I am talking about, has anyone got the query to execute?

------------------------------
Luis Marques
------------------------------

#Informix

Original Message------

Aha! Now you have the most useful query ever working! :)

------------------------------
FERNANDO NUNES
------------------------------

#Informix

you need to define what plus means in the context of a lvarchar.  If you want to mean different things to different values then you  can code that in the plus function or cast to a 'special' lvarchar type and go from there

Cheers
Paul

------------------------------
Paul Watson
------------------------------

Original Message:
Sent: Tue December 17, 2019 10:19 AM
From: Luis Marques
Subject: 9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

On the recent Informix roadshow presentation, there was a query to showcase the CTE implementation, and I cannot get it to work ( the 'THANK YOU!' query ).
I am using "IBM Informix Dynamic Server Version 14.10.FC3DE" on linux and I query is failing with error 9700 .
I do not fully understand the query, but the following simpler example gives the 9700 error:

select
  sum( '#'::lvarchar )
from
  systables
where
  tabid = 1
;
9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

I am not sure if I can publish the original query, but for those that have been to the Informix roadshow and know what I am talking about, has anyone got the query to execute?

------------------------------
Luis Marques
------------------------------

#Informix

Thank you Paul.

Scott provided the appropriate function for the query to work.
The error message was misleading me, I was assuming that there were multiple "plus" definitions already defined, that were ambiguous, not that I needed to define the "plus" operation for the "SUM" aggregation to work for "LVARCHAR".

Best regards,

------------------------------
Luis Marques
------------------------------

Original Message:
Sent: Tue December 17, 2019 11:34 AM
From: Paul Watson
Subject: 9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

you need to define what plus means in the context of a lvarchar.  If you want to mean different things to different values then you  can code that in the plus function or cast to a 'special' lvarchar type and go from there

Cheers
Paul

------------------------------
Paul Watson

Original Message:
Sent: Tue December 17, 2019 10:19 AM
From: Luis Marques
Subject: 9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

On the recent Informix roadshow presentation, there was a query to showcase the CTE implementation, and I cannot get it to work ( the 'THANK YOU!' query ).
I am using "IBM Informix Dynamic Server Version 14.10.FC3DE" on linux and I query is failing with error 9700 .
I do not fully understand the query, but the following simpler example gives the 9700 error:

select
  sum( '#'::lvarchar )
from
  systables
where
  tabid = 1
;
9700: Routine (plus) ambiguous - more than one routine resolves to given signature.

I am not sure if I can publish the original query, but for those that have been to the Informix roadshow and know what I am talking about, has anyone got the query to execute?

------------------------------
Luis Marques
------------------------------

#Informix