Thanks Bill. This is way over my head in comprehension so I'll do as you suggest and do it the first way which is the simplest and apparently the safest.
> -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Downall > Sent: Thursday, January 27, 2005 9:18 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - Re: Temporary Views > > Claudine, > > There are two techniques for this. One is to create a slightly more > complicated view, the other is to build your variable into the view > itself. > > If the view definition is: > > SELECT da_invoice_dt, billing_id, SUM (billing_amt) + > FROM d_b_allocation + > GROUP BY da_invoice_dt, billing_id > > Then every PRINT or other command must include the WHERE clause to find > the right date. > > If the view definition is: > > SET VAR vDate DATE = .#DATE > SELECT billing_id, SUM (billing_amt) + > FROM d_b_allocation + > WHERE da_invoice_dt = .vdate + > GROUP BY da_invoice_dt, billing_id > > The view will actually be saved with the VALUE of vdate, not the > variable. So don't do it this way. > > If you do: > > SET VAR vDate DATE = .#DATE > SELECT billing_id, SUM (billing_amt) + > FROM d_b_allocation + > WHERE da_invoice_dt = (.vdate) + > GROUP BY da_invoice_dt, billing_id > > The view will be saved with the variable, but every time you use it, it > will not even try to use an index on da_invoice_dt. So don't do it this > way. > > If you do this: > > SET VAR vDate DATE = .#DATE > SELECT billing_id, SUM (billing_amt) + > FROM d_b_allocation + > WHERE (da_invoice_dt = .vdate) + > GROUP BY da_invoice_dt, billing_id > > The view will save correctly, and will use indexes correctly, but will > cause weird errors sometimes (like when you are using the Data > Dictionary F3 and arrowing through the list of view names: this one will > cause an error whenever vdate is undefined.) > > So use the top method, or the bottom method. > > Bill > > > > > claudinerobbins wrote: > > >Speaking of views, I have a slight problem with the following: > > > >SELECT t40.billing_id, SUM(t40.billing_amt) FROM d_b_allocation t40 + > >GROUP BY t40.billing_id > > > >Works fine but I want to do this only for selected records in the table > > > >where da_invoice_dt = .vdate > > > >I've tried putting a where clause or a having clause in different places > to > >no avail. > > > >What am I missing? > > > >TIA, Claudine :) > > > >-----Original Message----- > >From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Albert > Berry > >Sent: Thursday, January 27, 2005 6:18 PM > >To: RBG7-L Mailing List > >Subject: [RBG7-L] - Re: Temporary Views > > > >The advantage of the temporary view is particularly noticeable when views > >are used for reporting, > >and more than one user needs a slightly modified view from another user > who > >is reporting at the > >same time, but needing a different data set returned. The temporary view > is > >only visible for the > >user who created it, and lasts only for the session. > > > > > > > >--- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > > > > >>Happy almost weekend!!! > >> > >>I have a quick question regarding the advantages and/or disadvantages of > >> > >> > >using create temporary > > > > > >>view as opposed to the regular create view commmand? > >> > >>Thank you... > >> > >>Tom Eldred > >> > >> > >> > >> > >> > >> > >>________________________________________________________________ > >>Sent via the WebMail system at localroads.net > >> > >> > >> > >> > >> > >> > >> > >> > > > > > >===== > >Albert Berry > >Management Consultant > >RR2 - 1252 Ponderosa Drive > >Sparwood BC, V0B 2G2 > >Canada > >(250) 425-5806 > >(250) 425-7259 > >(708) 575-3952 (fax) > >[EMAIL PROTECTED] > > > > > > > >
