Re[2]: [sqr-users] Nesting logic in a SELECT STATEMENT

[Bob.Melosi@edwardjones.com]

Do this:

        IF $EMPLID = '0'   
               $where =     'and p1.emplid = $EmplID'
        ELSE
               $where =     'And J1.Company = $Company'
        END-IF

Then add the [$where] to sql statement as follows.

 WHERE P1.EMPLID = E1.EMPLID AND
       N1.EMPLID = P1.EMPLID AND
       N1.NATIONAL_ID <> ' ' AND
       J1.EMPLID = E1.EMPLID AND
       J1.EMPL_RCD = E1.EMPL_RCD AND
       C1.COMPANY = J1.COMPANY AND
       C1.EFFDT = (SELECT MAX(C6.EFFDT)
                  FROM PS_COMPANY_TBL C6
                  WHERE C6.COMPANY = C1.COMPANY AND
                   C6.EFFDT <= SYSDATE)  AND
              J1.EFFDT =
         (SELECT MAX(J6.EFFDT) FROM PS_JOB J6
         WHERE J1.EMPLID = J6.EMPLID
           AND J1.EMPL_RCD = J6.EMPL_RCD
           AND J6.EFFDT <= SYSDATE)
  
       AND J1.EFFSEQ =
         (SELECT MAX(JA_ES.EFFSEQ) FROM PS_JOB JA_ES
         WHERE J1.EMPLID = JA_ES.EMPLID
           AND J1.EMPL_RCD = JA_ES.EMPL_RCD
          AND J1.EFFDT = JA_ES.EFFDT) 
          AND J1.EMPL_STATUS IN ('A','L','P')    
         [ $where ] 
    and J1.Empl_type = 'S'
  ORDER BY C1.COMPANY,P1.LAST_NAME_SRCH





> How about:
> 
>        AND (( $EMPLID = '0'   
>                   and p1.emplid = $EmplID)
>       OR  ($EMPLID <> '0'  
>            And J1.Company = $Company))
> 
> 
> 
> 
> Jeffrey K. Bedell
> Senior Systems Analyst
> Information Systems
> Syracuse University
> (315) 443-9273
> 
> >>> Sschuster@horizonnr.com 10/31/03 08:35AM >>>
> Is there any way of nesting conditional login withing the WHERE clause of a
> SELECT statement in SQR.
>  
> When I try the example below I get errors.
>  
> Please see the example below:
>  
> WHERE P1.EMPLID = E1.EMPLID AND
>       N1.EMPLID = P1.EMPLID AND
>       N1.NATIONAL_ID <> ' ' AND
>       J1.EMPLID = E1.EMPLID AND
>       J1.EMPL_RCD = E1.EMPL_RCD AND
>       C1.COMPANY = J1.COMPANY AND
>       C1.EFFDT = (SELECT MAX(C6.EFFDT)
>                  FROM PS_COMPANY_TBL C6
>                  WHERE C6.COMPANY = C1.COMPANY AND
>                   C6.EFFDT <= SYSDATE)  AND
>              J1.EFFDT =
>         (SELECT MAX(J6.EFFDT) FROM PS_JOB J6
>         WHERE J1.EMPLID = J6.EMPLID
>           AND J1.EMPL_RCD = J6.EMPL_RCD
>           AND J6.EFFDT <= SYSDATE)
>  
>       AND J1.EFFSEQ =
>         (SELECT MAX(JA_ES.EFFSEQ) FROM PS_JOB JA_ES
>         WHERE J1.EMPLID = JA_ES.EMPLID
>           AND J1.EMPL_RCD = JA_ES.EMPL_RCD
>          AND J1.EFFDT = JA_ES.EFFDT) 
>          AND J1.EMPL_STATUS IN ('A','L','P')    
>  
>        ******************** HERE IS WHERE I WANT TO PUT LOGIC
> ****************************
>        IF $EMPLID = '0'   
>                   and p1.emplid = $EmplID
>        ELSE
>            And J1.Company = $Company
>        END-IF
>        ******************** END OF LOGIC
> *************************************************************
>  
>    and J1.Empl_type = 'S'
>  ORDER BY C1.COMPANY,P1.LAST_NAME_SRCH
>  
>  
> Thanks,
> Stephen
>  
>  
> 
> _______________________________________________
> 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

Bob Melosi  x5-8427

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