Freight question

[pronet74]

We are adding freight to our orders now, which is done in the OE -> Billing -> Select Orders Screen.

What table and field does this information reside in? I looked at the help, and it shows oeordlin table, but I can't find it there.

 

[dgillz]

This is in the OEORDHDR_SQL table in the field frt_amt.

This data only stays here for a short time, after the invoice is posted it will reside in the same field, but in the OEHDRHST_SQL table.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pronet74]

Thanks.. I have another question. Anytime I run a query involving that table, it takes forever. I know there is alot of data in it, but is there anyway to speed it up? I tried importing the table locally, but that didn't help. I'm running on a Pention 2.4 GHz machine with 512mb RAM. Maybe if I index the local table?

 

[dgillz]

Are you running Pervasive or MS SQL? What is the query written in, MS Access, or what?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[kscheeler]

Are you querying the OEORDHDR or OEHDRHST table? The OEHDRHST table most likely is the much larger of the two since it has all of your invoice header history. One of the main key's on that table is inv_dt. If you look for a specific date range and enter it in the format YYYYMMDD, it will only pull up records in that range no matter how many records are in the table.

If you want to see all of the indexes on a table and you are SQL, you can go to Enterprise Manager, drill down to databases, choose your production database, choose tables below that, highlight and right click on the table you want to look at, go to "All tasks", then "Manage Indexes". It will show the index name, whether it's clustered or not (unique or not) and what fields in order that are in the index.

Kevin Scheeler

 

[pronet74]

I'm using pervasive SQL. What's weird is the date format. I have a query in access to pull it up by a range of dates. In the query if I just leave the date format alone (ie. 20040501) then it pulls up the right records. However in the query I have:
SELECT Mid([DATE_ENTERED],5,2) & "/" & Mid([DATE_ENTERED],7,2) & "/" & Left([DATE_ENTERED],4) AS ORDER_DATE, Sum(FREIGHT.FREIGHT_AMOUNT) AS SumOfFREIGHT_AMOUNT
FROM FREIGHT
GROUP BY Mid([DATE_ENTERED],5,2) & "/" & Mid([DATE_ENTERED],7,2) & "/" & Left([DATE_ENTERED],4)
HAVING (((Mid([DATE_ENTERED],5,2) & "/" & Mid([DATE_ENTERED],7,2) & "/" & Left([DATE_ENTERED],4)) Between [Enter Starting Date] And [Enter Ending Date]));

So the user can type in 05/01/2004 instead of 20040501 However, when I do this it pulls up data from 05/01/2000, 05/01/2001,05/01/2002,05/01/2003 and 05/01/2004.

Anyone know why this may be happening?

 

[pronet74]

Nevermind.. Fixed it. The correct query code should be:

SELECT Sum(FREIGHT.FREIGHT_AMOUNT) AS SumOfFREIGHT_AMOUNT
FROM FREIGHT
WHERE (((Mid([DATE_ENTERED],5,2) & "/" & Mid([DATE_ENTERED],7,2) & "/" & Left([DATE_ENTERED],4)) Between [Enter Starting Date] And [Enter Ending Date]));

I'll look to see about those indexes. Thanks

 

[kscheeler]

A shorter way to get all of the indexes on a table is to go into query analyzer and put in the following:

use demodata
exec sp_helpindex 'oehdrhst_sql'

Kevin Scheeler

 

[crystalreporting]

Or - if you're using Pervasive and ODBC, write a Crystal report on the indexes. You can also do this with the file and field.ddf's. In your database options within Crystal, make sure system tables is checked - this gives you access to the three DDF files. A really handy report is one that prompts for field name, then lists all of the tables where that field exists - kind of a 'where used' report. I have an example if anyone would like a copy.

Peter Shirley

http://www.linkcorp.com

 

[dgillz]

Pronet,

What you really ought to do is take advantage of the current promo Exact is offering to convert to MS SQL. Between now and June 30th you get free SQL client licenses!

This is a great deal. Reporting wise, everything is a LOT easier in SQL, regardless of what reporting tool you are using. I personally am bugging the heck out of every Pervasive customer I have! (I am trying to make that number a zero).



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com