WOW!!!
thanks for confirming/clarifying my understanding Albert ...
and thank you to Claudine and Bill for expanding it.
I <<gulp>> am actually considering a presentation at the conference at this point....some one smack me quick before i committ to it!
Happy Friday, hope you all have nice relaxing weekends scheduled
Tom Eldred
----- Original Message ----- From: "Claudine Robbins" <[EMAIL PROTECTED]>
To: "RBG7-L Mailing List" <[email protected]>
Sent: Thursday, January 27, 2005 11:27 PM
Subject: [RBG7-L] - Re: Temporary Views
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]
>
>
>
>
