from wrkqry to qmqry
Last Post 18 Nov 2005 09:20 PM by noRulez. 15 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
noRulez
Basic Member
Basic Member
Posts:136

--
17 Nov 2005 01:43 PM
I've been working in wrkqry for a while now, but now I would like to move on to qmqry or something that will let me do manual sql because I want to do an update query. Could anyone explain the steps of going into qmqry(not sure even how to get there) and write a query that updates file GMPRDFIL/G.FQUEUE's date field(QSDATE) to 111705(6 DIGIT NUMERIC) where QFLDC = 'F092/01'. Just a start would be nice. Or is there some other way that would make this easier?
noRulez
Basic Member
Basic Member
Posts:136

--
17 Nov 2005 04:11 PM Accepted Answer
ok, I got to a screen using strsql that is titled "Enter SQL Statements" and am now running into a problem. Apparently, the file being named G.FQUEUE is a problem because of the period because it's not allowing me to use that file. Is there any way around this? This is what I get when I try to use that: SELECT * FROM GMPRDFIL/G.FQUEUE Token . was not valid. Valid tokens: FOR WITH FETCH ORDER UNION OPTI
Lynne Noll
Senior Member
Senior Member
Posts:6567

--
17 Nov 2005 04:33 PM Accepted Answer
1) STRSQL is only remotely connected with QMQRY. They both access the database engine, and you can get SQL working in STRSQL and then copy it out to the source member and make a QMQRY out of it. But there is a different family of commands for QMQRY. 2) There are two naming conventions with SQL on the ISeries. One is based on how people native to the ISeries are used to referring to libraries and files (collections and tables). This looks like this: mylib/myfile mycollection/mytable The other is based on DB2 and looks like this: mylib.myfile mycollection.mytable You don't mix them. Which you use is set in STRSQL by hitting F13 (change Session attributes), taking option 1, and setting naming convention to *SYS (the slash) or *SQL (the dot.) Other ways to access SQL on the Iseries offer you a choice somewhere to set this up; it varies with the application and is generally retained until changed. The naming convention can also affect how data is located when the table is unqualified; system naming means use the library list. STRQRY gives you the menus for the Qry/400 options plus assess to QMQRY options like WRKQMQRY and WRKQMFORM. These versions let you suck form and SQL source out of the Qry/400s or QMQRYs and make new QMQRYs and QMFORMS out of the source. However, there is another set of commands for QMQRY that let you work directly with the queries and QMQRY profiles: try STRQM. This lets you either make QMQRYs by typing SQL or using a prompted mode such as WRKQRY provides. (Users can be restricted to the prompted mode or it can be optional.) I find making the query from sql easier than using the prompted mode, but the prompted mode is much better for the report forms. If you send me a good email address as a personal message, I can send you a more detailed write up on QMQRY that I wrote for people in my own shop.
noRulez
Basic Member
Basic Member
Posts:136

--
17 Nov 2005 04:38 PM Accepted Answer
My file name is actually g.fqueue in the library gmprdfil. The way I got it to work was to do: gmprdfil/"g.fqueue" Now I have another problem in that it is an old system 36 file with one field(f00001) and I'm supposed to update only the date portion. This could prove to be quite difficult. My email address is noRulez[nospam]@gmail.com without the [nospam] of course. I gather that you can't do an update from prompted mode, am I correct in this?
Tommy Holden
Senior Member
Senior Member
Posts:2833
Avatar

--
17 Nov 2005 05:16 PM Accepted Answer
update mylib/myfile set f0001=concat(substr(f00001,1,10),concat('11172005',substr(f00001,19,100))) basically on the first concat the substr should start in position 1 & go for n positions (until you get to the date part). The second concat the first part is your new date with the second part of the concat being the rest of the record. HTH
noRulez
Basic Member
Basic Member
Posts:136

--
17 Nov 2005 05:34 PM Accepted Answer
That should work well Tom. Thanks. Hey, where can I look to see exactly how long the field is so I make sure to concatenate the whole thing?
Tommy Holden
Senior Member
Senior Member
Posts:2833
Avatar

--
17 Nov 2005 05:43 PM Accepted Answer
or if it is only one field (some later versions could have more than one field k00001, f00002,etc.) then the record length would be the same as the field length. But in general I use DSPFFD for to lookup field definitions...or a futility I wrote for such stuff lol...
noRulez
Basic Member
Basic Member
Posts:136

--
17 Nov 2005 06:19 PM Accepted Answer
Do you see anything wrong w/ this? this is the error I got: Argument 3 of substringing function not valid. update dmacklib/fqueuecpy set f00001=concat(substr(f00001,1,24),concat('11172005' ,substr(f00001,33,128))) WHERE substr(f00001,15,7) = 'F092/01'
Tommy Holden
Senior Member
Senior Member
Posts:2833
Avatar

--
17 Nov 2005 06:31 PM Accepted Answer
I had one of the concats out of place...
update dmacklib/fqueuecpy set 
f00001=concat(concat(substr(f00001,1,24),'11172005 '), 
substr(f00001,33,128)) 
WHERE substr(f00001,15,7) = 'F092/01'
That should get it...
noRulez
Basic Member
Basic Member
Posts:136

--
17 Nov 2005 07:23 PM Accepted Answer
hmmm...I get the same error. If I take out the where clause, it works as far as syntax goes. ...actually, no it doesn't. sorry. edit: sorry about that. Thanks so much for the help. I was using 128 when that was the field length and I should've been subtracting the other lengths. whoops.
G. Peter Posthumus
Advanced Member
Advanced Member
Posts:250

--
17 Nov 2005 08:28 PM Accepted Answer
Rather than use concat() funtion. f00001 = substr( f00001,1,24 ) CONCAT '11172005 ' CONCAT substr( f00001,33 ) Note too that the length in the last substr() does not have to be supplied. Default is to go at the start postion to the end of the field. GPDP
noRulez
Basic Member
Basic Member
Posts:136

--
18 Nov 2005 04:45 PM Accepted Answer
Is there a way I can copy the original file with it's data dictionary associations and file definition associations intact? Every time I copy the file I'm using for testing, it loses this information.
Lynne Noll
Senior Member
Senior Member
Posts:6567

--
18 Nov 2005 05:02 PM Accepted Answer
Is there a way I can copy the original file with it's data dictionary associations and file definition associations intact? Every time I copy the file I'm using for testing, it loses this information.
In general, if you want to copy a file with all its attributes, try a save and restore (to another library), or CRTDUPOBJ (this can copy the file definition without the data or without the data.) I haven't checked if CREATE TABLE LIKE preserves all the DDS type attributes. Then you can use SQL insert or CPYF to populate the second file with the selected data.
noRulez
Basic Member
Basic Member
Posts:136

--
18 Nov 2005 05:08 PM Accepted Answer
It created the file with the fields and record formats and everything but said it could not copy the data. Is this because it is an old system 36 file? This is the error I get: Cannot copy data to member M041004 file G.FQUEUECP EDIT: I was able to use cpyfile with *nochk to get it in there. Thanks again!
SEAN MCGOVERN
Veteran Member
Veteran Member
Posts:523

--
18 Nov 2005 08:21 PM Accepted Answer
If you use CRTDUPOBJ to copy the file, check that the original file doesn't have a trigger program attached otherwise I believe this will be copied also. Use DSPFD to check.
noRulez
Basic Member
Basic Member
Posts:136

--
18 Nov 2005 09:20 PM Accepted Answer
It says # of triggers is 0 so I guess I'm fine. Thanks for the heads up. I'll have to look up what a trigger program is. :) As you can tell I'm new to this AS/400 stuff. It's quite a learning experience and I thank you all for your help.
You are not authorized to post a reply.

Acceptable Use Policy