I am joining to hold files to extract order information (both cost and retail) by skus. I'm getting the error, Unknown error occurred. Agent on reporting server EDASERVE may have crashed. Please investigate reporting server log.

The code from the first hold file is:

TABLE FILE SS_DW_SALES
SUM
ORDERED_QTY
EXTENDED_PRICE
BY ORDER_NO
BY ITEM_SKU
BY ROOT_LINE_ID
HEADING
""
FOOTING
""
WHERE ( SQL_DATE GE DT(&Start_Date) ) AND ( SQL_DATE LE DT(&End_Date 23:59:59.999) );
WHERE ITEM_SKU EQ '&SiteStuff_Sku';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS DW_HOLD FORMAT FOCUS INDEX 'ROOT_LINE_ID'
ON TABLE SET HTMLCSS ON


And the second:

TABLE FILE SS_F_PO_LINES_TEMP
PRINT
PO_NO
RELEASE_NUM
SUPPLIER_ID
SUPPLIER_TYPE
PO_QTY
UOM_CODE
UNIT_COST
SUPPLIER_NAME
SUPPLIER_SKU
CUSTOMER_ID
PROPERTY_ID
CONVERSION_RATE
Extended_Cost
BY ROOT_LINE_ID
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS PO_HOLD FORMAT FOCUS INDEX 'ROOT_LINE_ID'
ON TABLE SET HTMLCSS ON

They are joined at the ROOT_LINE_ID field.

I'm very new to this tool and would like to understand from the report painter tool what I should do to keep this type of error from happening.

Thanks!

Do the two requests run independently?

You don't need quotes around the ROOT_LINE_ID field on the hold statement.

The parent file can just be a hold format alpha. It is only important that the target be a FOCUS file and indexed.

You don't need any styling or headings/footings if you are holding data.

With all that said, that doesn't do anything to solve your problem.

We need to isolate why the agent is crashing and, let me tell you, that is not easy. So, to start I would try to run the two requests separately to see if they run alright. If they don't, try reducing the number of rows.

After you do that, let us know the results so we can figure out what to do next.

Ginny -

Thanks for the quick response.

As I created this in Report Painter, anything in the code came from the tool, so why would it put quotation marks if they aren't needed? At any rate, it appears that the second hold file is where the error is occuring. The first appears to run correctly.

Thanks!
Cathie

I don't see anything syntax-wise in the second request that would cause a problem. Is there a reason the Extended_Cost field is lower case instead of upper like the rest of the fields? Ginny also mentioned reducing the number of rows. If you add WHERE READLIMIT EQ 10 does the error still occur?

Darin,

The Extended Cost is a virtual column I added and I didn't CAP it....

I added the retrieval limit of 10 and it does not give me the error!

Cathie

Keep increasing the limit until it breaks. Could you be running out of disk space? How many rows are you actually returning? Can you do a COUNT * using your WHERE conditions for each table to find out how many rows there would be in each hold file?

Sorry about all of these questions but they will help isolate the problem.

Do you have FOC2GIGDB turned on? Could your second TABLE file be returning a result set that is greater than 2 Gig in size (total file size)? If that is the case, you may be failing on exceeding the FOCSORT size limit. I see you're on version 7.6, which has unlimited FOCSORT, provided FOC2GIGDB is turned on.

When you ran the request individually
TABLE FILE SS_DW_SALES
TABLE FILE SS_F_PO_LINES_TEMP

How large were each of the HOLD files?

If you then TABLE'd the HOLD file, PRINT *, is the data correct? Do you see any "special" characters?

quote:

Originally posted by Diptesh Patel:
Do you have FOC2GIGDB turned on? Could your second TABLE file be returning a result set that is greater than 2 Gig in size (total file size)? If that is the case, you may be failing on exceeding the FOCSORT size limit. I see you're on version 7.6, which has unlimited FOCSORT, provided FOC2GIGDB is turned on.

Is this a server-side setting?

Yes, FOC2GIGDB is a server side setting, but I think you can also set it in your Focexec. Try setting it in the Focexec first, to see whether that resolves your problem before turning it on at the server level. Remember though that unlimited Sort file sizes have their downside. You could have a 'monster' query that may consume all available space on the server.