I have a puzzle to solve and perhaps someone can see something I can't see!
I have two similiar sql's. One works, the other one crashes the agent. Both copied and dropped into pl/sql developer will execute just dandy.
What's crashing the agent? The trace will not work because it crashes.

Here's the bad boy:
select * FROM (
select RANK() over( ORDER by valu_lost_amt desc ) r,
acct_nme, VALU_LOST_AMT, rgn_nme, oppy_nme
FROM
oppy_fact b
JOIN oppy_dim ba
ON b.oppy_dim_id=ba.oppy_dim_id
LEFT OUTER JOIN rgn_dim c
ON b.rgn_dim_id=c.rgn_dim_id
LEFT OUTER JOIN acct_dim d
ON ba.acct_dim_id=d.acct_dim_id
WHERE UPPER(stg_nme)='LOST'
AND TO_CHAR(b.yr_mo_id)=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'yyyymm')
)
WHERE r <= 10
;

Here's the similiar one that works:
SELECT acct_nme, rgn_nme, oppy_nme, tot_price_amt FROM
(SELECT RANK() over( ORDER BY SUM(tot_price_amt) DESC) r,
acct_nme, SUM(tot_price_amt) AS tot_price_amt, rgn_nme, oppy_nme
FROM
oppy_line_item_fact a
JOIN oppy_fact b
ON a.oppy_fact_id=b.oppy_fact_id
JOIN oppy_dim ba
ON b.oppy_dim_id=ba.oppy_dim_id
LEFT OUTER JOIN rgn_dim c
ON b.rgn_dim_id=c.rgn_dim_id
LEFT OUTER JOIN acct_dim d
ON ba.acct_dim_id=d.acct_dim_id
WHERE UPPER(stg_nme)='WON'
AND TO_CHAR(b.yr_mo_id)=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'yyyymm')
GROUP BY acct_nme, rgn_nme, oppy_nme
)
WHERE r <= 10
;

I know it's something the RANK line because if I take it out it will execute and return data.
I have tried spaces, uppercase, lowercase and starting everything in pos 3 and just everything I can think of, but no luck.
If anyone sees anything please advise.
Thanks,
--wg

This message has been edited. Last edited by: Kerry,

I'm sure more than one of us reading your post would be interested in knowing the version of WebFOCUS and the RDBMS you're running this on.

Francis,

I would love to post that information but don't know how..... I have it on my profile and the signature box is checked..... what am I doing wrong?

--wg

Edit you profile. Scroll down to the "(**required) Product Signature: Please fill out your WebFOCUS release, OS/Platform, and expected Output format(s)" box and enter the information there.

Clumsy, but necessary.

Your profile doesn't specify the DBMS - it could be important.

Cheers,

All,

Sorry.... another test (I'm a moron).

--wg

Wilfred,

A couple of questions for you:

- How long does it take for the agent to crash? does it happen immediately? or does it happen after "a while"?
- How many records would you be going after when running this query and all of the joins have been resolved?

Your queries seem similar but I think they are dealing with volumes of records entirely different. The "bad boy" would attempt to perform a RANK() over each and every record that matches the joining criteria which means a huge sorting area if that dataset is "big".

The second query calculates RANK() over a set of already aggregated records (thanks to the GROUP BY in there) so the Oracle optimizer may be doing less work on this one.

This may not be related to your error, but unless you have Oracle Fuction-based Indexes on both UPPER(stg_name) and TO_CHAR(yr_mo_id) I would advise that you review your code and rewrite your filter conditions to avoid applying functions over the selection columns; otherwise you are pretty much stopping Oracle from using any index a/o partition pruning to better serve your queries.

It is preferable to select based on:

WHERE stg_name = UPPER(....)
WHERE yr_mo_id = TO_DATE(.....)

All the datasets are relative small (to our standards, telco). The good one runs in less than a minute. The bad one runs for maybe 2 mintues, then crashes.
Remember they both work nicely from pl/sql developer using the same connect account that the webfocus adapter is using.

Thanks for your response,
--wg

Winfred,

What happens if you replace the following line in your "good" query:

SELECT acct_nme, rgn_nme, oppy_nme, tot_price_amt FROM

By:

SELECT * FROM

Does the query still work?

In a similar fashion, could you try not to use "*" in your "bad" query and specify instead each column you need? You may try 2 different scenarios: one including your "r" column and another one excluding it (just as you're doing with the query that works).

I'm just curious to see what happens ...

- Neftali.

Thanks njsden!
I've been trying everything, but your comments caused me to add the grouping (like the one that works) and it works now, from pass thru.

Now, I don't have a clue as to why it would work in pl/sql developer but not in wf pass-thru (as posted).

This is such a hot job, I must move on....
Thanks for all the support,
--wg

njsden,

The original (bad and good) had select *., but I tried both ways on the bad one and it would not work from wf pass-thru.

However, it's working now, thanks to you!
--wg