Problem with ER

IDS 10.00.FC6 Solaris 10 I'm currently defining Enterprise Replication. I have a table (table1) that I want to replicate into table2. The first replicate is: cdr define replicate -C ignore --fullrow n --floatieee --ats --ris replic1 \\\\ "P db1@grpm:informix.table1" \\\\ "select code, name1 ,'1' from table1 where name1<>'' " \\\\ "R db2@grps:informix.table2" \\\\ "select uid,name,seq_no from table2" Second replicate: cdr define replicate -C ignore --fullrow n --floatieee --ats --ris replic2 \\\\ "P db1@grpm:informix.table1" \\\\ "select code, name2 ,'2' from table1 where name2<>'' " \\\\ "R db2@grps:informix.table2" \\\\ "select uid,name,seq_no from table2" I want the third column of table2 to get the value 1 or 2 depending on the replicate (column seq_no of table2 is defined as a smallint). When I run the statement to create the replicate I get the following error: command failed -- unsupported SQL syntax (join, etc..) (40) Replicate rule replic1 has been defined. When I remove the third column from the replication, it works. The problem comes from the '1' or '2' that I try to insert into the third column. However, I really need this column and I want it to have the right value. Any idea of how to get it run??? Thanks

Sometimes I wish that we had not made the selection list into a select statement for ER. It makes things confused with normal SQL. The projection list that ER uses for replication purposes must consist = only of real columns from the table. No joins allowed, and also no literals= . To do what you want to do, you would need to create two distinct tables an= d then create a view of a union of those two tables, including the litera= l 1 or 2. An alternative solution would be to create two staging tables an= d then create insert triggers on top of that table which would place the = data into the final target table - with the literal for the third column. Y= ou would need to define replication with the trigger firing turned on. M.P. = "GEORGES MARTIN" = <georges_martin_1 = @hotmail.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Problem with ER [9726] = 08/08/2007 10:53 = AM = = = Please respond to = ids@iiug.org = = = IDS 10.00.FC6 Solaris 10 I'm currently defining Enterprise Replication. I have a table (table1) = that I want to replicate into table2. The first replicate is: cdr define replicate -C ignore --fullrow n --floatieee --ats --ris repl= ic1 \\\\ "P db1@grpm:informix.table1" \\\\ "select code, name1 ,'1' from table1 where name1<>'' " \\\\ "R db2@grps:informix.table2" \\\\ "select uid,name,seq_no from table2" Second replicate: cdr define replicate -C ignore --fullrow n --floatieee --ats --ris repl= ic2 \\\\ "P db1@grpm:informix.table1" \\\\ "select code, name2 ,'2' from table1 where name2<>'' " \\\\ "R db2@grps:informix.table2" \\\\ "select uid,name,seq_no from table2" I want the third column of table2 to get the value 1 or 2 depending on = the replicate (column seq_no of table2 is defined as a smallint). When I run the statement to create the replicate I get the following er= ror: command failed -- unsupported SQL syntax (join, etc..) (40) Replicate rule replic1 has been defined. When I remove the third column from the replication, it works. The prob= lem comes from the '1' or '2' that I try to insert into the third column. However, I really need this column and I want it to have the right value. Any idea of how to get it run??? Thanks ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Thanks for replying Madison,would it be possible to provide examples for both solutions so that I can pick up the best suitable for my needs.I think I would go with the first solution but I don't really understand your recommendation for the view creation.If you could provide me with an example for at least the first recommendation (using views), I would appreciate.Regards> To: ids@iiug.org> From: mpruet@us.ibm.com> Subject: Re: Problem with ER [9736]> Date: Wed, 8 Aug 2007 16:23:44 -0400> > Sometimes I wish that we had not made the selection list into a select > statement for ER. It makes things confused with normal SQL. > > The projection list that ER uses for replication purposes must consist = > only > of real columns from the table. No joins allowed, and also no literals= > .. To > do what you want to do, you would need to create two distinct tables an= > d > then create a view of a union of those two tables, including the litera= > l 1 > or 2. An alternative solution would be to create two staging tables an= > d > then create insert triggers on top of that table which would place the = > data > into the final target table - with the literal for the third column. Y= > ou > would need to define replication with the trigger firing turned on. > > M.P. > > = > > "GEORGES MARTIN" = > > <georges_martin_1 = > > @hotmail.com> = > To > > Sent by: ids@iiug.org = > > ids-bounces@iiug. = > cc > > org = > > Subj= > ect > > Problem with ER [9726] = > > 08/08/2007 10:53 = > > AM = > > = > > = > > Please respond to = > > ids@iiug.org = > > = > > = > > IDS 10.00.FC6 > Solaris 10 > > I'm currently defining Enterprise Replication. I have a table (table1) = > that > I > want to replicate into table2. > > The first replicate is: > cdr define replicate -C ignore --fullrow n --floatieee --ats --ris repl= > ic1 > \\\\ > "P db1@grpm:informix.table1" \\\\ > "select code, name1 ,'1' from table1 where name1<>'' " \\\\ > "R db2@grps:informix.table2" \\\\ > "select uid,name,seq_no from table2" > > Second replicate: > cdr define replicate -C ignore --fullrow n --floatieee --ats --ris repl= > ic2 > \\\\ > "P db1@grpm:informix.table1" \\\\ > "select code, name2 ,'2' from table1 where name2<>'' " \\\\ > "R db2@grps:informix.table2" \\\\ > "select uid,name,seq_no from table2" > > I want the third column of table2 to get the value 1 or 2 depending on = > the > replicate (column seq_no of table2 is defined as a smallint). > When I run the statement to create the replicate I get the following er= > ror: > > command failed -- unsupported SQL syntax (join, etc..) (40) > Replicate rule replic1 has been defined. > > When I remove the third column from the replication, it works. The prob= > lem > comes from the '1' or '2' that I try to insert into the third column. > However, > I really need this column and I want it to have the right value. > Any idea of how to get it run??? > > Thanks > > ***********************************************************************= > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Discover the new Windows Vista http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE

Thanks for replying Madison, would it be possible to provide examples for both solutions so that I can pick up the best suitable for my needs.I think I would go with the first solution but I don't really understand your recommendation for the view creation.If you could provide me with an example for at least the first recommendation (using views), I would appreciate. Regards

Create replicates to the two tables (tab1, and tab2), then create a vie= w which has a union select. As an example consider the following.... create database cmpdb with log; create table tab1 (col1 int primary key, col2 char(20)); create table tab2 (col1 int primary key, col2 char(20)); create view utab (col1, col2, col3) as select col1, col2, 1 from tab1 union select col1, col2, 2 from tab2; insert into tab1 values (1, "row1"); insert into tab2 values (2, "row2"); select * from utab; A select from the view utab is --- col1 col2 col3 1 row1 1 2 row2 2 which is what your goal is. = "GEORGES MARTIN" = <georges_martin_1 = @hotmail.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: Problem with ER [9745] = 08/09/2007 12:55 = PM = = = Please respond to = ids@iiug.org = = = Thanks for replying Madison, would it be possible to provide examples for both solutions so that I c= an pick up the best suitable for my needs.I think I would go with the first solution but I don't really understand your recommendation for the view creation= .If you could provide me with an example for at least the first recommendation (using views), I would appreciate. Regards ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Thanks Madison, now I understand your point. In the example, I gave only 2 replicates to simplify things. In reality, for this particular table, we have 40 replicates associated to it (name1,name2 ...,name40). From my understanding, it would mean that we have to create 40 tables for our needs. The problem comes from our source database that is not normalized while the target one is. I think creating 40 new tables would be too much since we want to keep our target database small. Currently he target database has less than 30 tables. Is there an easiest way to do it without adding lot of tables or do you have at IBM a plan for making using litterals in the projection list possible in the next IDS releases? Regards

It's going to be the same amount of space used - be it one large table = or 40 small ones. No at the current time, we are not planning on extendin= g the projection list to include literals. = "GEORGES MARTIN" = <georges_martin_1 = @hotmail.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: Problem with ER [9752] = 08/10/2007 07:29 = AM = = = Please respond to = ids@iiug.org = = = Thanks Madison, now I understand your point. In the example, I gave only 2 replicates t= o simplify things. In reality, for this particular table, we have 40 replicates associated to it (name1,name2 ...,name40). From my understanding, it wo= uld mean that we have to create 40 tables for our needs. The problem comes = from our source database that is not normalized while the target one is. I t= hink creating 40 new tables would be too much since we want to keep our targ= et database small. Currently he target database has less than 30 tables. Is there an easiest way to do it without adding lot of tables or do you= have at IBM a plan for making using litterals in the projection list possibl= e in the next IDS releases? Regards ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =