I think you need something like this:
>CREATE VIEW vsvcsum2 (casenum, client,sid,staff,servcode,costcent,+
> unitsum) +
> AS SELECT t1.ypid, (max((t1.lname + ', ' + t1.fname)),t2.sid, +
> (max((t2.lname + ', ' + t2.fname)), t3.servcode,t3.costcent,(SUM(t3.unit)) +
> FROM yp t1, stbasic t2, service t3 +
> WHERE t1.ypid = t3.ypid AND t2.sid = t3.sid +
> GROUP BY t1.ypid,t1.lname , ,t2.sid,+
> t3.servcode,t3.costcent
If the ypid is unique for each lname and fname, you do not need the lname
and fname in the group by.
Troy Sosamon
===== Original Message from [EMAIL PROTECTED] at 7/13/01 9:03 am
>Can anyone tell me what I can do to make the following work? I get a BAD
>GROUP BY error.
>
>CREATE VIEW vsvcsum2 (casenum, client,sid,staff,servcode,costcent,+
> unitsum) +
> AS SELECT t1.ypid,(t1.lname + ', ' + t1.fname),t2.sid, +
> (t2.lname + ', ' + t2.fname), t3.servcode,t3.costcent,(SUM(t3.unit)) +
> FROM yp t1, stbasic t2, service t3 +
> WHERE t1.ypid = t3.ypid AND t2.sid = t3.sid +
> GROUP BY t1.ypid,(t1.lname + ', ' + t1.fname),t2.sid,+
> (t2.lname + ', ' + t2.fname),t3.servcode,t3.costcent
>
>I ran the same thing without concatenating the names and it worked, so I
>assume that is the problem, however, can it be done in one pass this way?
>I'm only concatenating to save vars in the reports.
>
>tia
>
>Dave
>[EMAIL PROTECTED]
