Re: SQL help needed.

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/ ---