[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
[sqr-users] Re: sqr-users Digest, Vol 8, Issue 7

Subject: [sqr-users] Re: sqr-users Digest, Vol 8, Issue 7
From: Neal Patterson <rnpatter@colby.edu>
Date: Tue, 12 Oct 2004 09:39:55 -0400
Delivery-date: Tue, 12 Oct 2004 08:42:24 -0500
In-reply-to: <E1CHKRW-0001YG-02@seldon.sqrug.org>
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
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
-------------------------------------------------------------------


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
Prev by Date: [sqr-users] ISOLATION LEVEL
Next by Date: [sqr-users] Data truncated during update from flat file
Previous by thread: [sqr-users] ISOLATION LEVEL
Next by thread: RE: [sqr-users] Re: sqr-users Digest, Vol 8, Issue 7
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page