Skip to Content
0
Former Member
Jul 21, 2009 at 07:29 PM

Help w/MaxDB Function; also: how does the "Debug SQL" function work?

32 Views

Hi there forum folks,

In my former life, I was a Basis guy, but I haven't had the pleasure of working directly with SAP applications in a few months. My current project is to attempt to use MaxDB in a real estate environment. We're tracking home listings so that we can build statistical reports... such as "what agents are in the Top 100 in postal code X?"

Anyway, as part of this project, I have attempted to construct my very first MaxDB database function. Unfortunately, it doesn't give me the answers I'm hoping to see. Here's the function:

-


CREATE FUNCTION COUNT_LISTINGS (AGENTID CHAR(10)) RETURNS FIXED(6,1) AS

VAR COLISTINGAGENTID CHAR(10);

LISTINGAGENTID CHAR(10);

MLSNUMBER CHAR(7);

UNITS FIXED(6,1);

SET UNITS = 0;

DECLARE FUNCTIONRESULT CURSOR FOR

SELECT MLSNUMBER,

LISTINGAGENTID,

COLISTINGAGENTID FROM FREDDIE.GLAR_SOLDS

WHERE LISTINGAGENTID = :agentid OR COLISTINGAGENTID = :agentid;

IF $COUNT IS NULL THEN

BEGIN

CLOSE FUNCTIONRESULT;

RETURN UNITS;

END

ELSE

SET $RC = 0;

WHILE $RC = 0 DO BEGIN

FETCH FUNCTIONRESULT INTO :mlsnumber, :listingagentid, :colistingagentid;

IF ( LISTINGAGENTID = AGENTID AND COLISTINGAGENTID IS NULL ) OR

( COLISTINGAGENTID = AGENTID AND LISTINGAGENTID IS NULL ) THEN

SET UNITS = UNITS + 1

ELSE

SET UNITS = UNITS + 0.5;

END;

CLOSE FUNCTIONRESULT;

RETURN UNITS;

-


I've tried to follow the official MaxDB documentation. My first deviation from that standard was the use of the "$COUNT" variable (instead of the "$RC" variable) immediately after the DECLARE/SELECT statement above. When I tried to use $RC, for either a successful or unsuccessful query, $RC was always set to a non-zero value.

I believe I'm past that, but now my issue is down around that FETCH statement. The UNITS variable doesn't end up with the value I expect. I know that it can be terribly confusing to try to analyze someone else's logic, but here's a brief narrative that describes what I'm trying to do...

The GLAR_SOLDS table holds one line for each home sold. It's keyed by the MLSnumber. Each record also stores up to four agents who've been involved in the transaction: the listing agent, the co-listing agent, the selling agent, and the co-selling agent. The database function I've written above pertains to the listing side only. If I can get this to work, a separate function will process the selling side. If no co-listing agent is involved in a given sell, that agent should get credit for 1 unit sold. If he/she has help from a co-listing agent, the agent should only get credit for 1/2 unit sold.

Also, does anyone know how the "Debug SQL" functionality is supposed to work within Database Studio? When I right-mouse click on my function, and follow the path thru "Debug As...", after entering the connection & function arguments, I'm presented with an empty screen. If you could point me to some documentation somewhere, I'd gratefully read it.

I'm using MaxDB 7.7.06.09 on Windows XP (WIN32) with MaxDB Database Studio 7.7.06.09 (build 009-123-202-944).

Thanks everyone for your help & advice.

~Fred