Recent content by ERPgal

I tried running statistics and that does not affect any records. When I say the query works at 43 seconds, I mean that all the other 4 tables' data are pulled, but the 5th table is completely ignored. Any columns representing the 5th table show as null. However, with {NO REORDER}, the 5th...

I am on the latest patch of 9.1D (old version ). I tried to run the following two statements against the table in question and it came back with "0 Rows Affected" Here are the two statements I executed: UPDATE STATISTICS for pub.rcvdtl UPDATE INDEX STATISTICS for pub.rcvdtl

Problem: I am joining 5 tables together. Two of the joins are left joins. One of the left joined tables only has 2400 records and works fine. However the other left joined table contains 101,000 records and it is NOT joining. I am only able to get it to work by disabling the optimizer by...

Please Disregard this post! The problem is a data issue that stems from an apparent business process issue! I have a query here that is causing me a headache. I am using the timestampdiff function and passing it an interval of SQL constant type SQL_TSI_DAY. The function seems to work when...

You can also create a new index that has the fields available to you. Or you can try to trick the db using a LIKE command i.e. WHERE a.myfield LIKE '%' The former is more likely to work.

Found what I was doing wrong. I needed add a WHERE clause at the very end of the query, outside of the parenthesis. select jo.jobnum ,cast(jh.duedate as varchar(25)) as duedate ,jo.oprseq ,jo.description --,jo.duedate as OpDue ,jh.partnum ,jh.revisionnum ,case when jh.jobclosed = 1 then...

Problem: I am on Progress 9.1D and I cannot build a query that gets me the information I need. Progress seems to only allow serial joins. Meaning, I must singly join each table one at a time; one before the other. I need the tables involved in Joins 2 and 3 to be cojoin before I join it to...

You make a good point Tamhas, but it turns out that in stead of telling us to upgrade progress, they are telling us to Upgrade our ERP system ...go figure. It is still a supported system, but most of their new employees have more expertise with their most recent version of the software. I...

We are running Vantage 6.1.542 which runs on 9.1D and haven't gone through an ERP upgrade. We are contemplating whether or not an upgrade is the best move for our company. Until then we are stuck with 9.1D

I found the solution. Progress does not seem to like working with Resultsets / temporary tables. Instead it is best to build the query serially using a sequence of joins. Basically, you are tacking on one table at a time. Here is what I ended up with: select jm.jobnum as jnum...

Will you copy and paste an example of a similar query that you were able to execute? That would really help me to see what I am doing wrong with SQL92.

Tamhas, this query will be used in an ASP.NET application. What is ABL ?

or ... and jh.duedate between to_date('10/28/2008') and to_date('11/19/2008')

Good suggestion. I took it a part and it looks as if no matter what I do, and no matter how many tables I include/exclude, the DB cannot do a smart join. It's only doing a Cross Join / Cartesian Product join with the two result sets. tamhas, have you successfully executed a similar query in...

I tried your suggestion and I am still getting the same result. Here is what I did: Select jnum, fgdspart, mtlpart,ponum, poline from ( select jm.jobnum as jnum ,jm.partnum as MtlPart ,jm.assemblyseq as assseq ,jm.company as comp ,jh.partnum as FGDSPart ,jm.requiredqty as...