Re: SQL RPGLE cursor delete from view? -204

this is just a "temporary" file in memory
maybe i dont have my verbage correct
i just want to delete the record in my "selected set" in you will
i could do this in RPG, but i wanted to use SQL and do it all in 1 smooth step

this is just a read-only and print application


Jonathan Ball <jonball@xxxxxxxxxxxxxx> wrote in message news:<QLOsc.9542$Tn6.4329@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>...
You cannot delete from joined files. Think about it:
how does the SQL engine know which row(s) to delete?

You need to figure out the table(s) from which you want
to delete row(s), then delete individually from each one.

The table names you provide make it look like an order
processing application, or possibly some shippig
application. Presumably, you don't want to delete the
customer master and/or customer address rows, do you?
More likely, just some obsolete order/shipping detail
records.

In that case, you need to capture the relevant key
fields just from those rows, then do a row deletion
using them. Assuming you've SELECTed the
(hypothetical) order number and order line number in
your cursor and have them in host variables, then after
your user presses some function key, you'd execute:

C/exec sql
C+ delete from CSHDET
C+ where csh_ord_no = :host_ord_no
C+ and csh_lin_no = :host_line_no
C/end-exec


Stan The Man wrote:
real world app with SQL (did SQL in school but never had to used it)
done SQL in excel macros and net.data, but never needed to manipulate
the data
and most have been "select * from file with status = 'A'"...simple
so...now i am going complex

i have a huge select statement joining 6 files, with some selection
once i read it in, i need to delete a row if it meets a certain type,
then read the previous row and delete it also.

heres the code
i have taken variable names out, shortened some code(to protect the
innocent)

my problem is the delete, i get a SQLCOD = -204 in which the
DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG) is a whole lotta help

C/exec sql
C+ declare C1 dynamic scroll cursor for
C+ select t1.f1,t1.f2,t1.f2 ...
C+ as tview
C+ from CSHDET T1, CSHSUM T2, INVMST T3, CUSTH T4, CUSADRL1 T5,
C+ MSTZEM T6
C+ where t1.f1 = t2.f1 and
C+ t1.f2 = t2.f2 and
C+ ....
C+ order by f1, f2...
C/end-exec

C/exec sql
C+ open C1
C/end-exec

C dou SQLCOD <> 0

C/exec sql
C+ fetch C1 into :f1,
C+ :f2,
C+ :f3,
C+ ...
C/end-exec

C if SQLCOD = 0
C if f3 = '11'

C/exec sql
C+ delete from tview where current of C1
C/end-exec

C/exec sql
C+ fetch PRIOR from C1 into :f1,
C+ :f2,
C+ ...
C/end-exec

C/exec sql
C+ delete from tview where current of C1
C/end-exec

C endif
C enddo

C/exec sql
C+ close C1
C/end-exec


initially i had it just selecting and printing records out just so i
could get the SQL RPG concepts down, then i added in the deleting

i think i am not understanding the "view" and "cursor"

thanx in advance
.

Relevant Pages

  • Re: updating to a table
    ... "Jimenda" wrote in message ... > selection in each box, and then ... Debug.Print SQL ... The challenge is in getting a string built up that results in a valid SQL ...
    (microsoft.public.access.forms)
  • Re: Requery Combo Box
    ... The SQL was generated like you said, ... On the same criteria row for each column add the restrictions ... The user selects a name from the first combo box that has a query behind ... After the selection, ...
    (microsoft.public.access.queries)
  • Re: Syntax of SQL in VB
    ... >a combo box based on a selection in another combo box. ... I choose Honda in one combo box and Honda models ... is in the use of quotation marks to fit the SQL ... the entire SQL is a string and must be within quotation marks. ...
    (microsoft.public.access.queries)
  • Re: Access/SQLServer Requery Recordsource
    ... make sure you put the record selection controls in the header ... > alter the recordsource SQL, you need to do it in the form Open ... > or determine the initial record before the CBO is visible. ... > a saved querydef as the source for the form. ...
    (microsoft.public.access.formscoding)
  • SQL RPGLE cursor delete from view? -204
    ... real world app with SQL ... done SQL in excel macros and net.data, but never needed to manipulate ... i have a huge select statement joining 6 files, with some selection ... C+ fetch PRIOR from C1 into:f1, ...
    (comp.sys.ibm.as400.misc)