[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????

'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