Query AS400 database in Excel

[techwriterAR]

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.

gjeffcoat

 

[SkipVought]

Hi,

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

http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafymst02.htm

I'd guess...

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 <= [b][current date] - 365[/b]

group by
  a.prdno
, b.txdta
, a.ittdt

Skip,
[sub]
Just got a nuance...
to replace the old subtlety![/sub]

 

[koresnordic]

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.



Graham

 

[SkipVought]

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.

Skip,
[sub]
Just got a nuance...
to replace the old subtlety![/sub]

 

[techwriterAR]

Koresnordic,
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?

--gjeffcoat

 

[PRPhx]

techwriterAR,
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?

 

[techwriterAR]

PRPhx,

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.

gjeffcoat

 

[koresnordic]

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

SELECT STRUCTURE.FNAME1, STRUCTURE.EMPSN, STRUCTURE.DEPDES, STRUCTURE.EMTITL, STRUCTURE.LVRDT
FROM S44H0202.SAAPAYDTA.STRUCTURE STRUCTURE
WHERE (STRUCTURE.WORM='W') AND (STRUCTURE.LVRDT>?) OR (STRUCTURE.WORM='B') AND (STRUCTURE.LVRDT>?)




Graham