Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Query AS400 database in Excel

Query AS400 database in Excel

I'm trying to build an excel 2007 spreadsheet by pulling data from an AS400 database.  I am writing my query inside Excel using 'Get data from other sources'.  This report is supposed to return quantity of each product sold from the current date to one year back.  I need to set the date field to the current date - 365 days.  However every thing I have tried does not work.  

This part of my query works and returns over 90,000 records:

select a.prdno, b.txdta, a.ittdt, sum(a.quant)
from initp100c a inner join mftxp100x b on (a.prdno = b.text#) and (b.txtyp = 'PD') and (b.txsqn = 0)
where  a.tcode in ('S')  
group by a.prdno, b.txdta, a.ittdt

If I add the date criteria, the program returns the message - could not add the table 'initp100c'.

select a.prdno, b.txdta, a.ittdt, sum(a.quant)
from initp100c a inner join mftxp100x b on (a.prdno = b.text#) and (b.txtyp = 'PD') and (b.txsqn = 0)
where  a.tcode in ('S')  and a.ittdt <= [current date - (365 days)]
group by a.prdno, b.txdta, a.ittdt

I have tried every combination I can think of to get this thing to run.  i searched the Microsoft Office forum by narrowing my criteria to excel ms query and tried everything I found.  Nothing worked.  I'm hoping someone can help me.


RE: Query AS400 database in Excel


I don't know AS400 syntax, but dates are just numbers.  In AS400 SQL a date is the number of days since 1AD

I'd guess...


, b.txdta
, a.ittdt
, sum(a.quant)

  initp100c a inner join
  mftxp100x b
  on (a.prdno = b.text#)
 and (b.txtyp = 'PD')
 and (b.txsqn = 0)

where  a.tcode in ('S')
  and a.ittdt <= [current date] - 365

group by
, b.txdta
, a.ittdt


glassesJust got a nuance...
to replace the old subtlety!tongue

RE: Query AS400 database in Excel

Normally when I drag data down form the 400 to a spreadsheet, I have a cell in the spreadsheet that has the resultant date I wish to check against, then use this as a parameter in the query. So when asked what the value is, in excel, just point to the cell and tell it to use this reference in future.



RE: Query AS400 database in Excel

Dates are tricky.  If you are using a Date in a cell as a parameter Data > Import External Data > Parameters, you must cast the data in the format that your SQL expects for a date, usually doing a conversion of Text to Date.


glassesJust got a nuance...
to replace the old subtlety!tongue

RE: Query AS400 database in Excel

I do not know how to create a parameter query inside Ms Query.  I use parameters in Crystal Reports and MS Access, but I have not tried it in Excel.  Would you have time to explain how I would set up my query to accomplish using the current date when running/refreshing the query?


RE: Query AS400 database in Excel

  A silly question, but what kind of permissions are set on this table ('initp100c') you can't add to your query???

Do you have permissions to access it?

RE: Query AS400 database in Excel


Yes, I have permission to access all the fields in the initp100c and mftxp100x tables.  I cannot change or edit records in either table but I can access them.  Most of the time I have to use the Data Transfer from AS400 when I need to get data, which comes in as a text file.  Then I use Data | Get External Data | from text to pull the data into excel.  There are a few tables that we use frequently which have been given an alias so we can skip the Data Transfer process and bring the data right into excel.


RE: Query AS400 database in Excel

How I normally do this.

in Excel:

1) Select the data ribbon, then "from other sources", from "microsoft Query"

2) Select the appropriate data source (or create a new one using iseries access as the driver.

3) Select the fields from the right files.

4) {next} three times

5) Change the option to be View data or edit query... {Finish}

6) By default you cannot see any selection lines, so got upto the menu option "View" and tick Criteria

7) Select the field you wich to select based on (in your case a.ittdt)

8) Under this, in the value field enter a prompt that means something. e.g. [date?] - note this must be inside square brackets

9) Now you can close this. You will probably be asked for a value. Enter a valid response.

10) When you now go back to Excel and are asked for where you want to put the data.

11) There is a button marked "Properties" - click this

12) Select the definition tab and then click on parameters

13) Select the bottom option "get the value from the following cell". Select the cell that holds the calculated date

14) You should now be done - just change the cell value and refresh.

An example on my sheets gets this as the SQL




Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close