Re: SQL help needed.

Peter Lancashire wrote: > > Sam Glattstein wrote: > > > > I am running Informix SQL Version 2.10 and I am trying to enter values > > into several fields in a given form. Once these values have been entered > > I want to update all of records ( by their inventory numbers ) that I > > apply these values to. This would be a type of batch update where all of > > the inventory numbers that are entered would be updated with the values > > entered for the given fields. If I have to update the same values every > > time for 100 different inventory numbers it becomes extremely tedious. > > The goal is to enter these values once and then enter the inventory > > numbers that they will apply to for the update. > > I have spent a lot of time trying to figure this out and I want to do it > > only in this Informix SQL. Can anyone help? > > > > EXAMPLE: > > > > These are fields where the values will be input to: > > > > Salesman: [a1 ] > > Date Given: [f015 ] > > Date Sold: [f016 ] > > Customer: [f017 ] > > Cust. PO#: [f018 ] > > Inv/memo#: [f014 ] > > Inv or Memo: [c] (I,M,N,O) > > > > The values entered above should update records for the multiple > > inventory numbers to be entered in this field : > > > > Inventory Number: [f000 ] > > > > > > > > BELOW IS THE PERFORM FORM THAT NEEDS TO BE MODIFIED > > ------------------------------------------------------------------------------------------------------------------ > > database northern without null input > > screen > > { > > > > NORTHERN - SALESMEN INVENTORY UPDATE > > > > Inventory Number: [f000 ] > > Item Number: [f001 ] > > Color: [b] > > C. Weight: [f003 ] > > D. Weight: [f005 ] > > Selling Price: [f011 ] > > > > > > Salesman: [a1 ] > > Date Given: [f015 ] > > Date Sold: [f016 ] > > Customer: [f017 ] > > Cust. PO#: [f018 ] > > Inv/memo#: [f014 ] > > Inv or Memo: [c] (I,M,N,O) > > > > } > > end > > tables > > inventory > > detail > > mountings > > attributes > > f000 = inventory.invnum, noupdate, required; > > f001 = inventory.itmid, noupdate, required, upshift, picture = "A-####", > > default = " - "; > > b = inventory.clr, noupdate, upshift, default = "."; > > f003 = inventory.clrwgt, noupdate, format = "##.##", default = .00; > > f005 = inventory.diawgt, noupdate, format = "##.##", default = .00; > > f011 = inventory.selpr, noupdate, right; > > a1 = inventory.sman, upshift; > > f015 = inventory.dgiven; > > f016 = inventory.dsold; > > f017 = inventory.cusid, upshift; > > f018 = inventory.aux1, upshift; > > f014 = inventory.comm2, upshift; > > c = inventory.qques, upshift, include = > > ("M","m","I","i","N","n","O","o"); > > > > end > > ----------------------------------------------------------------------------------------------------------- > > Any suggestions would be greatly appreciated. > > > > Sam Glattstein > > Basically, you can't do this. You could write a special C function using > ESQL/C to do it and link it into a custom Perform runner. An alternative > would be to write other special functions to spit out what the user > enters into a file which, after manipulation, would be run as SQL in a > separate process. > > You have run up against the limits of Perform, which works strictly one > row at a time. If you can't live with this, you'll have to upgrade to > 4GL, which requires more programming to achieve what Perform does (if > 4GL is a 4GL, then Perform is a 5GL ;-). > > -- > Peter Lancashire > Information Systems Specialist, Bayer plc > Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK > Tel: +44-1635-562258, Fax: +44-1635-562281 > --- > If all else fails, read the instructions and the release notes. > Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ > --- Peter, you gave me 2 options. 1. To write a special C function using ESQL/C linking it into a custom Perform runner. Would this proceedure compromise data inegrity? 2. Your second alternative by writing a special functions to spit out what the user enters into a file which, after manipulation, would be run as SQL in a separate process. Could you elaborate on this one. Thanks Sam