Dan,
Sami's warning about not having break points that duplicate your
group by logic makes a lot of sense.
If you haven't already, put your view definition into a command file
so you can drop and redefine easily.
> sel *,salesrepid
I don't see why this would be a problem, but since salesrepid is
already part of the * specifier, why duplicate it? Try without.
Using aliases keeps things clean and seems to avoid many
problems, so you might give that a try next, and this will help you
further down the road.
Last, start paring down SherrieStats...ie.. eliminate the outer join,
then some of the stats, etc... until it works, so you can identify
what needs to be adjusted.
If all that fails (or before, if you like) post the table structures
Clients, SalesRepAccounts and Orders
Ben Petersen
On 24 Sep 2001, at 17:14, Dan Weissmann wrote:
> Thanks Ben. Here goes:
>
> View: sherriescusts
> sel *,salesrepid from clients t1,SalesRepAccounts t2 where t2.clientid =
> t1.clientid and salesrepid = 2000
>
> View: SherrieStats (client, ytd, lastordate, totaljobs)
>
> sel t1.bclient,sum (t2.charge),max (t2.donedate),count (t2.orderid) from
> sherriescusts t1 full outer join orders t2 on t1.clientid = t2.clientid AND
> t2.cancelled = 0 group by t1.bclient
>
> The view seems to work fine as a select but it crashes big time when I run
> it through Report. There are no variables in the report, everything is
> pretty straightforward.
>
> Dan
>
> ----- Original Message -----
> From: "Ben Petersen" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, September 24, 2001 9:02 AM
> Subject: Re: Strange problem with a view
>
>
> > Dan,
> >
> > If you post your view maybe someone will catch something.
> >
> > How many tables does the view address?
> >
> > I use views extensively. Nearly all use aliases, and it makes no
> > difference if queried from the R> or a report. I have, on occasion,
> > had to invest some time in the more complex views to optimize
> > them.
> >
> > Ben Petersen
> >
> > On 24 Sep 2001, at 15:39, Texmaster Express wrote:
> >
> > > Good afternoon.
> > >
> > > I finally did my first outer join view (thanks Dr. R>), and have noticed
> > > some strange behaviour when running the same view off a report. I'm
> > > wondering if I'm trying to make RBase do something it wasn't meant to
> do.
> > >
> > > My view runs fine at the R> prompt. When run through Reports, it builds
> up
> > > the .$$$ sort file until I have to do a three finger salute and close
> down
> > > RBase. However, if I recreate the view (which uses several functions,
> such
> > > as MAX(t2.charge), etc) without naming the columns (Create View as
> (Maximum)
> > > MAX(t2.charge), it runs just fine. Unfortunately, the calc'd fields
> won't
> > > display in the report, only the group by column, so I do need to do it
> the
> > > first way.
> > >
> > > Any idea why it crashes the sort file through Report but not in a Select
> via
> > > the R> prompt?
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> > >
> >
> >
> >
>
>
