f



SQL RPGLE cursor delete from view? -204

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
stanguru (27)
5/25/2004 8:54:16 PM
comp.sys.ibm.as400.misc 9195 articles. 2 followers. Post Follow

6 Replies
779 Views

Similar Articles

[PageSpeed] 53

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
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
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
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
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
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
stanguru (27)
5/27/2004 12:32:21 PM
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 this problem? Is there a better? Thanks in advance, Randy Harris just covered this with a colleage... select ename , job , hiredate , emp.deptno , sal , ttl_sals from emp , ( select deptno, sum(sal) as ttl_sals from emp group by deptno ) empsals where emp.deptno = empsals.deptno -- Mark C. Stock email mcstock -> enquery(dot)com www.enquery.com (888) 512-2048 "Randy Harris" <randy@SpamFree.com> wrote in message news:9nzob.6741$P%1.5602532@newssvr28.news.prodigy.com... > 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 be...

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 that defines the view? SQL Server 2000, up to date patches: SELECT dbo.tbl_5001_NumericAudit.aEventID, dbo.tbl_5001_NumericAudit.nParentEventID, dbo.tbl_5001_NumericAudit.nUserID, dbo.tbl_5001_NumericAudit.nColumnID, dbo.tbl_5001_NumericAudit.nKeyID, dbo.tbl_5001_NumericAudit.dChangeTime, CAST(dbo.tbl_5001_NumericAudit.vToValue AS nVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode, dbo.tbl_5001_NumericAudit.tChildEventText, CASE WHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END AS nDataType, dbo.tbl_5001_NumericAudit.nID, CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) AS vFromValue FROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOIN dbo.tblColumnMain WITH (NoLock) INNER JOIN -- -- Posters comment: here is the double ON...

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

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 but obviously temp. tables can't be used in views? > > Is there any special things I should be looking for? You could always incorporate the view definition directly into the query that composes the view, and take care to exclude columns or tables that are not relevant for the view. If you feel that temp tables makes the query more efficient, you could make the view a multi-statement function. Without knowledge about the views, and why you have been asked to rewrite it, it's difficult to give a very exact answer. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> I've been asked to re-write a sql view. The view itself contains several ca...

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-forum.com/. On 1/5/2011 9:05 AM, Arun Kumar wrote: > 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 ? This is really a Rails question and should go on the Rails list. That said, the console just prints the the results from .inspect on whatever was returned by the last statement, so you should ...

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

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

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 SID or time/timestamp via the V$%SQL% views (or any other views). Thanks, Doron doron_almog@msn.com (Doron) wrote in message news:<995517bc.0401131438.26d1ba68@posting.google.com>... > 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 SID or time/timestamp via...

Printing on AS400 via IBM AS400 Java Toolkit
Hello all, I'm working on a program that needs to send a print job to a printer connected to an AS/400. I've tried using the SpooledFileOutputStream and I've also tried using one of the SCS Writer classes. Neither solution is working. With the SpooledFileOutputStream I am at least able to create my print file, but when my program ends, the file is still not being printed. This program does not run on the AS400, it runs on a PC and connects to the AS400 via the toolkit classes. I know this part works as I am able to use the toolkit to access Data Queues, Files and Data Areas without a problem. Is there anyplace anyone can point me to see some examples of how to print a file using the AS400 Java Toolkit? Thanks in advance, Chuck Chuck, What printer data stream is used? or how was the printer data generated? I am wondering if the data stream being generated matches the data stream of the printer connected to the iSeries? What printer is attached to the iSeries and how is it configured? The iSeries info center has some Toolbox examples: Programming>Java>IBM Toolbox for Java>IBM Toolbox for Java Classes> Access Classes>Print>Creating... Dave Chuck wrote: > Hello all, > > I'm working on a program that needs to send a print job to a printer > connected to an AS/400. I've tried using the SpooledFileOutputStream > and I've also tried using one of the SCS Writer classes. Neither > solution is working. With the Sp...

#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, " & _ " EFF_DT, TERM_DT, CUR_STS, STATUS, [FOUND], ADDED_UN, ADDED_DT, EDITED_UN, EDITED_DT, PREPOP, DMC_TS, TS )" & _ " SELECT t_PID.WMS_FID, t_PID.S_PER_IK, t_PID.S_SEQ_NB, t_PID.MRN_PRE, t_PID.MRN, t_PID.PID, t_PID.EU, t_PID.LAST_NM, t_PID.FIRST_NM, " & _ " t_PID.MIDDLE_NM, t_PID.SSN, t_PID.GENDER, t_PID.MBR_TYPE, t_PID.EFF_DT, t_PID.TERM_DT, t_PID.CUR_STS, t_PID.STATUS, t_PID.FOUND, " & _ " t_PID.ADDED_UN, t_PID.ADDED_DT, " & Chr(34) & Environ("UserName") & Chr(34) & " AS Expr1, Now() AS Expr2, t_PID.PREPOP, t_PID.DMC_TS, t_PID.TS" & _ " FROM t_PID" & _ " WHERE (((t_PID.WMS_FID)=" & Me.txt_WMS_FID & "));" DoCmd.RunSQL (s) '*** PMP 09/20/07 - Added to de...

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.newsland.it/news segnala gli abusi ad abuse@newsland.it simisa wrote: > 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 ? > Yes, it is certainly possible. Without cursors. What version of sql server (this is very relevant for this question)? What are the column names and datatypes? What is the primary key of the table? You can best answer the latter ...

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 (Select Partnumber From Query2;))); -- Jeffrey R. Bailey "C L Humphreys" <clhumphreys@toofgib.moc> wrote in message news:bkn1o3$f6a$1@ucsnew1.ncl.ac.uk... > 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 > > > > > "Jeffrey R. Bailey" <mrwizard1208@yahoo.com> wrote in message news:ymEbb.25075$Od.989918@twister.tampabay.rr.com... > 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...

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 first attempt was (but failed syntax) - I tried several versions, but I'm missing something here. delete 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' Terence, SQL can't handle what you want to do. You can't do a join during a delete. You will need to make use of a subselect with an "in" or "exists" predicate. For example, say I want to delete all order detail lines for orders more than a year old. Problem is the order date only appears in the order header file. So I'd need to to the following subselect: delete from orderdtl where ordernbr in (select ordernbr from orderhdr where orderdate...

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 only to this newsgroup. I do not reply to personal e-mail Art wrote: > Does anyone have an idea how to do this please ? > Microsoft doesn't provide a way but Stephen Lebans provides a custom solution here: http://www.lebans.com/addsqlcomments.htm -- '-------------------------- ' John Mishefske ' UtterAccess Editor ' 2007 Microsoft Access MVP '-------------------------- ...

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's because it is usually much faster, easier, more concise and more maintainable to use "set based" SQL code - code that manipulates entire sets of data rather than one row at a time. If you are new to SQL then my advice is that you ignore cursors until you get a lot more expert with set based SQL code. Although there are certainly legitimate uses for them, cursors are too often badly misused by the inexperienced who don't know any better. Only when you have lots and lots of experience in "real" SQL will you have the sense to recognise when a cursor is an appropriate solution. That may not sound like a very helpful answer but if you consider that probably at least 99% of what the average SQL developer does can be done without a cursor you will realise that there are lots more important things to learn first. -- David Portas, SQL Server MVP Whenever possible ...

PL/SQL: Is a CURSOR always Dynamic SQL ?? (pls-00455)
Hi there. I have a Oracle 9i database here. When I try to open a cursor FOR a cursor variable, i get the error: <PLS-00455: cursor 'X_SMDACUR' cannot be used in dynamic SQL OPEN statement> Searching the net, I could only find examples where this error occurs in connection with Dynamic SQL, but I do not use dynamic sql, but a static defined cursor. Heres my testing code: (see comment PLS-00455) ---- CODE ---- -- Package Def, OK CREATE OR REPLACE PACKAGE TestPackage IS PROCEDURE Test_Procedure ( I_PAR1 IN VARCHAR2 DEFAULT NULL ); END TestPackage; -- Package Body: CREATE OR REPLACE PACKAGE BODY TestPackage IS /* A record type */ TYPE rec_TestRecord IS RECORD (Test1 VARCHAR2(100) ); /* A strong cursor var type returning the record */ TYPE curtype_Test IS REF CURSOR RETURN rec_TestRecord; /* A static(???) cursor */ CURSOR cu_testCursor RETURN rec_testRecord IS select 'This is a test' from dual ; /* A procedure trying to open the static cursor for the cursor var */ PROCEDURE Test_Procedure ( I_PAR1 IN VARCHAR2 DEFAULT NULL ) IS X_TestCursor curtype_Test; BEGIN /**** this is OK ****/ OPEN X_TestCursor FOR select 'hello' from dual; CLOSE X_TestCursor; /**** this results in PLS-00455 ! ****/ OPEN X_TestCursor FOR cu_testCursor; CLOSE X_TestCursor; END; END TestPackage; / ----- END CODE ---- .... ideas anyone? thanks!...

IBM AS400
Does anyone have some notes, documentation or helful pointers about navigating, command language and DB2 on an AS400 for a guy who has done HP3000, Windows 2k/2k3, MS SQL, HP9000, IBM ES9000/UNIX, SCO UNIX? Ideally a commands cross reference qaulivalent would be terrific - I.E. HP3000 to AS400. Thanks much. P.S. I'm taking a role in a shop that runs its ERP system on this platform. Jim. ____________________________________________________________________________________ Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.y...

delete on delete !
Hi, If I do another delete on an object which has been deleted earlier, then how is the system expected to behave ? Is it an unpredictable behavior ?? Thanks Sandeep Sandeep Grover wrote: > Hi, > > If I do another delete on an object which has been deleted earlier, then > how is > the system expected to behave ? > > Is it an unpredictable behavior ?? > Yes, unpredictable. Some will choke, some destructors SEGV, it goes on and on. Sandeep Grover wrote: > If I do another delete on an object which has been deleted earlier, then > how is the system expected to behave? > > Is it an unpredictable behavior ?? It's undefined, so in theory, yes; anything could happen. S > Sandeep Grover wrote: > > > If I do another delete on an object which has been deleted earlier, then > > how is the system expected to behave? > > > > Is it an unpredictable behavior ?? > > It's undefined, so in theory, yes; anything could happen. > > S Thanks ! So, if I have an array of pointers; more than one of the entries could point to the same chunk of memory (allocated using new). How do I ensure that I end up deleting that entry only once. I dont want to use reference-counting kind of thing in constructor. On Fri, 11 Jul 2003 16:53:31 -0700, Sandeep Grover <sandeep@magma-da.com> wrote: >[I]f I have an array of pointers; more than one of the entries could >point to the same chunk of memo...

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

Delete a view
Hi ! I am quite new to Oracle views, and now i have the following question: How do I delete a view, that I don't need anymore ?? I have created a view like: create view oletest as select * from debtable where debgroup='10' Now i dont need the view anymore, but how do i delete it ? I have tried "delete view oletest", but that didn't work. Can someone help me... Regards Ole In article <1124794645.505881.292600@f14g2000cwb.googlegroups.com>, says... > I have tried "delete view oletest", but that didn't work. > > Can someone help me... > It is DDL you need: SQL> DROP VIEW MYVIEW; -- jeremy ohahaahr@hotmail.com wrote: > I have tried "delete view oletest", but that didn't work. You need to keep the Oracle=AE Database SQL Reference guide close at hand. Been using SQL since the early 90's and I still consult the SQL Reference guide a lot (also Oracle SQL is different from Ingress SQL that is different from DB2 SQL that is different from SQL-Server SQL etc) On-line copies of Oracle documentation at http://tahiti.oracle.com -- Billy Great ! It works. Thanks a lot ! Regards, Ole ...

Sql View
I am doing a monthly report to export to an excel Doc from SQL 2000 using views. With my design it works great until it is the first of the month. As the report always checks from yesterday to the first of the current month. One view is below. The date statement checks between the first of month and current date -1 Can anyone help with with this? Mike CREATE VIEW dbo.GUTUR AS SELECT COUNT(*) AS GUTUR FROM dbo.Chev WHERE ([Deal Date] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND GETDATE() - 1) AND ([N/U] = 'U') AND (([Sales Type] = 'R') or ([Sales Type] = '3')) On Mon, 3 Jan 2005 13:22:59 -0800, Mike Lander wrote: (snip) Hi Mike, You already asked this question in microsoft.public.sqlserver.server and you already got some answers there. Please keep the discussion in one place, okay? Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) ...

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.querydefs("qrySales").sql It will display the sql string of the query "qrySales." HTH, -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) ** Respond only to this newsgroup. I DO NOT respond to emails ** -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBR/6RGoechKqOuFEgEQKrKgCg0A5+X5eOC5s+1kdKKk7+3V/gHwEAn06t fdtJrvLwUv8p5sX8EkhC3KRH =jnVY -----END PGP SIGNATURE----- ...

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 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. I'm not really sure that I understand what's going on here. The normal procedure would be to get datafiles of new and changed data, and merge this into the existing database. In such case, your views still be there. But it sounds that you actually a get a new complete database this morning and replace your old one each morning. Then of course the views are replaced as well. One possibility would be to create a second database ...

SQL from AS400
I'm using ADO to pull a recordset from AS400. Everything works fine except my SQL. This SQL was generated using Microsoft query in Excel, and it works fine, obviously, when using MSQuery. It does not work when I put it in code. The rest of my code is fine, and has been tested successfully with a different, simpler SQL. Please advise. mySQL = "SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG " & _ "FROM {oj ARWF.JGFTYT.CUST CUST LEFT OUTER JOIN ARWF.JGFTYT.CPRD CPRD ON CUST.CCUST# = CPRD.CPCST#} " & _ "WHERE (CPRD.CPCHG In ('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO','BE','BS')) AND (SUBSTR(CSPCL4,1,2) In ('CW','DF'," & _ "'MC','MK','CK','WE','LI','GA','GR','CO','BE','BS')) AND (CUST.CCYCLE In ('RW','RR','RC','TR','WR','SB','SC','ZB','1','2','10'," & _ "'11','12','13','14','15','16')) AND (CUST.CCMPNY In ('1','3','4','5','6'))" & ";" -- RMC,CPA Richard wrote: > I'm using ADO to pull a recordset from AS400. Everything works fine except my SQL. This SQL was generated using Microsof...

Web resources about - SQL RPGLE cursor delete from view? -204 - comp.sys.ibm.as400.misc

Cursor (computing) - Wikipedia, the free encyclopedia
In computing, a cursor is an indicator used to show the position on a computer monitor or other display device that will respond to input from ...

iPad Tip : the cursor - Flickr - Photo Sharing!
Like the iPhone, tap on the screen for the cursor to move to that spot. Fine-tune by holding your finger on the cursor, waiting for the magnifying ...

Curse of the cursor: US and Israel credited with shooting Iran down in Flame
Curse of the cursor: US and Israel credited with shooting Iran down in Flame

Setting cursor on address line
Community Platform of DuckDuckGo - Translate DuckDuckGo, use our XMPP or make your own user page!

This Clever Amnesty Campaign Imprisons Your Cursor Within a Banner Ad - Adweek
Sometimes an ad idea just doesn't get the reach it deserves, and this is certainly one of those times.

Video: Kitten meets cursor
Your moment of zen, courtesy of AMERICAblog reader Marianne and her kitten.

iPad Keyboard Concept Highlights Cursor Placement And Text Selection
This keyboard concept could have Cupertinos engineers reconsidering their plans for iOS 6.

MacBook, MacBook Pro: Cursor reacts unexpectedly
Does the cursor on your Mac notebook move while you're typing? Do you notice that sometimes the cursor arrow doesn't appear when you use the ...

Apple awarded dynamic 'hybrid cursor' utility patent
The U.S. Patent and Trademark Office on Tuesday granted Apple a utility patent for the dynamically changing mouse cursor seen in previous Mac ...

Google, Aalto University, And Cursor To Focus on South-Eastern Finland
... create quite the startup scene in the suburbs of Helsinki, and now the two organizations are working with financial support of Google and Cursor ...

Resources last updated: 3/24/2016 3:19:09 PM