Re: INSERT INTO ... UNION - unsupported!?
> It seems you are not destined to like it. Go back to SQL server or Cloudscape. > Everything about Informix is clearly a pain in the butt. I'm sorry, you are right - I do sound whiny, and everyone on this newsgroup has been very helpful. Regarding Informix, we're still willing to be convinced, esp. by real users like yourself. I guess that our expectations need adjusting - I was thinking Informix provides features out the wazoo, great performance, and stellar standards compliance. After all, it is one of the "big 3" (or 4 or whatever). Instead we're seeing something more organic - features that are strong in some areas, but stragely weak or lacking in others. Given adequate time to port, it wouldn't be too bad, but deadline pressures combined with the port taking an unexpectely long time... Anyway, sorry. > FWIW, I don't think a > view will work, but why not just do a couple of INSERTs? Why does it have to be > a UNION, rather than separate INSERTs? We wanted the UNION's distict feature, i.e., we only want unique records from the separate SELECTs. I guess we could change our code to do each select in order, and inserting each subsequent one based on whether there's a match or not... matt-mr-tail-between-his-legs
In the year of Our Lord Wed, 13 Dec 2000 16:48:36 -0500, Matthew Cornell <cornell@cs.umass.edu> broke a vow of silence to utter: >> It seems you are not destined to like it. Go back to SQL server or Cloudscape. >> Everything about Informix is clearly a pain in the butt. > >I'm sorry, you are right - I do sound whiny, and everyone on this >newsgroup has been very helpful. Regarding Informix, we're still willing >to be convinced, esp. by real users like yourself. I guess that our Real users like myself? I just consult, mate, I don't actually *do* anything. :0) >expectations need adjusting - I was thinking Informix provides features >out the wazoo, great performance, and stellar standards compliance. >After all, it is one of the "big 3" (or 4 or whatever). Instead we're >seeing something more organic - features that are strong in some areas, >but stragely weak or lacking in others. Given adequate time to port, it >wouldn't be too bad, but deadline pressures combined with the port >taking an unexpectely long time... Anyway, sorry. > >> FWIW, I don't think a >> view will work, but why not just do a couple of INSERTs? Why does it have to be >> a UNION, rather than separate INSERTs? > >We wanted the UNION's distict feature, i.e., we only want unique records >from the separate SELECTs. I guess we could change our code to do each >select in order, and inserting each subsequent one based on whether >there's a match or not... Or you could put a UNIQUE constraint on the table, build a cursor, insert into the table and just ignore duplicate errors. Where's Paul Brown when we need him?
Obnoxio The Clown wrote in message <3a37f9cd.14802645@News.CIS.DFN.DE>... >In the year of Our Lord Wed, 13 Dec 2000 16:48:36 -0500, Matthew Cornell ><cornell@cs.umass.edu> broke a vow of silence to utter: > > >Or you could put a UNIQUE constraint on the table, build a cursor, insert into >the table and just ignore duplicate errors. > >Where's Paul Brown when we need him? Who's Paul Brown? I'm surprised to see the rule against UNION in the statement. Had to grab the manual to confirm it - sorry - I don't want to sound like I don't believe you. Have you tried defining a view which is the UNION? It may not work however, because Informix views tend to act like macros, so the same technical limitation blocking a straight-up UNION may well kick in. Don't be afraid of temp tables. They can be quite efficient in Informix, especially if you have explicit temp table dbspaces with the T bit switched on to prevent logging. Or, if you create a temp table explicitly, use the WITH NO LOG syntax at the end for similar performance-enhancing results. I noticed from the manual that stored procedures can be used, so that's another possible solution. No doubt you have to use a "cursory" stored procedure. Is that a dumb label for a style of SPL or what??? Finally, Obbies suggestion (can I be that familiar? there's a Monty Python sketch in there somewhere) of using constraints and ignoring errors triggered me to remember good ol' violation tables. This is a concept whereby you setup attached tables which receive naughty rows that violate table constraints in the face of INSERT, UPDATE and I believe even DELETES. Check out the START VIOLATIONS TABLE ... statement. I've had some good results using that to simplify data loads and to simultaneously catch the naughty rows for further examination - exactly what they were designed for! Yay! __END__ Andrew Hamm Technical Consultant Sanderson Australia Pty Ltd e-mail: <mailto:ahamm@sanderson.net.au> web: <http://www.sanderson.net.au>; -- I like cats too - let's exchange recipies