[RBG7-L] - Re: Temporary Views

[Bill Downall]

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: rbg7-l@rbase.com [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]