SQL RPGLE cursor delete from view? -204

  • Permalink
  • submit to reddit
  • Email
  • Follow


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
0
Reply stanguru (27) 5/25/2004 8:54:16 PM

See related articles to this posting

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

0
Reply jonball (677) 5/25/2004 9:19:44 PM

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@whitehouse.not> wrote in message news:<QLOsc.9542$Tn6.4329@newsread1.news.pas.earthlink.net>...
> 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
0
Reply stanguru (27) 5/26/2004 12:10:53 PM

Stan The Man wrote:

> 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

Whatever, you just can't delete from a cursor that 
joins multiple files.

> 
> 
> Jonathan Ball <jonball@whitehouse.not> wrote in message news:<QLOsc.9542$Tn6.4329@newsread1.news.pas.earthlink.net>...
> 
>>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

0
Reply jonball (677) 5/26/2004 3:03:16 PM

In article <Uk2tc.12022$be.10695@newsread2.news.pas.earthlink.net>, 
jonball@whitehouse.not says...
> Stan The Man wrote:
> 
> > 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
> 
> Whatever, you just can't delete from a cursor that 
> joins multiple files.
> 

Even if you could do the delete from the cursor, you wouldn't want to 
since as I read your post you simply want to remove a record from the 
current result set without affecting the actual data.

Deleting via a cursor deletes the row in the underlying table.

A cursor and/or a view is simply a window or an access path into the 
original table(s).


To do this with SQL, you'll need to do the same thing you'd need to do 
with RPG....build an actual temporary work file.

Depending on the version of OS/400 you are running:

create table QTEMP/MYWORKFILE <...>

<or>

declare global temporary table <...>


HTH,
CHarles
0
Reply cwilt1 (190) 5/26/2004 5:06:55 PM

Charles Wilt wrote:

> In article <Uk2tc.12022$be.10695@newsread2.news.pas.earthlink.net>, 
> jonball@whitehouse.not says...
> 
>>Stan The Man wrote:
>>
>>
>>>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
>>
>>Whatever, you just can't delete from a cursor that 
>>joins multiple files.
>>
> 
> 
> Even if you could do the delete from the cursor, you wouldn't want to 
> since as I read your post you simply want to remove a record from the 
> current result set without affecting the actual data.

I'm not sure he is really only trying to delete from 
the result set, although it's possible; the language is 
a little opaque.  In any event, a DELETE operation in 
SQL definitely is the wrong way to do it.

Your suggestion below about populating a temporary 
table below, then working with its contents as the 
result set, possibly would work for the original poster.

> 
> Deleting via a cursor deletes the row in the underlying table.
> 
> A cursor and/or a view is simply a window or an access path into the 
> original table(s).
> 
> 
> To do this with SQL, you'll need to do the same thing you'd need to do 
> with RPG....build an actual temporary work file.
> 
> Depending on the version of OS/400 you are running:
> 
> create table QTEMP/MYWORKFILE <...>
> 
> <or>
> 
> declare global temporary table <...>
> 
> 
> HTH,
> CHarles

0
Reply jonball (677) 5/26/2004 6:06:47 PM

thanx fellas

i am a lazy programmer...not necessarily lazy just hate hardcoding
stuff and move fields back and forth...i like clean code, i was trying
to make this thing dynamic and not define a "work file" with defined
fields...but i guess i gotta build a work file. i was just tryin to
make it easy to change, b/c i know this app will be mod'ed alot.

i was able to accomplish my task with STRSQL to produce workfile,
RPGLE to scrub, then an SQLRPGLE to print(had to get the project
out)...am working on combining all

has anyone wrote the same app in RPGLE and SQLRPGLE to see which one
actually runs faster??? i will post this separate to see all the
responses

Jonathan Ball <jonball@whitehouse.not> wrote in message news:<X05tc.12208$be.7008@newsread2.news.pas.earthlink.net>...
> Charles Wilt wrote:
> 
> > In article <Uk2tc.12022$be.10695@newsread2.news.pas.earthlink.net>, 
> > jonball@whitehouse.not says...
> > 
> >>Stan The Man wrote:
> >>
> >>
> >>>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
> >>
> >>Whatever, you just can't delete from a cursor that 
> >>joins multiple files.
> >>
> > 
> > 
> > Even if you could do the delete from the cursor, you wouldn't want to 
> > since as I read your post you simply want to remove a record from the 
> > current result set without affecting the actual data.
> 
> I'm not sure he is really only trying to delete from 
> the result set, although it's possible; the language is 
> a little opaque.  In any event, a DELETE operation in 
> SQL definitely is the wrong way to do it.
> 
> Your suggestion below about populating a temporary 
> table below, then working with its contents as the 
> result set, possibly would work for the original poster.
> 
> > 
> > Deleting via a cursor deletes the row in the underlying table.
> > 
> > A cursor and/or a view is simply a window or an access path into the 
> > original table(s).
> > 
> > 
> > To do this with SQL, you'll need to do the same thing you'd need to do 
> > with RPG....build an actual temporary work file.
> > 
> > Depending on the version of OS/400 you are running:
> > 
> > create table QTEMP/MYWORKFILE <...>
> > 
> > <or>
> > 
> > declare global temporary table <...>
> > 
> > 
> > HTH,
> > CHarles
0
Reply stanguru (27) 5/27/2004 12:32:21 PM
comp.sys.ibm.as400.misc 8989 articles. 9 followers. Post

6 Replies
151 Views

Similar Articles

[PageSpeed] 47

  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

View to view SQL
I'm not very good with SQL and would appreciate some advice. I have a view in 8.1.7.4 that retrieves nearly a hundred fields from five different tables. It's long but simple and straightforward, and has been working quite well for some time. I've just been told that I now need to also retrieve some SUMs and COUNTs from fields in another table. Rather than modifying this view with 100 Group BYs, I wonder if it is feasible to create another view that gets the SUMs and COUNTs and then add that view's output fields to this view's? Is that a smart way to approach th...

view works, but the sql from the view does not
I was looking through our vendors views, searching for something I needed for our Datawarehouse and I came across something I do not understand: I found a view that lists data when I use it in t-sql, however when I try to use the statement when I modified the view (via MS SQL Server Management Studio) I can not execute the statement. I get The column prefix 'dbo.tbl_5001_NumericAudit' does not match with a table name or alias name used in the query. Upon closer inspection, I found two ON for the inner join, which I dont think is correct. So, how can the view work, but not the SQL tha...

Differences between SAS views and SQL views
hello all, for MANY reasons we are moving our SQL views on Oracle to SAS views. but see the differences between that two codes, 'drop' and 'rename' statements are not processed in the same order... Xavier. proc sql; create table TEST (VISIT NUM, VISCOD NUM, TOTO NUM); quit; proc sql; create view TEST1 as select * from TEST; quit; data TEST2/view=TEST2; set TEST; run; proc sort data=TEST1(drop=VISIT rename=(VISCOD=VISIT)) out=SEE1; by TOTO; run; proc sort data=TEST2(drop=VISIT rename=(VISCOD=VISIT)) out=SEE2; by TOTO; run; ...

How to view the converted sql from Activerecord method in view
Hi All, I will be happy if someone clear doubt, i can see objects in view by using <%= debug @object %> and lot of methods is there apart from view like to_yml, etc Is there any method available for seeing the converted sql from ActiveRecord method in view, etc. Although I can find it in console but it will confuse when we run multiple queries.. example: User.find :all it will produce "SELECT * FROM users;" in output console But i want it in view are any other specific point like yml , etc ? Thanks, Jak -- Posted via http://www.ruby-for...

Re: Differences between SAS views and SQL views
Xavier, Yes, but proc sort data=TEST1 out=SEE1(drop=VISIT rename=(VISCOD=VISIT)); by TOTO; run; proc sort data=TEST2 out=SEE2(drop=VISIT rename=(VISCOD=VISIT)); by TOTO; run; From a recent SAS notes message it became clear that they fixed the 15 year cheat of DROP and KEEP on input being shifted to OUTPUT (V6) by making a work copy of the file using the _DATA_ option as shown by the log: 57 data w ; x = 1 ; y = 2 ; run ; NOTE: The data set WORK.W has 1 observations and 2 variables. NOTE: DATA statement used: real time 0.04 seconds cpu time 0.01 se...

SQL Views
Hi guys, I've been asked to re-write a sql view. The view itself contains several calls to other views (embedded). Is there a way to get around using embedded views. I've written the same query up using temp. tables but obviously temp. tables can't be used in views? Is there any special things I should be looking for? (colabus@gmail.com) writes: > I've been asked to re-write a sql view. The view itself contains > several calls to other views (embedded). Is there a way to get around > using embedded views. I've written the same query up using temp. > tables ...

How to Delete Login (SQL-DMO)?
When deleting a database and all it's objects...How can I accomplish that with DMO...I've tried several things with no success. Thanks! ...

Delete a row fetched by a cursor in a cursor loop
Hello, I small example would be better than a lot of words. Can I do this ? <<< declare cursor myCursor is select objectid from myTable where value = 'TEST'; begin for myRecord in myCursor loop delete from myTable where objectid = myRecord.objectid; commit; end loop; end; >>> I would really appreciate to be able to do this but I am afraid of side effects, because I modify the cursor data set during its usage. Which precaution must I take ? Any advice welcome. Thanks a lot AD Albert Dupond <ad@ad.com> wrote ...

Refresh view
Hi. In a formula I delete the current document by Lotusscript placed in the Queryclose event, when the document is closed and I went back to view, the document is mark for deletion, but not removed from the view. If I press the F9, or close the database it is removed. I'd really like it if I could somehow refresh the display just as if F9 had been pressed. I have tried both the Dim ntview As NotesView ntview.refresh and Dim workspace As New NotesUIWorkspace workspace.ViewRefresh but without any luck. Thanks for any help. J�rn Try changing the fields on th...

assotiating SQL text from the V$%SQL% views with sessions
Hi, Is there any way to associate SQL text from the V$%SQL% views with sessions. A piece of code in our application locks up a critical table about once a month. I suspect that the code is not committing one or more transactions on that table. I can find out which session is locking that specific table and I can detect the locking of the table about 2 min after it happens. I tried looking at V$%SQL% views but couldn't associate code with sessions. I'm not permitted to trace changes to that specific table (or any other table). Is there a way of associating code with S...