Get data from a string variable
A user on IDS 7.31 stored a comma-separated list of category codes in a VARCHAR column and needed, inside a FOREACH, to test whether a single category value from another table appears in that list (Java-style split). Suggestions of LIST/SET types and normalising the table were raised, but 7.31 supports neither those types nor dynamic SQL. Art Kagel's working answer was a MATCHES test with the arguments the right way round: WHERE vCategorias MATCHES '*' || IdCategoria || '*', which the poster confirmed worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Dear people of the forum, I have a problem with a stored procedure, which is
that I have a table with a column of type Varchar, which is of variable
length, and in a foreach I have to evaluate if one exists in the data string.
example:
CREATE TABLE TEMP Categories
(CategoryID INTEGER,
Description VARCHAR (100.0)
) WITH NO LOG
LOCK MODE ROW;
INSERT INTO Category VALUES (1, "'04 ', '05', '06 ', '60'")
FOREACH
SELECT CategoryID
INTO vIdCategoria
FROM xxResumen
WHERE CategoryID IN (SELECT Description FROM Categories)
¿Alguien sabe cómo hacer para poder leer los datos separados por coma de la
columna Descripcion, al estilo del split de Java, o de alguna otra manera?
Desde ya, muchas gracias a todos por su atención
Version and platform information will help.
I'm not sure what you are trying to accomplish here.
PLEASE when you post a question, post the original problem you are trying
to solve NOT the possible solution that you thought of but that you can't
get to work!
Post the original problem and we will try to help out.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Mar 14, 2012 at 8:14 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Dear people of the forum, I have a problem with a stored procedure, which
> is
> that I have a table with a column of type Varchar, which is of variable
> length, and in a foreach I have to evaluate if one exists in the data
> string.
> example:
> CREATE TABLE TEMP Categories>
> (CategoryID INTEGER,
>
> Description VARCHAR (100.0)
>
> ) WITH NO LOG
>
> LOCK MODE ROW;
>
> INSERT INTO Category VALUES (1, "'04 ', '05', '06 ', '60'")>
> FOREACH
>
> SELECT CategoryID>
> INTO vIdCategoria
>
> FROM xxResumen
>
> WHERE CategoryID IN (SELECT Description FROM Categories)
>
> ¿Alguien sabe cómo hacer para poder leer los datos separados por coma de la
> columna Descripcion, al estilo del split de Java, o de alguna otra manera?
>
> Desde ya, muchas gracias a todos por su atención
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3b9d977f3da604bb338a0a
Hello, I think you should be using list data type, or set datatype, in order
to access each element, instead of just using varchar type.
Check Information Center documentation about these ok?
Regards.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70
IBM Information Management Informix Technical Professional
IBM Infosphere DataStage Technical Professional
Database Administrator
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: Get data from a string variable [26517]
> Date: Wed, 14 Mar 2012 08:59:13 -0400
>
> Version and platform information will help.
>
> I'm not sure what you are trying to accomplish here.
>
> PLEASE when you post a question, post the original problem you are trying
> to solve NOT the possible solution that you thought of but that you can't
> get to work!
>
> Post the original problem and we will try to help out.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Wed, Mar 14, 2012 at 8:14 AM, GUSTAVO ECHENIQUE <
> gustavo.echenique@cemdo.com.ar> wrote:
>
> > Dear people of the forum, I have a problem with a stored procedure, which
> > is
> > that I have a table with a column of type Varchar, which is of variable
> > length, and in a foreach I have to evaluate if one exists in the data
> > string.
> > example:
> > CREATE TABLE TEMP Categories> >
> > (CategoryID INTEGER,
> >
> > Description VARCHAR (100.0)
> >
> > ) WITH NO LOG
> >
> > LOCK MODE ROW;
> >
> > INSERT INTO Category VALUES (1, "'04 ', '05', '06 ', '60'")> >
> > FOREACH
> >
> > SELECT CategoryID> >
> > INTO vIdCategoria
> >
> > FROM xxResumen
> >
> > WHERE CategoryID IN (SELECT Description FROM Categories)
> >
> > ¿Alguien sabe cómo hacer para poder leer los datos separados por coma de la
> > columna Descripcion, al estilo del split de Java, o de alguna otra manera?
> >
> > Desde ya, muchas gracias a todos por su atención
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f3b9d977f3da604bb338a0a
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Alexandre, thanks for your response! My engine is IDS 7.31 TD6, this version supports the datatypes? Regards. Gustavo Echenique
No it doesn't. That's why you need to post your version info when you post so you don't get answers you cannot use. I was going to propose a solution using dynamic SQL in your stored procedures, but 7.31 doesn't support that either. Now, if you post the problem, maybe we can help? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 14, 2012 at 10:28 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > Alexandre, thanks for your response! > > My engine is IDS 7.31 TD6, this version supports the datatypes? > > Regards. > > Gustavo Echenique > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f646abf9c6fb304bb34d5fd
On Wed, Mar 14, 2012 at 05:14, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Dear people of the forum, I have a problem with a stored procedure, which
> is
> that I have a table with a column of type Varchar, which is of variable
> length, and in a foreach I have to evaluate if one exists in the data
> string.
> example:
> CREATE TABLE TEMP Categories>
> (CategoryID INTEGER,
>
> Description VARCHAR (100.0)
>
> ) WITH NO LOG
>
> LOCK MODE ROW;
>
> INSERT INTO Category VALUES (1, "'04 ', '05', '06 ', '60'")>
> FOREACH
>
> SELECT CategoryID>
> INTO vIdCategoria
>
> FROM xxResumen
>
> WHERE CategoryID IN (SELECT Description FROM Categories)
>
> ¿Alguien sabe cómo hacer para poder leer los datos separados por coma de la
> columna Descripcion, al estilo del split de Java, o de alguna otra manera?
>
Babelfish says that is:
Somebody knows how to do for being able to read the separated data by comma
of the column Description, in the style of split of Java, or some other way?
Don't use non-normalized data like that; it makes it hard to do queries on
it. Redesign your table so that you have:
CREATE TABLE Categories
(
CategoryID INTEGER NOT NULL,
CategoryCode CHAR(2) NOT NULL,
PRIMARY KEY(CategoryID, CategoryCode)
);
The CategoryCode can be CHAR(4) if you need the quotes or or any other
appropriate size. Now you can do regular joins.
If you must go with a list (not recommended), then use an explicit LIST.
See:
http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqlr.doc/ids
_sqr_124.htmamongst
other places in the reference manual.
CREATE TABLE Categories
(
CategoryID INTEGER NOT NULL,
CategoryCodes LIST{CHAR(2) NOT NULL} NOT NULL,
PRIMARY KEY(CategoryID)
);
Note the different name for the column, and the different primary key. I'm
don't think that the optimizer can efficiently join with one of the items
in the list, which is why I wouldn't recommend it.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--f46d040716c79c934b04bb3538bc
Hello again Art!, Good to read your posts. What I need is within a Foreach compare a value in another table must be in a sequence of values. The problem is that being in a sequence, making the content as a single value, then, the comparison does not shed never a true value. example: In Table 1 I have a column called categories, and have these values ​​in the column: '04 ', '05', '06 ', '60', '28 ', '75', '76 ', '77 ' In Table 2, when entering the Foreach records come with some of these values, for example brings the value '06 'but the comparison is '06' = '04 ', '05', '06 ', '60' , '28 ', '75', '76 ', '77', because commas separating the values ​​are part of the chain, and I need to separate them. I hope you understood the problem. Greetings! Gustavo Echenique
Try this: and "'04 ', '05', '06 ', '60', '28 ', '75', '76 ', '77'" matches '*' || '06' || '*' Obviously you will be substituting the variable or column containing the sequence. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 14, 2012 at 11:18 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > Hello again Art!, Good to read your posts. > > What I need is within a Foreach compare a value in another table must be > in a > sequence of values. The problem is that being in a sequence, making the > content as a single value, then, the comparison does not shed never a true > value. > > example: > In Table 1 I have a column called categories, and have these values > ​​in the column: '04 ', '05', '06 ', '60', '28 ', '75', '76 ', > '77 > ' > In Table 2, when entering the Foreach records come with some of these > values, > for example brings the value '06 'but the comparison is '06' = '04 ', '05', > '06 ', '60' , '28 ', '75', '76 ', '77', because commas separating the > values > ​​are part of the chain, and I need to separate them. > > I hope you understood the problem. > > Greetings! > > Gustavo Echenique > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3b9d973cafca04bb359ce9
Art, don't work.
I copy a part or the code:
FOREACH
SELECT xx.DescCateg, xx.Categorias, ConsumoDesde, ConsumoHasta
INTO vDescCateg, vCategorias, vConsumoDesde, vConsumoHasta
FROM xxFinal xx
LET vCategorias = TRIM( vCategorias);
FOREACH
SELECT Anio, NroPer, COUNT(*), SUM( Unidades ), SUM( Importe ), SUM(
ImporteFinal ), SUM( ImpuestosNac ), SUM( ImpuestosProv )
INTO vAnio, vNroPer, vCantUsuarios, vConsumo, vImpSinSub, vImpConSub, vImpNac,
vImpProv
FROM xxResumen
WHERE IdCategoria MATCHES '*' || vCategorias || '*'
AND Unidades >= vConsumoDesde
AND Unidades <= vConsumoHasta
GROUP BY 1,2
vCategorias is the variable wich contains the string of values.
Regards!
Gustavo Echenique
Art, you are a genious!!!
The correct code is:
FOREACH
SELECT xx.DescCateg, xx.Categorias, ConsumoDesde, ConsumoHasta
INTO vDescCateg, vCategorias, vConsumoDesde, vConsumoHasta
FROM xxFinal xx
LET vCategorias = TRIM( vCategorias);
FOREACH
SELECT Anio, NroPer, COUNT(*), SUM( Unidades ), SUM( Importe ), SUM(
ImporteFinal ), SUM( ImpuestosNac ), SUM( ImpuestosProv )
INTO vAnio, vNroPer, vCantUsuarios, vConsumo, vImpSinSub, vImpConSub, vImpNac,
vImpProv
FROM xxResumen
WHERE vCategorias MATCHES '*' || IdCategoria || '*'
AND Unidades >= vConsumoDesde
AND Unidades <= vConsumoHasta
GROUP BY 1,2
And works fine!!!
Thanks, very, very thanks!!!
The other way around:
SELECT Anio, NroPer, COUNT(*), SUM( Unidades ), SUM( Importe ), SUM(
ImporteFinal ), SUM( ImpuestosNac ), SUM( ImpuestosProv )
INTO vAnio, vNroPer, vCantUsuarios, vConsumo, vImpSinSub, vImpConSub,
vImpNac, vImpProv
FROM xxResumen
WHERE vCategorias MATCHES '*' || IdCategoria || '*'
AND Unidades >= vConsumoDesde
AND Unidades <= vConsumoHasta
GROUP BY 1,2;
So, expanded (what you will see in the SET EXPLAIN output) will be:
WHERE "'01', '06', '08', '03'" MATCHES '*06*'
So, here's a trivial example to illustrate the technique:
> select 1 from systables where tabid = 1 and> "'01', '06', '08', '03'" MATCHES '*06*';
(constant)
1
1 row(s) retrieved.
> select 1 from systables where tabid = 1 and> "'01', '06', '08', '03'" MATCHES '*04*';
(constant)
No rows found.
Note that "04" is not in the string of strings but "06" is so the MATCHES
is working as expected.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Mar 14, 2012 at 12:01 PM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Art, don't work.
>
> I copy a part or the code:
>
> FOREACH
>
> SELECT xx.DescCateg, xx.Categorias, ConsumoDesde, ConsumoHasta>
> INTO vDescCateg, vCategorias, vConsumoDesde, vConsumoHasta
>
> FROM xxFinal xx
>
> LET vCategorias = TRIM( vCategorias);
>
> FOREACH
>
> SELECT Anio, NroPer, COUNT(*), SUM( Unidades ), SUM( Importe ), SUM(
> ImporteFinal ), SUM( ImpuestosNac ), SUM( ImpuestosProv )>
> INTO vAnio, vNroPer, vCantUsuarios, vConsumo, vImpSinSub, vImpConSub,
> vImpNac,
> vImpProv
>
> FROM xxResumen
>
> WHERE IdCategoria MATCHES '*' || vCategorias || '*'
>
> AND Unidades >= vConsumoDesde
>
> AND Unidades <= vConsumoHasta
>
> GROUP BY 1,2
>
> vCategorias is the variable wich contains the string of values.
>
> Regards!
>
> Gustavo Echenique
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340661cc55be04bb365d6b