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

[Former Member]

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

[Former Member · Accepted solution]

Whew! Sorry for the bad formatting, everyone. I'm working on trying to clean it up...

[Former Member · Accepted solution]

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;

[Former Member · Accepted solution]

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

[lbreddemann · Accepted solution]

Hi there,

I hope you do realize how flawed your requirements are.

The definition of "TOP 100" alone would be worth a discussion. What are the TOP 100?

What happens when different agents score alike?

Anyhow, what you described doesn't require a stored procedure.

And even more important - as I'm struggeling myself everytime I work with stored procedures I'm not to keen on explaining how to do it

Ok, so here's what I would do.

Note that, given what you've provided to work with (= NOTHING!), I needed to make some assumptions.

So the example is far from perfect (e.g. you could have all four agent types assigned with the same agent etc.)

First let's setup the data:

Here goes our salesforce

create table "LARS"."AGENTS"(
	"ID" INTEGER not null default serial,
	"NAME" VARCHAR (20) ASCII not null,
primary key ("ID"))

insert into agents values (default, 'Lars')
//
insert into agents values (default, 'Mona')
//
insert into agents values (default, 'Paul')
//
insert into agents values (default, 'Lisa')

sqlcli db77=> select * from agents
| ID             | NAME                 |
| -------------- | -------------------- |
|              1 | Lars                 |
|              2 | Mona                 |
|              3 | Paul                 |
|              4 | Lisa                 |

4 rows selected (1263 usec)

--> see 2nd part of the reply - split due to formatting length constraints of this superb forum software...

[lbreddemann · Accepted solution]

And here's their work:

create table "LARS"."SOLDHOMES"(
	"OBJECT_ID" INTEGER not null default serial,
	"LIST_AGENT_ID" INTEGER,
	"LIST_COAGENT_ID" INTEGER,
	"SELL_AGENT_ID" INTEGER,
	"SELL_COAGENT_ID" INTEGER,
primary key ("OBJECT_ID"),
	foreign key "FK_LIST_AGENT" ("LIST_AGENT_ID") references "LARS"."AGENTS" ("ID") on delete restrict,
	foreign key "FK_LIST_COAGENT" ("LIST_COAGENT_ID") references "LARS"."AGENTS" ("ID") on delete restrict,
	foreign key "FK_LIST_SELL_AGENT" ("SELL_AGENT_ID") references "LARS"."AGENTS" ("ID") on delete restrict,
	foreign key "FK_SELL_COAGENT" ("SELL_COAGENT_ID") references "LARS"."AGENTS" ("ID") on delete restrict)
	
Insert into soldhomes values (1, 1, NULL, NULL, NULL)	
// -- lars sold 1
Insert into soldhomes values (2, 1, 2, NULL, NULL)
// -- lars sold 1 with help of Mona
Insert into soldhomes values (3, 2, NULL, NULL, NULL)
// -- mona sold 1
Insert into soldhomes values (4, 2, NULL, NULL, NULL)
// -- mona sold 2
Insert into soldhomes values (5, 3, 4, NULL, NULL)
// -- paul sold 1 with help of Lisa
Insert into soldhomes values (6, 4, 3, NULL, NULL)
// -- Lisa sold 1 with help of Paul
Insert into soldhomes values (7, 1, NULL, NULL, NULL)
// -- Lisa sold 1 
Insert into soldhomes values (8, 1, NULL, NULL, NULL)
// -- Lars sold 1
Insert into soldhomes values (9, 3, 4, NULL, NULL)
// -- Paul sold 1 with help of Lisa
Insert into soldhomes values (10, 4, 1, NULL, NULL)
// -- Lisa sold 1 with help of Lars

--> forward to 3rd part of the reply

[lbreddemann · Accepted solution]

Let's check what we have now:

}sqlcli db77=> select * from soldhomes
| OBJECT_ID      | LIST_AGENT_ID      | LIST_COAGENT_ID      | SELL_AGENT_ID      | SELL_COAGENT_ID      |
| -------------- | ------------------ | -------------------- | ------------------ | -------------------- |
|              1 |                  1 | ?                    | ?                  | ?                    |
|              2 |                  1 |                    2 | ?                  | ?                    |
|              3 |                  2 | ?                    | ?                  | ?                    |
|              4 |                  2 | ?                    | ?                  | ?                    |
|              5 |                  3 |                    4 | ?                  | ?                    |
|              6 |                  4 |                    3 | ?                  | ?                    |
|              7 |                  1 | ?                    | ?                  | ?                    |
|              8 |                  1 | ?                    | ?                  | ?                    |
|              9 |                  3 |                    4 | ?                  | ?                    |
|             10 |                  4 |                    1 | ?                  | ?                    |

10 rows selected (114.560 msec)

So, if II counted correctly, we should end up with a result like this:

Lars 4.5, Mona 2.5, Paul 2.5, Lisa 3

--> see answer 4. part

[lbreddemann · Accepted solution]

or to get the better overview we can join the tables:

sqlcli db77=> select sh.object_id, lag.id as list_agent_id, lag.name as list_agent, lcag.id as list_coagent_id, lcag.name as list_co_agent
> from soldhomes sh join agents lag
>                                        on sh.list_agent_id = lag.id
>                                 left outer join agents lcag
>                                        on sh.list_coagent_id = lcag.id;
| OBJECT_ID      | LIST_AGENT_ID      | LIST_AGENT           | LIST_COAGENT_ID      | LIST_CO_AGENT        |
| -------------- | ------------------ | -------------------- | -------------------- | -------------------- |
|              1 |                  1 | Lars                 | ?                    | ?                    |
|              2 |                  1 | Lars                 |                    2 | Mona                 |
|              3 |                  2 | Mona                 | ?                    | ?                    |
|              4 |                  2 | Mona                 | ?                    | ?                    |
|              5 |                  3 | Paul                 |                    4 | Lisa                 |
|              6 |                  4 | Lisa                 |                    3 | Paul                 |
|              7 |                  1 | Lars                 | ?                    | ?                    |
|              8 |                  1 | Lars                 | ?                    | ?                    |
|              9 |                  3 | Paul                 |                    4 | Lisa                 |
|             10 |                  4 | Lisa                 |                    1 | Lars                 |

10 rows selected (6491 usec)

--> see answer 4. part

-->Edited by: Lars Breddemann on Jul 22, 2009 12:02 AM

--> sorry this needs to be a LEFT outer join of course...

[lbreddemann · Accepted solution]

Once we have this setup, it's a pretty easy job: we just have to tell the database to count '1' for each entry in LISTAGENT_ID and '0.5' for each one in COLIST_AGENT_ID and sum this stuff up:

sqlcli db77=> \mu
Multiline mode switched ON
sqlcli db77=> select list_agent, sum (credit) as SUM_CREDIT from (
> select sh.object_id, lag.name as list_agent, 1 as credit
> from soldhomes sh join agents lag on lag.id = sh.list_agent_id
> union all
> select sh.object_id, lag.name as list_coagent, 0.5 as credit
> from soldhomes sh join agents lag on lag.id = sh.list_coagent_id
> )
> group by list_agent;
| LIST_AGENT           | SUM_CREDIT                                     |
| -------------------- | ---------------------------------------------- |
| Lars                 |                                            4.5 |
| Lisa                 |                                              3 |
| Mona                 |                                            2.5 |
| Paul                 |                                            2.5 |

4 rows selected (8240 usec)

Not too difficult is it?

Concerning the "Debug SQL" functionality: I've not the faintest idea - maybe it's not yet implemented.

regards,

Lars

[Former Member · Accepted solution]

Lars,

Thanks so much for all this great work & information! I'm going to study your work for a little bit... then I'll reply further.

~Fred

[Former Member · Accepted solution]

Lars,

I've built a query (based upon your great work) that manages to calculate the listing units & listing volume. I'll post that query in a new response so I have enough room. But now, my issue is how do I store the resultant data in its own table? I have a six-field table (named GLAR_RPT_MARKET_PENETRATION) in which I hope to ultimately store AgentID, OfficeID, ListUnits, ListVolume, SoldUnits, and SoldVolume. However, I'm having difficulty in trying to use the UPDATE statement to process a subquery which returns multiple rows. And keep in mind that the query that appears below only processes the Listing side of the equation so far. My intent is to use the query below for the Listing side, then pump that result set into the GLAR_RPT... table noted above, and then construct a new query to calculate the Selling side, and finally "merge" (?) that result set into the GLAR_RPT... table. While writing this, it occurs to me that I can probably enhance the query (that will appear below) to do both... but then would an INSERT (instead of an UPDATE) allow me to process multiple rows from a subquery?

My existing query to follow below...

Thanks,

~Fred

[Former Member · Accepted solution]

NOTE: "NE" substituted below for less-than-greater-than symbols.

select "Agent",
       "Office",
   sum("Listing Units")    as "Listing Units",
   sum("Sold Price")       as "Listing Volume"

from (

( select listingagentid    as "Agent",
         listingofficeid   as "Office",
         1                 as "Listing Units",
         soldprice         as "Sold Price"
    from glar_solds
   where listingagentid NE ' ' and colistingagentid = ' ' )

union all

( select listingagentid    as "Agent",
         listingofficeid   as "Office",
         0.5               as "Listing Units",
         soldprice*0.5     as "Sold Price"
    from glar_solds
   where listingagentid NE ' ' and colistingagentid NE ' ' )

union all

( select colistingagentid  as "Agent",
         colistingofficeid as "Office",
         0.5               as "Listing Units",
         soldprice*0.5     as "Sold Price"
    from glar_solds
   where listingagentid NE ' ' and colistingagentid NE ' ' )

union all

( select colistingagentid  as "Agent",
         colistingofficeid as "Office",
         1                 as "Listing Units",
         soldprice         as "Sold Price"
    from glar_solds
   where listingagentid = ' ' and colistingagentid NE ' ' ))

group by "Agent", "Office"

[lbreddemann · Accepted solution]

Fred,

please either provide the full SQL statements for your example or stick with mine.

I'm not going to build it up myself a second time to suit yours now.

> But now, my issue is how do I store the resultant data in its own table?

So where is the problem?

INSERT INTO <target table> (field 1, field 2, ...) (<your query>) UDPATE DUPLICATES-

With my tables this looks like this:

create table sell_result (list_agent varchar(20) primary key, SUM_CREDIT fixed (10,2))

insert 
into sell_result (list_agent,sum_credit)
    ( select list_agent, sum (credit) as SUM_CREDIT 
      from ( select sh.object_id,lag.name as list_agent, 1 as credit 
             from soldhomes sh join agents lag on lag.id = sh.list_agent_id 
            union all 
             select sh.object_id, lag.name as list_coagent, 0.5 as credit 
             from soldhomes sh join agents lag on lag.id = sh.list_coagent_id 
            )
      group by list_agent
     ) 
update duplicates

Check what we have now

sqlcli db770=> select * from sell_result
| LIST_AGENT           | SUM_CREDIT        |
| -------------------- | ----------------- |
| Lars                 |              4.50 |
| Lisa                 |              3.00 |
| Mona                 |              2.50 |
| Paul                 |              2.50 |

4 rows selected (600 usec)

Now add some sales data...

Insert into soldhomes values (11, 1, 2, NULL, NULL)
//
Insert into soldhomes values (12, 2, NULL, NULL, NULL)
//
Insert into soldhomes values (13, 2, NULL, NULL, NULL)

Re-run the INSERT command and you're done:

sqlcli db770=> select * from sell_result
| LIST_AGENT           | SUM_CREDIT        |
| -------------------- | ----------------- |
| Lars                 |              5.50 |
| Lisa                 |              3.00 |
| Mona                 |              5.00 |
| Paul                 |              2.50 |

4 rows selected (390 usec)

Neat, isn't it?

[Former Member · Accepted solution]

Lars (and everyone else),

Thanks to your guidance, I can now declare that my problem has been completely resolved. To help others who may have been following this thread, I'm going to post all the SQL involved. (It'll be in subsequent replies.)

Lars -- you're the MAN!

Thanks,

~Fred

[Former Member · Accepted solution]

create table "FREDDIE"."GLAR_AGENTS"(
	"AGENTID" CHAR (10) ASCII not null,
	"FIRSTNAME" CHAR (15) ASCII,
	"LASTNAME" CHAR (20) ASCII,
	"AGENTMAILADDRESS" CHAR (45) ASCII,
	"AGENTMAILCITY" CHAR (15) ASCII,
	"AGENTMAILSTATE" CHAR (2) ASCII,
	"AGENTMAILZIP" CHAR (5) ASCII,
	"HOMEPHONE" CHAR (25) ASCII,
	"DIRECTOFFICEPHONE" CHAR (25) ASCII,
	"CELLPHONE" CHAR (25) ASCII,
	"BEEPERPHONE" CHAR (15) ASCII,
	"PAGER" CHAR (25) ASCII,
	"AGENTEMAIL" CHAR (40) ASCII,
	"AGENTWEBSITE" CHAR (65) ASCII,
	"OFFICEID" CHAR (10) ASCII,
constraint SYSPRIMARYKEY primary key ("AGENTID"))

create table "FREDDIE"."GLAR_OFFICES"(
	"OFFICEID" CHAR (10) ASCII not null,
	"OFFICENAME" CHAR (30) ASCII,
	"OFFICEMAILADDRESS" CHAR (45) ASCII,
	"OFFICEMAILCITY" CHAR (15) ASCII,
	"OFFICEMAILSTATE" CHAR (2) ASCII,
	"OFFICEMAILZIP" CHAR (5) ASCII,
	"OFFICEPHONE" CHAR (15) ASCII,
	"OFFICEFAX" CHAR (15) ASCII,
	"OFFICEEMAIL" CHAR (40) ASCII,
	"OFFICEIDXYN" CHAR (1) ASCII,
	"OFFICEWEBSITE" CHAR (45) ASCII,
constraint SYSPRIMARYKEY primary key ("OFFICEID"))

create table "FREDDIE"."GLAR_RPT_MARKET_PENETRATION"(
	"AGENTID" CHAR (10) ASCII not null,
	"OFFICEID" CHAR (10) ASCII not null,
	"LISTINGUNITS" FIXED (5,1),
	"LISTINGVOLUME" FIXED (9),
	"SELLINGUNITS" FIXED (5,1),
	"SELLINGVOLUME" FIXED (9),
constraint SYSPRIMARYKEY primary key ("AGENTID"))

create table "FREDDIE"."GLAR_SOLDS"(
	"MLSNUMBER" CHAR (7) ASCII not null,
	"STATUS" CHAR (1) ASCII,
	"AREA" CHAR (2) ASCII,
	"SUBAREA" CHAR (3) ASCII,
	"MAPNUMBER" CHAR (4) ASCII,
	"DOM" FIXED (4),
	"CDOM" FIXED (4),
	"CATEGORY" CHAR (11) ASCII,
	"PROPERTYTYPE" CHAR (25) ASCII,
	"STREETNUMBER" CHAR (6) ASCII,
	"STREETDIRECTION" CHAR (2) ASCII,
	"STREETNAME" CHAR (30) ASCII,
	"UNITNUMBER" CHAR (8) ASCII,
	"CITY" CHAR (20) ASCII,
	"COUNTY" CHAR (15) ASCII,
	"STATE" CHAR (2) ASCII,
	"ZIP" CHAR (5) ASCII,
	"LISTINGAGENTID" CHAR (10) ASCII,
	"LISTINGOFFICEID" CHAR (10) ASCII,
	"COLISTINGAGENTID" CHAR (10) ASCII,
	"COLISTINGOFFICEID" CHAR (10) ASCII,
	"SELLINGAGENTID" CHAR (10) ASCII,
	"SELLINGOFFICEID" CHAR (10) ASCII,
	"COSELLINGAGENTID" CHAR (10) ASCII,
	"COSELLINGOFFICEID" CHAR (10) ASCII,
	"LISTDATE" DATE,
	"SOLDDATE" DATE,
	"ORIGINALLISTPRICE" FIXED (8),
	"LISTPRICE" FIXED (8),
	"SOLDPRICE" FIXED (8),
	"TOTALLIVINGAREA" FIXED (5),
	"TAG" CHAR (1) ASCII,
constraint SYSPRIMARYKEY primary key ("MLSNUMBER"))

[Former Member · Accepted solution]

insert into glar_rpt_market_penetration
            (agentid, officeid,
            listingunits, listingvolume,
            sellingunits, sellingvolume)

( select "Agent ID",
         "Office ID",
     sum("Listing Units")           as "Listing Units",
     sum("Listing Volume")          as "Listing Volume",
     sum("Selling Units")           as "Selling Units",
     sum("Selling Volume")          as "Selling Volume"

    from (

(continues in next posting; gosh this forum's software is picky

[Former Member · Accepted solution]

(continued from previous posting)

/* Calculate Listing Units & Volume for Listing Agents      */
/* who listed without help from any Co-Listing Agent        */
           select listingagentid    as "Agent ID",
                  listingofficeid   as "Office ID",
                  1                 as "Listing Units",
                  soldprice         as "Listing Volume",
                  0                 as "Selling Units",
                  0                 as "Selling Volume"
             from glar_solds
            where listingagentid   NE ' '
              and colistingagentid =  ' '
              and tag              =  'X'

           union all

/* Calculate Listing Units & Volume for Listing Agents      */
/* who listed with help from a Co-Listing Agent             */
           select listingagentid    as "Agent ID",
                  listingofficeid   as "Office ID",
                  0.5               as "Listing Units",
                  soldprice*0.5     as "Listing Volume",
                  0                 as "Selling Units",
                  0                 as "Selling Volume"
             from glar_solds
            where listingagentid   NE ' '
              and colistingagentid NE ' '
              and tag              =  'X'

           union all

/* Calculate Listing Units & Volume for Co-Listing Agents   */
/* with listed with help from a Listing Agent               */
           select colistingagentid  as "Agent ID",
                  colistingofficeid as "Office ID",
                  0.5               as "Listing Units",
                  soldprice*0.5     as "Listing Volume",
                  0                 as "Selling Units",
                  0                 as "Selling Volume"
             from glar_solds
            where listingagentid   NE ' '
              and colistingagentid NE ' '
              and tag              =  'X'

           union all

(continued below)

[Former Member · Accepted solution]

(continued from above)

/* Calculate Listing Units & Volume for Co-Listing Agents   */
/* who listed without help from any Listing Agents          */
/* (Yeah, I know this is weird, but it apparently happens.) */
           select colistingagentid  as "Agent ID",
                  colistingofficeid as "Office ID",
                  1                 as "Listing Units",
                  soldprice         as "Listing Volume",
                  0                 as "Selling Units",
                  0                 as "Selling Volume"
             from glar_solds
            where listingagentid   =  ' '
              and colistingagentid <> ' '
              and tag              =  'X'

           union all

/* Calculate Selling Units & Volume for Selling Agents      */
/* who sold without help from any Co-Selling Agent          */
           select sellingagentid    as "Agent ID",
                  sellingofficeid   as "Office ID",
                  0                 as "Listing Units",
                  0                 as "Listing Volume",
                  1                 as "Selling Units",
                  soldprice         as "Selling Volume"
             from glar_solds
            where sellingagentid   <> ' '
              and cosellingagentid =  ' '
              and tag              =  'X'

           union all

/* Calculate Selling Units & Volume for Selling Agents      */
/* who sold with help from a Co-Selling Agent               */
           select sellingagentid    as "Agent ID",
                  sellingofficeid   as "Office ID",
                  0                 as "Listing Units",
                  0                 as "Listing Volume",
                  0.5               as "Selling Units",
                  soldprice*0.5     as "Selling Volume"
             from glar_solds
            where sellingagentid   <> ' '
              and cosellingagentid <> ' '
              and tag              =  'X'

           union all

(continued below)

[Former Member · Accepted solution]

last one!

/* Calculate Selling Units & Volume for Co-Selling Agents   */
/* who sold with help from a Selling Agent                  */
           select cosellingagentid  as "Agent ID",
                  cosellingofficeid as "Office ID",
                  0                 as "Listing Units",
                  0                 as "Listing Volume",
                  0.5               as "Selling Units",
                  soldprice*0.5     as "Selling Volume"
             from glar_solds
            where sellingagentid   <> ' '
              and cosellingagentid <> ' '
              and tag              =  'X' )

   group by "Agent ID", "Office ID" ) update duplicates

I'm still uncertain why that "update duplicates" modifier had to be there, but it wouldn't work without it.

Thanks again for your help Lars.

~Fred

[Former Member · Accepted solution]

btw, for those folks playing along at home, note that I forgot to replace my less-than-greater-than signs with "NE" in those last couple of postings. If you see an operator missing, just pop "NE" in its place. Thanks.