Re: Trying to write a prog to load data AND comply w/referentail integrity

root (root@TeamInfinity.com) wrote: : This may sound like a very easy question, so much the easier for you to : answer hopefully... : We are using informix and powerbuilder. (Just telling you this, we : actually want to just use STANDARD SQL to accomplish this so our platform : should not make a difference) : We are trying to load data from a migration tablespace into a production : tablespace in such a way that referential integrity is maintained, actually : we have no choice because Constraints restrict adding one record to a file : unless a required record is in another file first. Was thinking to use : cursor, and fetch and insert, but how do we determine if a required record : exists in another table where a referential constraint requires there to be : one, before adding a record to another table. Was thinking of using SQL : count, but how do I take that SQL statements output and use it in a : comparison to see if the record exists or not before adding another record : to another table. I am used to using CLIPPER where you can just do a find() : and if eof() I know it does not exist. If you could give me syntax, we : would greatly appreciate it. There is no such think as STRAIGHT SQL right ? : We have to mix it in with C or COBOL, if so C would be the preferred way, as : I do alot of C programming. : Thanks MUCH in advance, : root@TeamInfinity.com You have a lot of requests in there... First - STRAIGHT SQL, as you call it, is probably really known as PL/SQL or Procedural Language SQL. You can LOOP, QUERY, INSERT, Use logical condition checking, etc. This is probably more than enough tool to do what I think you are describing. You can, but definitely don't have to MIX IT WITH C or COBOL. The CLIPPER Find() Eof() stuff is easier in SQL... You use a SELECT statement, and if you get a row returned, then that is the same as Found() := .T. otherwise its .F. In your PL/SQL, you will need to DECLARE a CURSOR, OPEN it, FETCH from it, and use the result in a subsequent IF statement and also an UPDATE statement, then CLOSE the CURSOR. Look up these 'CAPS' commands in a SQL manual for the exact syntax. Hint: It won't look much like Clipper... Good Luck Randy -- ..uu. z' , ___________________ ?$""`?i . x" .uu I Randy I `M .@" d9b . f .,.uun. ?$ ?i' I DeWoolfson I 'Z :#" M `8 U8 < .dP"```"# `M .@" I-----------------I '&H?` X ,oR $5. $ ?* @ 'Z :#" I randyd@cais.com I d#`*L :RP' $b f`$L:M Xf .f' '&H?` I-----------------I ,!"> 5. $' ?X X "NXF M .=` d#` I ,\\\\/. I > 'M d$ `E M "$$ Mux~ n!" I |o o| I k `h ' ' "" ` z" * I=ooO==(_)==Ooo===I % ` " " I_________________I