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 9019 articles. 12 followers. Post

6 Replies
206 Views

Similar Articles

[PageSpeed] 2


  • 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 ...

How do you delete a custom view in the View Orientation dialog box?
How do you delete a custom view in the View Orientation dialog box? I created some temporary views, and I cant seem to delete them with the delete key or right-click. Highlight and hit your Delete key. Just tried it and it works that way on mine. WT "SW Monkey" <ggroups.50.bb007@spamgourmet.com> wrote in message news:1164663827.012554.261990@j72g2000cwa.googlegroups.com... > How do you delete a custom view in the View Orientation dialog box? I > created some temporary views, and I cant seem to delete them with the > delete key or right-click. > -- Po...

#deleted problem when deleting records from sql server through vba
I have a front-end access database that uses a sql server back-end to link the tables. When I run the following code, the subform displays #deleted in place of the deleted record. What am I doing wrong??? s = "DELETE WMS_FID FROM [tmp_PID] WHERE [WMS_FID]=" & Me.txt_WMS_FID domcd.RunSQL (s) '*** PMP 9/22/07 - Copy the records to be deleted from t_PID to t_PID_Deleted table s = "INSERT INTO t_PID_Deleted ( WMS_FID, S_PER_IK, S_SEQ_NB, MRN_PRE, MRN, PID, EU, LAST_NM, FIRST_NM, MIDDLE_NM, SSN, GENDER, MBR_TYPE, " & _ ...

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

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

different behaviour between simple sql and pl/sql-cursor?
Hello, I have a strange behaviour in our production database (9.2.0.7). In a method inside a package a cursor is used to iterate through the dataset. When the cursor arrives at a certain row, an ORA-01722 invalid number is thrown. If I use the same sql statment not in a cursor but "stand alone", everything works and all rows are displayed. Is there any known difference between opening a dataset via (1) select * from emp where deptno = 1 and (2) declare cursor curemp(ideptno in number) is select * from emp where dep...

Walkbacks in D6B2, Deleting a method, and Deleting a StaticText view...
Because other people have reported walkbacks in D6B2, I wanted to report two that I have recently encountered. Having encountered them, I was reminded that I have probably encountered similar walkbacks before. They are fairly rare, and I think I have not paid much attention to and quickly forget them because I was working intensely (this may be relevant) and was able to quickly work around them. This is the most recent Walkback, it happened when I deleted a method. It caused the system to freeze up, and I had to Ctrl-BREAK it. Somehow it seems that the Semaphore got stuck. ...

SQL Cursors
Hallo all, I have a problem to resolv asap...I hope that somebody can help me 1 Table called classage agent1 10000 value2 1 agent2 8000 value2 2 agent3 7500 value2 3 agent4 1000 value2 4 .... The table is order by rank desc. I need to update the value2 like this: agent1 10000 0 1 agent2 8000 2000 2 agent3 7500 500 3 agent4 1000 6500 4 Is it possible ? thanks in advance -- questo articolo e` stato inviato via web dal servizio gratuito http://www.new...

SQL delete
I am trying to delete records in one file based on criteria in a second file. I have the select running, and it select the proper records. I'm trying to figure the syntax of the delete. The select : select * from SLALLO, MBADREP where ADAKDT > 1040731 and ADUUQ1 > 0 and ADZ93N < ADUUQ1 and ADA3CD like '4%' and ADAENB=CONO and ADDCCD=ORTP and ADCVNB=ORDNO and ADFCNB=ITMSQ and ADHFCD < '50' order by ADCVNB The ADxxxx fields are from file MBADREP, the others from file SLALLO. I want to delete the SLALLO records that meet the where criteria. My fi...

sql delete
Hi, Should I be able to do the following delete in access 2002 - 'delete from table1 where table1.x in (select x from table2)' I get a syntax error. I have also tried 'delete * from...' Anyone help? cheers, Chris Try adding a semi-colon to the end of the select statement in parentheses, also you should include the field in the delete statement, it will delete the entire row anyway. Here is some sample SQL doing a similar thing only based on a query instead of a table(makes no difference). DELETE Inventory.PartNumber FROM Inventory WHERE (((Inventory.PartNumber) In (S...

what are cursors in sql???
I am new to sql and require some help on cursors? what are they and how and why are they used for??? it will be kind enough if anyone helps me in this regards.. regards vishal jain. vishal wrote: > I am new to sql and require some help on cursors? > what are they and how and why are they used for??? > > > it will be kind enough if anyone helps me in this regards.. > > > regards > vishal jain. Cursors are data structures that allow you to manipulate the results of a query one row at a time. Typically we only do that in client application code not in SQL. That&#...

delete from a view
Hi I have a view that is a union all of several select. If I try to delete a row, I get ora 01752 error. I'm sure that I can delete the row directly, there is no constraint of FK on it. Any idea ? Thanks Frederic On Wed, 28 Apr 2004 16:18:37 +0200, Frederic Houbie <fh@ionicsoft.comNOSPAAAAM> wrote: >Hi > >I have a view that is a union all of several select. If I try to delete a >row, I get ora 01752 error. > >I'm sure that I can delete the row directly, there is no constraint of FK >on it. > >Any idea ? > > >Thanks > >Frederic ...

deleting views
Is there an easy way to delete all views that might be in the work library, without typing their names ? I think of something similar to proc delete data = work._all_; run; that works for tables. Regards Rune ...

Views Are Deleted
I am runnnig Microsoft SQL Server 2000 and have a database that gets downloaded everymorning from an ftp site. This download contains the new records and any changes to existing records. However I am running into a problem in that I am forced to recreate the views over everytime we replace the new database with the old one. I've tried restoring the database as well as replacing it and am not sure what I am missing. I am new to SQL Server and any help would be greatly appreciated. rockio (rockio3@yahoo.com) writes: > I am runnnig Microsoft SQL Server 2000 and have a database that g...

How do you temporarily comment out Sql code in MS Access Sql view
Does anyone have an idea how to do this please ? On Sat, 18 Aug 2007 06:31:02 -0700, Art wrote: > Does anyone have an idea how to do this please ? You can't. There is no such thing as commenting in SQL. If you wish to remove some code just for testing, copy the existing code. Then delete from the SQL what you don't want. When you wish to return to the previous code, Paste it back. If there might be lot's of changes in the mean time, Copy the original code and paste it into a Notepad document. Then re-copy and paste back into the SQL when needed. -- Fred Please respond o...

DELETE FROM VIEW
HI all, when i try to delete data from view it gives me error. ERROR: Update access is not supported for file Even when i give delete permission for user,still i get above error. How can i delete data from VIEW Thanks. On Oct 26, 11:06=A0am, peesari.mah...@GMAIL.COM ("SUBSCRIBE SAS-L Joe H. Smith") wrote: > HI all, > > when i try to delete data from view it gives me error. > > ERROR: Update access is not supported for file > Even when i give delete permission for user,still i get above error. > > How can i delete data from VIEW > > Thanks. Hi Smit...

switch the way the query opens from a design view to an SQL view
is there an option to switch the way the query opens from a design view to an SQL view without entering to the query befoere? thread wrote: > is there an option to switch the way the query opens from a design > view to an SQL view without entering to the query befoere? -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 No. If you want the query to open in SQL view you'll have to save it while that view is on screen. If you want to just see the SQL string w/o opening the query you can do the following: In the Debug window (Ctrl-G) type something like the following: ? currentdb.query...

SQL delete
I have SQL delete that generate an error : Microsoft engine cannot find input tabel or query "false". Make sure it exists and name spelled correctly." Pls help Dim dbs As Database Set dbs = opendatabase("test.mdb") dbs.Execute "DELETE * FROM " _ & " test1 WHERE " & [name] = Me.Text0.Value & "" dbs.Close Regards, Daniel ...