f



AS400 Queries with out a existing query

Wondered if you could jog my memory a bit.

Its been years since I've worked on an AS400.

I recall there is a way to issue a QUERY command to have it list the
data with in a file with out creating an entire query.

I thought it was a QUERY *N .... but that doesn't seem to work.

Thanks for any help.
0
janssen_ (2)
8/26/2004 11:09:23 PM
comp.sys.ibm.as400.misc 9219 articles. 3 followers. Post Follow

2 Replies
832 Views

Similar Articles

[PageSpeed] 35

Try:
runqry *n libname/filename

A Switzer wrote:

> Wondered if you could jog my memory a bit.
> 
> Its been years since I've worked on an AS400.
> 
> I recall there is a way to issue a QUERY command to have it list the
> data with in a file with out creating an entire query.
> 
> I thought it was a QUERY *N .... but that doesn't seem to work.
> 
> Thanks for any help.
0
warpman999 (65)
8/26/2004 11:19:25 PM
Thats the one,  Thank You!


janssen_@hotmail.com (A Switzer) wrote in message news:<65116016.0408261509.497cfbb8@posting.google.com>...
> Wondered if you could jog my memory a bit.
> 
> Its been years since I've worked on an AS400.
> 
> I recall there is a way to issue a QUERY command to have it list the
> data with in a file with out creating an entire query.
> 
> I thought it was a QUERY *N .... but that doesn't seem to work.
> 
> Thanks for any help.
0
janssen_ (2)
8/31/2004 7:19:40 PM
Reply:

Similar Artilces:

Query Query
I'm new to Access (and databases in general) and have the following problem.... TABLE_A is basically just a list of events occurring on a system. It has fields; "EVENT_ID" (auto number), "EVENT_TIME", "EVENT_DESCRIPTION". TABLE_B is a list of activities that are undertaken in reaction to the events of TABLE_A. It has fields; "ACTIVITY_ID" (auto number) "ACTIVITY_TIME", "ACTIVITY_DESCRIPTION", "SOURCE_EVENT". "EVENT_ID" in TABLE_A is linked with "SOURCE_EVENT" in TABLE_B, allowing me to build a query that shows me all the activities relating to a certain event. So far, so good. The problem is that an event in TABLE_A can be a "child" of another event in TABLE_A. Each event can only have one parent, however it's parent may also have a parent, ad infinitum. Also, each event may have several children. Ideally, I would like to add one extra field to TABLE_A such that if an event has a parent, it can be identified. We then have fields; "EVENT_ID" (auto number), "EVENT_TIME", "EVENT_DESCRIPTION", "EVENT_PARENT". NOW FOR THE QUESTION!!!!!! If I wanted to show all the activities relating to an event and its children, I could write some VBA code to mash through TABLE_A, determine all the descendents of the event (if any) and then make a query. But, is there a way to do this using the normal MS Access query GUI? I'm happy to re-...

AS400 Printing queries
Hi, I am having trouble trying to figure out why when I print query spool files, they only print landscape not portrait. There isn't anywhere in the query definition that allows me to print landscape or portrait. Do you know of a way I can do this? Thanks in advance!! Shannon Set the number of columns to 80 in the "Select output type and output form" option, second screen Form size - Width. - Bob Comer "Shannon" <stilley@nbnet.nb.ca> wrote in message news:NLfPb.68613$IF6.1663775@ursa-nb00s0.nbnet.nb.ca... > Hi, > > I am having trouble trying to figure out why when I print query spool files, > they only print landscape not portrait. There isn't anywhere in the query > definition that allows me to print landscape or portrait. Do you know of a > way I can do this? > > Thanks in advance!! > > Shannon > > > > > > I forgot to mention that it only prints landscape on laser printers. It prints as it should on our wide impact printer. Shannon "Shannon" <stilley@nbnet.nb.ca> wrote in message news:NLfPb.68613$IF6.1663775@ursa-nb00s0.nbnet.nb.ca... > Hi, > > I am having trouble trying to figure out why when I print query spool files, > they only print landscape not portrait. There isn't anywhere in the query > definition that allows me to print landscape or portrait. Do you know of a > way I can do this? > > Thanks in advance!! > > ...

Query that blocks AS400
Hi! I have a VB program , that creates a query in a dynamic way, depending on the users selections. So, one of the query that it creates is the below query. It consists in 6 unions, the first 3 are more simple and returns one registers each one, and the next 3 returns some more registers. If I execute the query in this order the process, begins to catch CPU, since it block the AS400. If I change the order and I put the last 3 statment before the first 3, then the query runs correctly. Could anybody explain me what happen? We have the last version of AS400, and it happends also if I execute the query with the Operations Navigator. Thanks in advance and sorry by my english. This is the query: Select 2 As Tipo, 2 As Semana, 0 As SIN_A, 0 As SIN_B, 0 As SIN_C, 0 As COBRADO, 0 As PREVISTO, 0 As NO_PREVISTO From (Select Count(*) From LIB.TB_DETAIL) AS UNICOREG Union All Select 2 As Tipo, 3 As Semana, 0 As SIN_A, 0 As SIN_B, 0 As SIN_C, 0 As COBRADO, 0 As PREVISTO, 0 As NO_PREVISTO From (Select Count(*) From LIB.TB_DETAIL) AS UNICOREG Union All Select 2 As Tipo, 5 As Semana, 0 As SIN_A, 0 As SIN_B, 0 As SIN_C, 0 As COBRADO, 0 As PREVISTO, 0 As NO_PREVISTO From (Select Count(*) From LIB.TB_DETAIL) AS UNICOREG Union All Select 1 As Tipo, 1 As Semana, Sum(Case When C.Endoso<>'S' And C.EndDis<>'S' And D.ForCob In (1, 2, 7, 18, 21) Then Princi Else 0 End) As SIN_A, Sum(Case When C.Endoso<>'S' And C.EndDis<>'S&#...

Query AS400 with ASP
Hello I'm trying a connection to AS400 with ASP pages, my problem is when I use a query like this: CommandText = "SELECT btforn, pruser FROM carg.bolef WHERE btforn=16046;" The query retuns to me all the records an not only the records with btforn=16046. Can you help me plese??? Thanks F.V. On May 24, 11:28 am, aldobe...@gmail.com wrote: > Hello > I'm trying a connection to AS400 with ASP pages, my problem is when I > use a query like this: > CommandText = "SELECT btforn, pruser FROM carg.bolef WHERE > btforn=16046;" > > The query retuns to me all the records an not only the records with > btforn=16046. > > Can you help me plese??? > Thanks > > F.V. I notice that you have an ";" inside the quotes rather than outside which looks a little strange. Otherwise the most likely cause is that somehow you are losing the WHERE clause when the query is being submitted to OS/400. Make sure you don't have any control characters in your CommandText string. If this is fine then you can use iSeries OpsNav to start a database performance monitor and monitor the SQL requests it is receiving. John Garrould ...

Query about ADO Query
I have the following code in my application: count_of_views = cat.Views.Count If (count_of_views > 0) Then For view_pointer = 0 To count_of_views - 1 If (cat.Views.Item(view_pointer).Name = "New_New_Form_31240_Subform_Unselect_Query_odd") Then cat.Views.Delete "New_New_Form_31240_Subform_Unselect_Query_odd" view_pointer = count_of_views End If Next view_pointer End If cat.Views.Refresh cat.Views.Append "New_New_Form_31240_Subform_Unselect_Query_odd", cmd ----------------(Note: Indented in this post for easy reading) I get an error on the cat.Views.Append line saying "New_New_Form_31240_Subform_Unselect_Query_odd" already exists. But when I follow the code through the debugger I find that the cat.Views.Delete line is never executed. Further, when I examine in the Local Window, I find that although there are 61 entries in the cat.Views NONE of them bear the name "New_New_Form_31240_Subform_Unselect_Query_odd" nevertheless, I get that error. Any explanations anyone can offer? Thanks in Advance Cheers! Uttam ...

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

Query Based on Query
Hi there, I have a query (QueryB) that is based on the results of another query (QueryA). When I open QueryB itself, it displays the exact records that I want. I have a report based on QueryB, and when I open it directly, it displays what I want. When I open the report from a command button on a form using this code: DoCmd.OpenReport "rpt_QueryB", acViewPreview, "QueryB" ...I get prompted for parameters. Since I can open the query and the report itself without getting prompted for parameters, why do I get prompted for them when I use docmd.openreport? I don't want it to do this. Thanks! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Try just this DoCmd.OpenReport "rpt_QueryB", acViewPreview -- Wayne Morgan Microsoft Access MVP "Steven Stewart" <o6t6i@unb.ca> wrote in message news:3fb641f8$0$198$75868355@news.frii.net... > Hi there, > > I have a query (QueryB) that is based on the results of another query > (QueryA). > > When I open QueryB itself, it displays the exact records that I want. I > have a report based on QueryB, and when I open it directly, it displays > what I want. When I open the report from a command button on a form > using this code: > > DoCmd.OpenReport "rpt_QueryB", acViewPreview, "QueryB" > > ..I get prompted for parameters. > > Since I can open the que...

AS400 to AS400 connection
Hi. What`s the rocommended method of copyting data beetween two AS400 (both on V5R1) ? Regards, Tom tom wrote: > Hi. > What`s the rocommended method of copyting data beetween two AS400 (both on > V5R1) ? Probably FTP. "tom" <tomeks@comp.pl> wrote in message news:c8c9u5$fb4$1@nemesis.news.tpi.pl... > Hi. > What`s the rocommended method of copyting data beetween two AS400 (both on > V5R1) ? > > Regards, > Tom > > It depends how often you are doing the copying & how much there is. If you want say one record but every 3 seconds then you can either store them up for a few hours days or weeks & use FTP, snads or a tape. Or you can do it realtime & use DDMF to zap each record to the other machine as its written. I actually save the data I want sending this way into a file which is read & deleted by a daemon style of job so that if comms or the other machine fails only the daemon is affected. FTP has its upside in that the target is easily resettable for say a unix machine but SNADS has an easy to use & understand message sent to the sender & recipient to verify arrival of the data. -- Jonathan Bailey. You could install the No Charge SW "Object Connect" from the OS CDs. Once setup you can issue cmds line SAVRSTLIB and SAVRSTOBJ. Give these cmds the LIB or OBJ list and the target system. It then saves/transfers/restores to the target system all with just the 1 SAVRSTxxx cmd. tom wro...

Slow Query Query
In a survey database I have a query "qcboCriteria" that uses another query in its criteria: Select SurveyTakenID From tblSurveyTaken Where SurveyTakenID Not In (Select ID From qryPassCount) qryPassCount itself references another query and qcboCriteria takes minutes to run, but if I put the SQL code from qryPassCount into the criteria for qcboCriteria then it takes just a few seconds: Select SurveyTakenID From tblSurveyTaken Where SurveyTakenID Not In (SELECT SurveyTakenID FROM qryAnswers WHERE (((Answer)=-1 Or (Answer)=1) AND ((Include)=True)) GROUP BY SurveyTakenID HAVING (((Count(ID))=(select count ([Include]) from qryQuestions where [include] = -1))) WITH OWNERACCESS OPTION;) Is the slowness of the first example a function of using too many stored queries? For info, the purpose of the query is to determine which surveys have a "Yes" answer count that is less than the total number of questions in the Questions lookup table (and so constitutes a failure). There's probably a more elegant way to achieve this too but it's the best I could come up with. I'm more interested in why one method is slow at this point. Thanks. Keith. On Oct 29, 6:00 am, "Keith Wilby" <h...@there.com> wrote: > In a survey database I have a query "qcboCriteria" that uses another query > in its criteria: > > Select SurveyTakenID From tblSurveyTaken > Where SurveyTakenID Not In > (Select ID From qryPassCount) ...

Query of Query PF
Suppose a database file (PF) is created using WRKQRY. Is there any way to find out the query name of the database file? On Dec 11, 12:26 pm, Tapan <tapankn...@gmail.com> wrote: > Suppose a database file (PF) is created using WRKQRY. Is there any way > to find out the query name of the database file? Tapan, You need to mention the file name when you write a new WRKQRY in AS400. The option SELECT OUTPUT TYPE AND OUTPUT FORM in define query screen. Here you can select 3 for database file and can mention the file name and the library you wanted to store the file. Hope this helps. Regards, Gai. ...

Running Totals in AS400 Query
Hi, Is there a way of creating a running total field in AS400 query Lets say I have 2 fields 1. Date of Transaction 2. Amount. eg -2000 +50, -55 Date Amount Running Total 01/01/07 200 200 02/01/07 -50 150 I want to create the running total field using the Amount field programmatically. I plan to use the define report fields screens. Any ideas or help appreciated, Many Thanks, Ant AS400 Query Beginner excel-ant wrote: > Hi, > > Is there a way of creating a running total field in AS400 query Nope. . IIRC, the Query/38 feature offered that function. That software comes with OPTION(06) of i5/OS. If that option is installed, CALL QCL, then issue DSNQRYAPP Beyond that, I recall almost nothing about it except that F1=Exit. Thus if your PF1 key is mapped to only Help, be sure to first remap your keyboard to allow exit/save of the designed query report/application. If you can create some SQL VIEW that gets the data in rows that the Query/400 reporting can present, then you could replace the query of the file with a report of the VIEW -- with or without summary processing in the query definition, depending on what the VIEW achieves for you. I do not want my brain to hurt, so I have not given this any thought for the given example. Regards, Chuck -- All comments provided "as is" with no warranties of any kind whatsoever excel-ant wrote: > Is there a way of creating a running total field ...

Help with an SQL query (opposite to a union query
Hi all, I have a table (tblCodes) that has two fields: 'ID' and 'Code'. A small portion of the table is as follows: |-------------| | tblCodes | |-------------| | ID | Code | |------|------| | A-01 | TBN | | A-01 | SPN | | A-01 | EWK | | A-01 | EMX | | A-02 | QIM | | A-02 | TBN | | A-02 | EWK | | A-03 | TBN | | A-04 | DOY | | A-04 | CAV | | A-04 | ICP | | A-04 | CYG | | A-04 | MER | | A-05 | ICP | |------|------| The table can grow very large. ID numbers in the thousands each with up to 400 different codes (but usually a max of 20 codes per ID, with an average of between 2-5 codes per ID). Is there a way to build a SQL statement that returns the codes that intersect 2 or more IDs? For example, 'A-01' Intersects 'A-02' = TBN, EWK or 'A-01' Intersects 'A-02' Intersects 'A-03' = TBN I've been trying to figure a way for a while now with no luck. I'm at a point were I might just loop throgh the table and pass duplicates to an array, but I suspect it will be very slow on large tables. Any ideas or pointers would be very helpful. Thank you. Pete bradleyp@inspection.gc.ca wrote: > Hi all, > > I have a table (tblCodes) that has two fields: 'ID' and 'Code'. > A small portion of the table is as follows: > > |-------------| > | tblCodes | > |-------------| > | ID | Code | > |------|------| > | A-01 | TBN | > | A-01 | SPN | > | A-01 ...

SQL queries on AS400 database
Hi, Currently I use a program called DataSelect for running queries on my AS400 database. From Clarion 5.5. I open a DDE channel and have the outcom of the query running into a dBase IV file, from where I can access the data in my Clarion applications. I am sure there must be a way to run SQL based queries directly on the AS400, using the ODBC connection I have setup with the database, but I don't know how to set this up and how to ensure that the data ends in a table (TopSpeed file). Anybody who could help me? Thanks for your reply, Gerard Hi, > I am sure there must be a way to run SQL based queries directly on the > AS400, using the ODBC > connection I have setup with the database, but I don't know how to set this > up and how to > ensure that the data ends in a table (TopSpeed file). Import the AS400 filedefinitions into your dictionary using the ODBC filedriver. Then you can either access these files in your application as you would access topspeed files (kind of), or you can copy the contents to a topspeedfile (e/g using a Process type procedure) HTH -- Arie Rens (RADventure B.V.) Maarssen, the Netherlands http://www.radventure.nl SoftVelocity Distributor for the Benelux try winsql lite. it free. it allow you to browse tru any database via odbc. from winsql then you can decide which table to import. "Arie Rens (RADventure B.V.)" <reader8@radventure.nl> wrote in message news:VA.00000107.43058c20@radventure.nl... &...

Query of a Query DataSheet
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and can not get that query to work. I thought I might be able to accomplish the results in two steps by using two queries. If this is possible how can I do it? Thank you, Stan Hanna On Sat, 29 Sep 2007 09:39:17 -0700, Stan wrote: > I am using MS Office Access 2003 (11.5614). My basic question is can > I run a query of a query datasheet. I want to use more that one > criteria and can not get that query to work. I thought I might be > able to accomplish the results in two steps by using two queries. If > this is possible how can I do it? > > Thank you, > > Stan Hanna You haven't given sufficient information for more than generic help, but yes, you can use one query as the record source for another query. You can also use a form in which to enter the parameters. Then reference the relevant form controls in the query: forms!FormName!ControlName As long as the form is open, any query that refers to the form and it's control will run with the correct criteria. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Stan wrote: > I am using MS Office Access 2003 (11.5614). My basic question is can > I run a query of a query datasheet. I want to use more that one > criteria and can not get that query to work. I thought I might be > able to accompli...

xpath query query
Hi Folks, I wonder if anyone can help me with the following (perhaps trivial) problem: <xml> <node name="a" type="a"/> <node name="b" type=""/> <node name="c"/> <node name="d" type="b"/> </xml> we're using a command line xpath script to pluck values from configuration files, e.g. to get the list of node names: xpath test.xml '/xml/node/@name' Returns the list of names in the file, easy. a b c d Because of the way our other command line tools work, we're relying on the result being a return separated list. We can get the list of types like this: xpath test.xml '/xml/node/@type' which returns a b But passes over the node with no type attribute. Is there an xpath query which would give the result: a b So where no type attribute existed, an empty string is returned? thanks in advance, David Gordon David Gordon <nospam@nospam.com> writes: ..... > <xml> > <node name="a" type="a"/> > <node name="b" type=""/> > <node name="c"/> > <node name="d" type="b"/> > </xml> > > xpath test.xml '/xml/node/@type' > > which returns > > a > > b > > But passes over the node with no type attribute. Is there an xpath query > which would give the result: ...

SQL QUERY IN QUERY
Hi, I'm looking for a way to use a query inside another query in the following way: First I make my first query(Q1) and I save it as Q1. Then I want to reuse the result of that query inside query 2(Q2) It would have to look like: SELECT * FROM SOMETABLE, Q2 I tried different things but it did not work, saving the data first and then running does work but data is not always up to date and with frequent changes it is not efficient to run the query first, save it and run again. So if anyone has an idea. ps: I know about the procedures but I'd rather not use that. Parentheses are your friends! Google on "SQL SUBQUERY". -Philip michael.goossens@gmail.com wrote: > Hi, > > I'm looking for a way to use a query inside another query in the > following way: > > First I make my first query(Q1) and I save it as Q1. > Then I want to reuse the result of that query inside query 2(Q2) > > It would have to look like: > > SELECT * > FROM SOMETABLE, > Q2 > > I tried different things but it did not work, saving the data first > and then running does work but data is not always up to date and with > frequent changes it is not efficient to run the query first, save it > and run again. > > So if anyone has an idea. > > ps: I know about the procedures but I'd rather not use that. "Philip Wright" <p...

Base a query on a query
I have a query qry2 which is based on query qry1. Both qry1 and qry2 are defined in Access. I would like to use parameters for both queries. I indicate below a simple verson of the queries as they appear in Access: qry1: SELECT * FROM tblX WHERE (tblX.[fldName]) Is Not Null); qry2: INSERT INTO tblY ( fld1, fld2, fld3) SELECT qry1.[fld1], qry1.[fld2], qry1.[fld3] FROM qry1;" Is there a simple way to incorporate qry1 into qry2 so that I can use DoCmd.RunSQL to run qry2? Thanks, John Per JohnP: >I have a query qry2 which is based on query qry1. Both qry1 and qry2 >are defined in Access. I would like to use parameters for both >queries. I indicate below a simple verson of the queries as they >appear in Access: >qry1: >SELECT * FROM tblX >WHERE (tblX.[fldName]) Is Not Null); > >qry2: >INSERT INTO tblY ( fld1, fld2, fld3) >SELECT qry1.[fld1], qry1.[fld2], qry1.[fld3] FROM qry1;" > >Is there a simple way to incorporate qry1 into qry2 so that I can use >DoCmd.RunSQL to run qry2? Yes, by using the UI's query builder screens, specifying the parameters via Query | Parameters and saving the resulting queries. If you run Qry2 "by hand", it will then prompt for the parms. If you run qry2 in VBA, you will: ----------------------------------- Dim myRS as DAO.Recordset Dim myQury As DAO.QueryDef Set myQuery = CurrentDB.QueryDefs("qry2" With myQuery .Parameters("theParm1") = Whatever .Parame...

AS400 Query Combining Numeric Fields
Hi! Does anyone know how to combine numeric fields in AS 400 query? I have 3 fields that are numeric FIELD1 FIELD2 FIELD3... would like to combine them to be FIELD1/FIELD2/FIELD3 - if FIELD1 is 1 and FIELD2 is 8 and FIELD3 is 7 = then it should combine them to 1/8/7 I have tried to play around with define views but can not figure it out. Please help if anyone has any advice. Thanks in advance. Create a new field with: Digits(Fld1) !! '/' !! Digits(Fld2) !! '/' !! Digits(Fld3) Birgitta That example is a bit too simplistic to infer the intentions. Does the data type of the /result field/ need to be character data type? Zero prefix disallowed? What are the attributes of the /numeric/ fields? create view qtemp.combined as (select rtrim(char(field1)) concat rtrim(char(field2)) concat rtrim(char(field3)) as F123 from thelib.thefile ) Regards, Chuck lcollinsfl@hotmail.com wrote: > Does anyone know how to combine numeric fields in AS 400 query? I > have 3 fields that are numeric FIELD1 FIELD2 FIELD3... would like to > combine them to be FIELD1/FIELD2/FIELD3 - > > if FIELD1 is 1 and FIELD2 is 8 and FIELD3 is 7 = then it should > combine them to 1/8/7 > > I have tried to play around with define views but can not figure it > out. ...

DATE CALCULATIONS USING AS400 QUERY
I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP?? Current(Date) - 30 Days Birgitta On 10 Aug., 17:33, JIMBO <JST...@SHENANDOAHFURNITURE.COM> wrote: > I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I > CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW > TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP?? On Fri, 10 Aug 2007 08:33:40 -0700, JIMBO wrote: > I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I > CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW > TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP?? It's not that hard with SQL (*QMQRY). SELECT DATE(DAYS(CURDATE()) - 30) will hand back the date 30 days prior. -- 6. I will not gloat over my enemies' predicament before killing them. --Peter Anspach's list of things to do as an Evil Overlord On Aug 10, 1:04 pm, "Hau...@sss-software.de" <Hau...@sss-software.de> wrote: > Current(Date) - 30 Days > > Birgitta > > On 10 Aug., 17:33, JIMBO <JST...@SHENANDOAHFURNITURE.COM> wrote: > > > > > I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I > > CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW > > TO GET CU...

Execute Query Stored on AS400 from Excel VBA
I connect to our AS400 using ODBC and pass SQL from Excel 2003 VBA. Due to performance issues, some queries are stored on the AS400, and executed manually. They are in a library named "PRDJ73QRY". Can these queries be executed from within Excel 2003 VBA (so that a user does not have to manually execute the query), and if so, could I see a sample of the code? We use "IBM iSeries Access for Windows". With later releases of OS/400, can you still use Public cnAS400 As ADODB.Connection Set cnAS400 = New ADODB.Connection cnAS400.Open "Provider=IBMDA400; data source=system_name;", "user_ID;", "password;" cnAS400.Execute "{{QSYS/DSPFD FILE(*ALLUSR/QS36SRC) TYPE(*RCDFMT) " & _ "OUTPUT(*OUTFILE) OUTFILE(QS36F/S36LIBS)}}", 0, adCmdText IBMDA used to be part of the Express client so should be worth trying. Best of luck Peter Kinsman "White Horse" <michael_13143@msn.com> wrote in message news:1114546735.788216.254130@f14g2000cwb.googlegroups.com... > I connect to our AS400 using ODBC and pass SQL from Excel 2003 VBA. > > Due to performance issues, some queries are stored on the AS400, and > executed manually. They are in a library named "PRDJ73QRY". > > Can these queries be executed from within Excel 2003 VBA (so that a > user does not have to manually execute the query), and if so, could I > see a sample of the code? > >...

Excel>MS Query>AS400
Setup: Windows XP, Excel 2003, OS400 v5r2 I am trying to connect to the as400 using MS Excel & MSQuery but keep getting the following error when I try to connect. SQL0204 - systables in qsys2 type *file not found I was able to do this on another AS400 in the past (don't recall OS400 version). I have Client Access installed so I think I have the approporiate ODBC drivers already installed. I read somewhere that the problem might be a missing or outdated jt400.jar file. If this is the case, where do I get it & how/where do I install it? Can anyone suggest possible solutions to this problem? Thanks, Jim The jt400.jar is the AS/400 Java Toolbox. If you are trying to connect using JDBC, this could be the culprit. You can find find the jar on the IBM website. If you are trying to connect another way (ODBC for eample), then this Jar probably isn't relevant. Are you sure you have authority to see the qsys2 library and systables file? walker, Thanks for the reply. I am not explicitly trying to access qsys2. I am guessing MS Query is trying to access this library/file to return a list of tables for me to query. I tried changing my user type to SECOFR and I got the same results. I am not sure why it is looking at qsys2 since my default library is qgpl. Thanks, Jim It's looking in qsys because it thinks you are looking for a table called systables (which is in the qsys2 library). Are you saying you are trying to get the AS/400 to list all of the ta...

AS400 - RPG - UDB - DB2 - ODBC
I tried to locate the FAQ, hope I am not asking a common question. I am working with a very good RPG programmer on a system he knows inside out on an old AS400 (720) system. However it has been upgraded to an eServer and anything new he doesn't have much knowledge of. It's got all of the IBM stuff (UDB etc) I am successfully running queries against the DB2 database via ODBC / JDBC (am not tied to either). However I need to call an RPG program passing a parameter and retrieving 2, there is no way to just do the SQL (long story, trust me!). I believe there is a way to: call lib.progr('var1',?,?) but I can't get it to work. Getting messages like <eb1>SQL0204 - PROGR in type *N not found. I think it just can't find it. I think there is something I need to have done to the program? However, if there is I am not sure how and nor is the RPG programmer! Please if anyone can help point me in the right direction, I would be very grateful. I have full access to the machine, and at the moment am testing through ODBC/JDBC using a windows machine, I will probably be using PHP and linux to actually run against the machine at some point. NeilC wrote: > I have full access to the machine, and at the moment am testing > through ODBC/JDBC using a windows machine, I will probably be using > PHP and linux to actually run against the machine at some point. Speaking in Java: get jt400.jar from either a PC with iSeries Access (formarly known as ...

Using results of query in a query
Hello all, I'm looking for some hints as to how to use the results of a query in another query. I assume I'm 'thinking' wrong in how to solve this, so I'm hoping someone can clobber me and send me in the right direction. My question is best illustrated by simplified example. I have one table (table A) with a single column called "Word". This table contains 100 records of single words. Another table (table B) contains whole sentences in a single column. I want to eventually count the occurances of all of the words in Table A, in each of the sentences in Table B and store these results in another table. Here's the 'thinking wrong' part. The only way I can see to do this is to select the entire table A using a cursor, then select 'LIKE's of the result against each individual row in table B. This seems horribly inefficient, and I know there have to be one or two MUCH better ways to do this, but I can't get me head wrapped around thinking in SQL at this stage. Any info appreciated. Thanks, Stan stanwalters@hotmail.com (Stan) wrote in message news:<9a4a871d.0405022057.7a22b375@posting.google.com>... > Hello all, > I'm looking for some hints as to how to use the results of a query in > another query. I assume I'm 'thinking' wrong in how to solve this, so > I'm hoping someone can clobber me and send me in the right direction. > > My question is best illustrated by simplified ex...

Variables in a Query Manager Query
Okay I am stumped. Here is my query: SELECT LEFT(TRIM(EMFNAM),1)||RIGHT(TRIM(CHAR(EXSSAN)),4) FROM STORE.PEIS301X JOIN STORE.PEIS301 ON EXEM# = EM# WHERE EXEM# = (SELECT PA# FROM STORE.PEIS480H JOIN STORE.PEIS301 ON PA# = EM# WHERE PADATA = '&USR') Ideally I would run this, I would get prompted for a value for &USR, and the end result would be a 5 character string. Running it from the query manager results in no results because it doesn't see &USR as a variable. If I take the quotes out, it fails. Double and triple quotes does not work either. &USR is the username, ie JOE. So what I need to happen is the comparison to be "WHERE PADATA = 'JOE')" What am I doing wrong? Thanks, Chris il 09.03.2010 1.40, Scrive Hal 38510904: > Okay I am stumped. Here is my query: > > SELECT LEFT(TRIM(EMFNAM),1)||RIGHT(TRIM(CHAR(EXSSAN)),4) FROM > STORE.PEIS301X > JOIN STORE.PEIS301 ON EXEM# = EM# > WHERE EXEM# = > (SELECT PA# FROM STORE.PEIS480H > JOIN STORE.PEIS301 ON PA# = EM# > WHERE PADATA = '&USR') > > Ideally I would run this, I would get prompted for a value for &USR, > and the end result would be a 5 character string. Running it from the > query manager results in no results because it doesn't see &USR as a > variable. If I take the quotes out, it fails. Double and triple quotes > does not work either. > > &USR is the username, ie JOE. So what I need to ha...

Web resources about - AS400 Queries with out a existing query - comp.sys.ibm.as400.misc

Pre-existing condition - Wikipedia, the free encyclopedia
J. James Rohack, president of the American Medical Association , has stated on a Fox News Sunday interview that exclusions, based upon these ...

STUDY: Facebook More Effective In Driving Viewers To New Shows Than Existing Ones
... television and Facebook and other social networks, finding that social media are more effective at driving viewership of new shows than existing ...

A New Way To Re-Engage Your Existing Mobile App Users
Today we are introducing a new way to re-engage your mobile app users with Facebook ads. You can now advertise to people who have taken specific ...

Facebook expands IPO with additional 83.8M shares from existing stockholders
... according to an eighth amendment to its S-1 filing with the Securities and Exchange Commission. Most of these shares are being sold by existing ...

How do you grow your brand through existing clients?
Anna Farmery and I are doing a podcast this week on how to leverage happy clients to grow your brand. We&#39;d love to include your advice in ...

Video Zoom - Apply Zoom To Existing Videos, Crop on the App Store on iTunes
Get Video Zoom - Apply Zoom To Existing Videos, Crop on the App Store. See screenshots and ratings, and read customer reviews.

“The important thing is not to stop questioning. Curiosity has its own reason for existing.” Albert Einstein ...
Pretty dreary here today, but this stop sign caught my attention. Bright, vivid colors can draw attention and add "pop" to a photo. Make a photo ...

Shorten unveils Labor election policy: No more negative gearing existing homes
... policy that Opposition Leader Bill Shorten says will save the federal budget $32 billion over a decade. But, from July 2017, buyers of existing ...

US existing home sales fall in August
... as investors retreated from real estate and first-time buyers remained scarce. The National Association of Realtors says sales of existing ...

Leaked cabinet advice shows existing road funding to be relaunched as new
... under question, after a leaked cabinet submission showed Roads Minister Luke Donnellan&#10003;&#8203; is preparing to simply repackage an existing ...

Resources last updated: 3/24/2016 3:01:20 PM