RE: [sqr-users] Re: sqr-users Digest, Vol 8, Issue 7

["George Jansen" <GJANSEN@aflcio.org>]

Just my two cents: Any of the three base queries reduce to

SELECT min(effdt)
FROM ps_something
GROUP BY keyfield1, keyyfield2, ... keyfield_n

and, since you're then comparing maximums, you might as well

SELECT max(min(effdt)
FROM ps_something
GROUP BY ...

Or, ultimately

SELECT Greatest(com.effdt, pay.effdt, loc.effdt) as max_min_effdt
FROM
  (SELECT max(min(effdt)) AS effdt
   FROM ps_company_tbl com
   WHERE effdt <= Sysdate
   GROUP BY company) com,
  (SELECT max(min(effdt)) AS effdt
   FROM ps_paygroup_tbl
   WHERE effdt <= Sysdate
   GROUP BY company, paygroup) pay,
  (SELECT max(min(effdt)) AS effdt
   FROM ps_location_tbl
   WHERE effdt <= Sysdate
   GROUP BY location) loc;

(My PS_LOCATION_TBL, in a now-abandoned system, has no COMPANY
column.)

For small tables, the gain in efficiency is negligible, but I at least
find the SQL clearer.




>>> debbie.larney@thicare.com 10/13/2004 8:28:46 AM >>>
Neil,

That is a great idea. Thank you so much. I will definitely use it.

Deb

-----Original Message-----
From: Neal Patterson [mailto:rnpatter@colby.edu] 
Sent: Tuesday, October 12, 2004 9:40 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Re: sqr-users Digest, Vol 8, Issue 7


If Debbie wants an SQL approach to picking the maximum if her 3 dates,

maybe something like this would do:


select max(MyDate) from

(select com.effdt as MyDate
from ps_company_tbl com
where com.effdt = (select
        min(inner.effdt)
      from ps_company_tbl inner
      where inner.company = com.company and
            inner.effdt  <= sysdate)
union

select pay.effdt as MyDate
from ps_paygroup_tbl pay
where pay.effdt = (select
        min(inner.effdt)
      from ps_paygroup_tbl inner
      where inner.company = pay.company and
      and inner.paygroup = pay.paygroup
            inner.effdt  <= sysdate)
union

select loc.effdt as MyDate
from ps_location_tbl loc
where loc.effdt = (select
        min(inner.effdt)
      from ps_location_tbl inner
      where inner.company = loc.company and
      and inner.location = loc.location
            inner.effdt  <= sysdate)) as T

- Neal Patterson
-----------------------------------------------------------------------
At 06:03 AM 10/12/2004 -0500, you wrote:
>Send sqr-users mailing list submissions to
>         sqr-users@sqrug.org 
>
>To subscribe or unsubscribe via the World Wide Web, visit
>         http://www.sqrug.org/mailman/listinfo/sqr-users 
>or, via email, send a message with subject or body 'help' to
>         sqr-users-request@sqrug.org 
>
>You can reach the person managing the list at
>         sqr-users-owner@sqrug.org 
>
>When replying, please edit your Subject line so it is more specific
>than "Re: Contents of sqr-users digest..."
>
>
>Today's Topics:
>
>    1. Max effective date evaluation not in sql (Debbie Larney)
>    2. RE: Max effective date evaluation not in sql (Knapp, Richard)
>    3. ISOLATION LEVEL (Scott Eubank)
>
>
>----------------------------------------------------------------------
>
>Message: 1
>Date: Mon, 11 Oct 2004 14:24:09 -0400
>From: "Debbie Larney" <debbie.larney@thicare.com>
>Subject: [sqr-users] Max effective date evaluation not in sql
>To: <sqr-users@sqrug.org>
>Message-ID:
>         <768DB1A57CCF794A9A6F7288B66D8F6563A8E1@HAMMERHEAD.ltc.com>
>Content-Type: text/plain; charset="iso-8859-1"
>
>Everyone,
>
>I have a simple issue that I can't seem to work out. I need to
determine >the max effective dated row from the minimum row returned in
three 
>different select statements.
>Here are my sample select statements
>
>select com.effdt
>from ps_company_tbl com
>where com.effdt = (select
>        min(inner.effdt)
>      from ps_company_tbl inner
>      where inner.company = com.company and
>            inner.effdt  <= sysdate)
>
>select pay.effdt
>from ps_paygroup_tbl pay
>where pay.effdt = (select
>        min(inner.effdt)
>      from ps_paygroup_tbl inner
>      where inner.company = pay.company and
>      and inner.paygroup = pay.paygroup
>            inner.effdt  <= sysdate)
>
>select loc.effdt
>from ps_location_tbl loc
>where loc.effdt = (select
>        min(inner.effdt)
>      from ps_location_tbl inner
>      where inner.company = loc.company and
>      and inner.location = loc.location
>            inner.effdt  <= sysdate)
>
>Now that I have loc.effdt, pay.effdt and com.effdt how do I determine

>which is the max effective dated row.
>
>Thanks in advance
>
>Deb
>
>
>
>********************************************************************
>CONFIDENTIALITY NOTICE:
>The information contained in this e-mail is legally privileged and 
>confidential information intended only for the use of the individual
or 
>entity to whom it is addressed.  If the reader of this message is not
the >intended recipient, you are hereby notified that any viewing, 
>dissemination, distribution, or copying of this e-mail message is
strictly >prohibited.  If you have received and/or are viewing this
e-mail in error, 
>please immediately notify the sender by reply e-mail, and delete this

>e-mail from your system.
>
>Thank you.
>********************************************************************
>
>
>
>
>------------------------------
>
>Message: 2
>Date: Mon, 11 Oct 2004 13:52:59 -0500
>From: "Knapp, Richard" <KnappR@umsystem.edu>
>Subject: RE: [sqr-users] Max effective date evaluation not in sql
>To: "This list is for discussion about the SQR database
>         reportinglanguage from  Hyperion Solutions."
<sqr-users@sqrug.org>
>Message-ID:
>        
<C3A8BF882E6B604B8DFA11DDD25D2226898FAF@UM-EMAIL05.um.umsystem.edu>
>Content-Type: text/plain;       charset="US-ASCII"
>
>
>A crude bubble sort?  If A > B
>                          if A > C
>                              it's A
>                          end-if
>                       else
>                          if B > C
>                             it's B
>                          else
>                             it's C
>                          end-if
>                       end-if
>
>Richard Knapp
>Database Programmer/Analyst
>Institutional Research and Planning
>University of Missouri System
>573-882-8856
>knappr@umsystem.edu 
>
>
>-----Original Message-----
>From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org 
>[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
>Debbie Larney
>Sent: Monday, October 11, 2004 1:24 PM
>To: sqr-users@sqrug.org 
>Subject: [sqr-users] Max effective date evaluation not in sql
>
>Everyone,
>
>I have a simple issue that I can't seem to work out. I need to
determine
>the max effective dated row from the minimum row returned in three
>different select statements.
>Here are my sample select statements
>
>select com.effdt
>from ps_company_tbl com
>where com.effdt = (select
>        min(inner.effdt)
>      from ps_company_tbl inner
>      where inner.company = com.company and
>            inner.effdt  <= sysdate)
>
>select pay.effdt
>from ps_paygroup_tbl pay
>where pay.effdt = (select
>        min(inner.effdt)
>      from ps_paygroup_tbl inner
>      where inner.company = pay.company and
>      and inner.paygroup = pay.paygroup
>            inner.effdt  <= sysdate)
>
>select loc.effdt
>from ps_location_tbl loc
>where loc.effdt = (select
>        min(inner.effdt)
>      from ps_location_tbl inner
>      where inner.company = loc.company and
>      and inner.location = loc.location
>            inner.effdt  <= sysdate)
>
>Now that I have loc.effdt, pay.effdt and com.effdt how do I determine
>which is the max effective dated row.
>
>Thanks in advance
>
>Deb
>
>
>
>********************************************************************
>CONFIDENTIALITY NOTICE:
>The information contained in this e-mail is legally privileged and
>confidential information intended only for the use of the individual
or
>entity to whom it is addressed.  If the reader of this message is not
>the intended recipient, you are hereby notified that any viewing,
>dissemination, distribution, or copying of this e-mail message is
>strictly prohibited.  If you have received and/or are viewing this
>e-mail in error, please immediately notify the sender by reply
e-mail,
>and delete this e-mail from your system.
>
>Thank you.
>********************************************************************
>
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org 
>http://www.sqrug.org/mailman/listinfo/sqr-users 
>
>
>
>------------------------------
>
>Message: 3
>Date: Mon, 11 Oct 2004 16:05:47 -0400
>From: Scott Eubank <SEUBANK@advance-auto.com>
>Subject: [sqr-users] ISOLATION LEVEL
>To: 'SQRUG' <sqr-users@sqrug.org>
>Message-ID:
> 
><4B25267A624F0F46AAAF8F42CB62863102A5DF98@exchange2k2.corp.advancestores.com>
>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>
>  I need to do a "dirty read",  basically an uncommited read for a
report so
>that I do not disrupt processes that are running at nite.   How do I
>"change isolation Level UR" in an SQR for the entire SQR.
>
>   AIX, DB2 , PSOFT-Tools 7.59
>
>Any help would be greatly appreciated.
>
>
>Scott Ebank
>
>
>
>===================================================================
>NOTICE - CONFIDENTIAL AND PRIVILEGED - This e-mail may contain
>privileged and confidential information and is intended only for the
>addressee named above. If you received this message in error,
>please immediately notify the sender by return e-mail and delete the
>original message; any distribution, copying or use of this e-mail
>by you is strictly prohibited and may be unlawful.
>
>
>
>------------------------------
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org 
>http://www.sqrug.org/mailman/listinfo/sqr-users 
>
>
>End of sqr-users Digest, Vol 8, Issue 7
>***************************************

-------------------------------------------------------------------
              Neal Patterson, Sr. Programmer/Analyst
     College Relations Information Systems Services, Colby College
        4333 Mayflower Hill Drive, Waterville, ME 04901-8843
       Phone: (207)872-3419               E-mail: rnpatter@colby.edu 
-------------------------------------------------------------------





********************************************************************
CONFIDENTIALITY NOTICE:
The information contained in this e-mail is legally privileged and
confidential information intended only for the use of the individual or
entity to whom it is addressed.  If the reader of this message is not
the intended recipient, you are hereby notified that any viewing,
dissemination, distribution, or copying of this e-mail message is
strictly prohibited.  If you have received and/or are viewing this
e-mail in error, please immediately notify the sender by reply e-mail,
and delete this e-mail from your system.

Thank you.
********************************************************************


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users