[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: "George Jansen" <GJANSEN@aflcio.org>
Date: Tue, 19 Oct 2004 08:10:10 -0400
Cc:
Delivery-date: Tue, 19 Oct 2004 07:11:49 -0500
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
Why not set up a table, somewhere, called something like
PROGRAMS_OF_INTEREST, with two columns, namely CAMPUS and ACAD_PROGRAM.
Then your SQL becomes

(campus, acad_program) IN
   (SELECT campus, acad_program
    FROM programs_of_interest)

Not only is the SQL less convoluted, it is generally better to put such
business rules in a database table rather than down in a a procedure of
a report program.

>>> mlindqui@uwc.edu 10/18/2004 12:07:23 PM >>>
  (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: [sqr-users] Fw: Problem with special characters
Next by Date: [sqr-users] reporting specs
Previous by thread: RE: [sqr-users] What is wrong????
Next by thread: [sqr-users] FTP from SQR.
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page