Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

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.

techwriterAR (MIS) (OP)
1 May 08 17:25
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 (Programmer)
1 May 08 19:19


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

CODE

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

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

Skip,

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

koresnordic (IS/IT--Management)
2 May 08 9:57
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.

pc

Graham

SkipVought (Programmer)
2 May 08 10:20



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,

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

techwriterAR (MIS) (OP)
2 May 08 10:45
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 (MIS)
2 May 08 10:52
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 (MIS) (OP)
2 May 08 11:01
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 (IS/IT--Management)
2 May 08 11:02
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>?)
 

pc

Graham

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