[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
RE: [sqr-users] What is wrong????

Subject: RE: [sqr-users] What is wrong????
From: "Knapp, Richard" <KnappR@umsystem.edu>
Date: Mon, 18 Oct 2004 11:24:43 -0500
Delivery-date: Mon, 18 Oct 2004 11:25:52 -0500
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
Thread-index: AcS1J8A42u33xPXgS0eocIgdzvp3uQAAeYiQAACl3UAAAFiEcA==
Thread-topic: [sqr-users] What is wrong????

I think either I am not getting what you want or your use of and and or
is not correct.

For the purposes of discussion, I am referring to the two parts of your
example as case 1 and case 2.

In the first case, can a person have two campuses and two acad_progs?
Or is it that the persons you want have either of two campuses and
either of two degrees?  In which case you need to replace the internal
ands with ors.

Looking at both cases is it true that you want all persons who are at
any of the campuses you mention (in both cases)  and then you want to
sort them by what acad_prog their in based on their campus?  If that is
the case, your query should just get the campus and acad_prog for all
the campuses on you lists then test the campus ( 'if &campus =' or
'evaluate &campus') within the query and act accordingly for each group
of acad_progs.

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
Lindquist, Maria
Sent: Monday, October 18, 2004 11:07 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] What is wrong????

  (X and Y) or ( Z and W) 

Well in other words I want it look select students according to the
following:

Campus = MTH and MSF 
where
Acad Prog = NON and DEG

And 

Campus = 'BRB' or 'BRN' or 'FDL' or 'MAN' or 'RLN'
Where 
AcadProg = DEG

Did I not code it correctly??????

Maria K Lindquist
mlindqui@uwc.edu
IS Programmer/Analyst - Business Office
University of Wisconsin Colleges
780 Regent St, PO Box 8680, Madison, WI  53708-8680
Phone: 608.265.2677 / Fax: 608.265.5770

-----Original Message-----
From: sqr-users-bounces+mlindqui=uwc.edu@sqrug.org
[mailto:sqr-users-bounces+mlindqui=uwc.edu@sqrug.org] On Behalf Of Murty
Mirthipati Venkata Suryanarayan
Sent: Monday, October 18, 2004 10:57 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] What is wrong????

let me put it another way. This is rather a logical problem, I suppose.
The way the logical operand's are arranged could be revisited.
 
 I suggest you better re-check your criterion:?
 is it   (X and Y) or ( Z and W)
 or   X and (Y or Z ) and W
 or As you have specified,  X and Y or Z or W

 Cheers,
Murty MVS








-----Original Message-----
From: David Thorburn [mailto:thorburn@conl.net]
Sent: Saturday, October 16, 2004 12:52 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: Re: [sqr-users] What is wrong????


'IN' is very 'costly' coding.

OR's are processed faster.

Best Regards,

David
__________________________________________________

David Otis Thorburn                                   (916) 712-2272
 thorburn@conl.net      www.geocities.com/david_thorburn
__________________________________________________
----- Original Message -----
From: "Lindquist, Maria" <mlindqui@uwc.edu>
To: "This list is for discussion about the SQR database
reportinglanguage fromHyperion Solutions." <sqr-users@sqrug.org>
Sent: Friday, October 15, 2004 12:04 PM
Subject: RE: [sqr-users] What is wrong????


Well I don't know what my problem is today but I just did this in the
main process

  AND (C.ACAD_PROG IN ('DEG', 'NON')
  AND C.CAMPUS IN ('MTH','MSF')
  OR  C.ACAD_PROG IN ('DEG')
  AND C.CAMPUS IN ('BRB','BRN','FDL','MAN','RLN'))

And that did exactly what it was supposed to. Having lunch and listening
to music on my headphones must have helped.


Maria K Lindquist
mlindqui@uwc.edu
IS Programmer/Analyst - Business Office
University of Wisconsin Colleges
780 Regent St, PO Box 8680, Madison, WI  53708-8680
Phone: 608.265.2677 / Fax: 608.265.5770

-----Original Message-----
From: sqr-users-bounces+mlindqui=uwc.edu@sqrug.org
[mailto:sqr-users-bounces+mlindqui=uwc.edu@sqrug.org] On Behalf Of Les
Hancock
Sent: Friday, October 15, 2004 12:06 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: Re: [sqr-users] What is wrong????

$Campus will not be defined until rows are returned.

And then you evaluate the value of $Campus and set up the variables used
in the Where clause ($AcadProgSel, $CampusSel).

So, going into the Select, $AcadProgSel and $CampusSel will be empty and
not used.

It appears to me that you will need 2 or more separate select statements
to do what you want to do.


Les



--- "Lindquist, Maria" <mlindqui@uwc.edu> wrote:

> I don't know what I'm doing wrong in my SQR but it keeps turning on
> the checkflag on the equation variable on students that are Non Degree

> for certain campuses when I do not ask for them. I must be having a
> major brain freeze today because I can't see it. Any suggestions on
> the below?
>
>
!*******************************************************
> Begin-Procedure Process-Main
>
!*******************************************************
> #Debugp Show 'Process-Main Procedure'
> Show 'Entering Process-Main'
>
>    Let #Total_New_Freshman = 0
>
> Begin-Select
> A.EMPLID
> A.ACAD_CAREER
> E.NAME
> C.CAMPUS
>
>    Let $Campus = &C.CAMPUS
>
>    Let $Emplid = &A.EMPLID
>    Let $Billing_Career = &A.ACAD_CAREER
>    Let $Name = &E.Name
>
>
>    Do Evaluate-Campus
>
>    Show 'Emplid      = ' $Emplid
>
>    Let $Flag_Exists = 'N'
>    Let $Row_Exists = 'N'
>    Do Check-Flag
>    Do Check-Prev-Flag
>
>    If $Row_Exists = 'N'
>       Do Insert-Flag
>       Add 1 to #Total_New_Freshman
>    Else
>       If $Flag_Exists = 'N'
>          Do Update-Flag
>          Add 1 to #Total_New_Freshman
>       End-If
>    End-If
>
>    Do Update-Tuit-Calc
>
> FROM PS_STDNT_CAR_TERM A,
>      PS_ADM_APPL_DATA B,
>      PS_ADM_APPL_PROG C,
>      PS_PERSONAL_DATA E
> WHERE A.INSTITUTION = $Institution
>   AND A.STRM = $Strm
>   AND A.ACAD_LEVEL_BOT = '10'
>   AND B.INSTITUTION = A.INSTITUTION
>   AND C.INSTITUTION = B.INSTITUTION
>   AND B.EMPLID = A.EMPLID
>   AND C.EMPLID = B.EMPLID
>   AND C.ADMIT_TERM = A.STRM
>   AND B.ACAD_CAREER = A.ACAD_CAREER
>   AND C.ACAD_CAREER = B.ACAD_CAREER
>   AND B.STDNT_CAR_NBR = A.STDNT_CAR_NBR
>   AND C.STDNT_CAR_NBR = B.STDNT_CAR_NBR
>   AND C.ADM_APPL_NBR = B.ADM_APPL_NBR
>   AND C.EFFDT = (SELECT MAX(C2.EFFDT) FROM
> PS_ADM_APPL_PROG C2
>                  WHERE C2.EMPLID = C.EMPLID
>                    AND C2.ACAD_CAREER =
> C.ACAD_CAREER
>                    AND C2.STDNT_CAR_NBR =
> C.STDNT_CAR_NBR
>                    AND C2.ADM_APPL_NBR =
> C.ADM_APPL_NBR
>                    AND C2.INSTITUTION =
> C.INSTITUTION
>                    AND C2.EFFDT <=
> {DateIn-Prefix}$AsOfToday{DateIn-Suffix})
>   AND C.EFFSEQ = (SELECT MAX(C3.EFFSEQ) FROM
> PS_ADM_APPL_PROG C3
>                   WHERE C3.EMPLID = C.EMPLID
>                     AND C3.ACAD_CAREER =
> C.ACAD_CAREER
>                     AND C3.STDNT_CAR_NBR =
> C.STDNT_CAR_NBR
>                     AND C3.ADM_APPL_NBR =
> C.ADM_APPL_NBR
>                     AND C3.INSTITUTION =
> C.INSTITUTION
>                     AND C3.EFFDT = C.EFFDT)
>   AND B.ADMIT_TYPE = 'NEW'
>   [$AcadProgSel]
>   [$CampusSel]
> !  AND C.CAMPUS = $Campus
> !  AND NOT EXISTS (SELECT 'X' FROM
> PS_STDNT_EQUTN_VAR D
> - MOD - MLINDQUIST 4/19/04
> !                  WHERE D.EMPLID = A.EMPLID
> - MOD - MLINDQUIST 4/19/04
> !                    AND D.BILLING_CAREER =
> A.ACAD_CAREER
> - MOD - MLINDQUIST 4/19/04
> !                    AND D.STRM <= A.STRM
> - MOD - MLINDQUIST 4/19/04
> !                    AND D.VARIABLE_FLAG3 = 'Y')
> - MOD - MLINDQUIST 4/19/04
>   AND E.EMPLID = A.EMPLID
> ORDER BY E.NAME, A.EMPLID
> End-Select
>
> Show '<Exiting Process-Main'
> End-Procedure Process-Main
>
>
!*******************************************************
> Begin-Procedure Evaluate-Campus
>
!*******************************************************
> #Debugp Show 'Evaluate-Campus'
> Show 'Entering Evaluate-Campus'
>
> Evaluate $Campus
>
>         When = 'BRB'
>           Let $AcadProgSel = 'AND C.ACAD_PROG IN
> (''DEG'')'
>           Let $CampusSel  = 'AND C.CAMPUS IN
> (''BRB'')'
>
>
>         When = 'BRN'
>           Let $AcadProgSel = 'AND C.ACAD_PROG IN
> (''DEG'')'
>           Let $CampusSel  = 'AND C.CAMPUS IN
> (''BRN'')'
>
>         When = 'FDL'
>           Let $AcadProgSel = 'AND C.ACAD_PROG IN
> (''DEG'')'
>           Let $CampusSel  = 'AND C.CAMPUS IN
> (''FDL'')'
>
>         When = 'MAN'
>           Let $AcadProgSel = 'AND C.ACAD_PROG IN
> (''DEG'')'
>           Let $CampusSel  = 'AND C.CAMPUS IN
> (''MAN'')'
>
>         When = 'MTH'
>           Let $AcadProgSel = 'AND C.ACAD_PROG IN
> (''DEG'',''NON'')'
>           Let $CampusSel  = 'AND C.CAMPUS IN
> (''MTH'')'
>
>         When = 'MSF'
>           Let $AcadProgSel = 'AND C.ACAD_PROG IN
> (''DEG'',''NON'')'
>           Let $CampusSel  = 'AND C.CAMPUS IN
> (''MSF'')'
>
>         When = 'RLN'
>           Let $AcadProgSel = 'AND C.ACAD_PROG IN
> (''DEG'')'
>           Let $CampusSel  = 'AND C.CAMPUS IN
> (''RLN'')'
>
>         When-Other
>            Let $AcadProgSel = 'AND C.ACAD_PROG IN
> (''DEG'',''NON'')'
> !          Let $CampusSel  = 'AND C.CAMPUS IN
> (''BRB'')'
>
>
> End-Evaluate
>
>    Show 'Campus      = ' $Campus
>    Show 'AcadProgSel = ' $AcadProgSel
>
>
> Show '< Exiting Evaluate-Campus'
> End-Procedure Evaluate-Campus
>
> ________________________________
>
> Maria K Lindquist
> mlindqui@uwc.edu <mailto:mlindqui@uwc.edu>
> IS Programmer/Analyst - Business Office
> University of Wisconsin Colleges
> 780 Regent St, PO Box 8680, Madison, WI  53708-8680
> Phone: 608.265.2677 / Fax: 608.265.5770
>  <http://www.uwc.edu/images/logo_large.gif>
>
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>




_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

_______________________________________________
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




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


DISCLAIMER:
Information contained and transmitted by this E-MAIL is proprietary to
Hexaware Technologies and is intended for use only by the individual or
entity to which it is addressed and may contain information that is
privileged, confidential or exempt from disclosure under applicable law.
If this is a forwarded message, the content of this E-MAIL may not have
been sent with the authority of the Company. If you are not the intended
recipient, an agent of the intended recipient or a person responsible
for delivering the information to the named recipient, you are notified
that any use, distribution, transmission, printing, copying or
dissemination of this information in any way or in any manner is
strictly prohibited. If you have received this communication in error,
please delete this mail & notify us immediately at
adminchn@hexaware.com.

_______________________________________________
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

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
Prev by Date: RE: [sqr-users] What is wrong????
Next by Date: RE: [sqr-users] What is wrong????
Previous by thread: RE: [sqr-users] What is wrong????
Next by thread: RE: [sqr-users] What is wrong????
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page