RE: [sqr-users] Probelm with SQR procedure

[Bob Stone <bstone@fastenal.com>]

i'm not positive if it's an sqr requirement, or just the way I program, but
dont the let statements all have to be in between the select and the from?
Another thing to check is if your effective date logic isn't a bit
off...there's a whole lotta effective dates in there, and i'm not sure
exactly what you're trying to accomplish with them.  Could be missing
something.  Maybe there's a < when it should be <= , or something along
those lines.

Begin-Select
EMPLID &EMPLID   
SUPERVISOR_ID   
   LET $EMPLID = &EMPLID    /** Right there <--?**/
   LET $SUP_ID = &SUPERVISOR_ID

   SHOW 'SELECT'
   DO GET-MAX-EFFDT
   SHOW $EMPLID
   SHOW $SUP_ID
   SHOW $EFFDT
   DO UPD-SUPID

FROM  PS_JOB A
WHERE A.EMPLID = '000156'
AND   A.EFFDT  = (SELECT MAX(B.EFFDT ) FROM PS_JOB B
                  WHERE A.EMPLID   = B.EMPLID
                  AND   B.EFFDT    < (SELECT MAX(C.EFFDT ) FROM PS_JOB C
                                      WHERE B.EMPLID   = C.EMPLID
                                      AND   C.EMPLID   = A.EMPLID
                                      AND   C.ACTION   = 'DTA'
                                      AND   C.EFFDT    <= $ASOFDATE))
End-Select

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of GK
Sent: Thursday, October 21, 2004 4:19 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: [sqr-users] Probelm with SQR procedure


Hi all,

I have an SQR procedure that I have a small problem with. When I am running
the query in query analyzer, it is returning one row and is working fine.
but when I am using the same query in a sqr procedure, it is not returning
and rows. I am on SQL server. Can anybody please tell me what I am doing
wrong? Please find the procedure.

Begin-Select

EMPLID &EMPLID
   LET $EMPLID = &EMPLID
SUPERVISOR_ID
   LET $SUP_ID = &SUPERVISOR_ID

   SHOW 'SELECT'
   DO GET-MAX-EFFDT
   SHOW $EMPLID
   SHOW $SUP_ID
   SHOW $EFFDT
   DO UPD-SUPID

FROM  PS_JOB A
WHERE A.EMPLID = '000156'
AND   A.EFFDT  = (SELECT MAX(B.EFFDT ) FROM PS_JOB B
                  WHERE A.EMPLID   = B.EMPLID
                  AND   B.EFFDT    < (SELECT MAX(C.EFFDT ) FROM PS_JOB C
                                      WHERE B.EMPLID   = C.EMPLID
                                      AND   C.EMPLID   = A.EMPLID
                                      AND   C.ACTION   = 'DTA'
                                      AND   C.EFFDT    <= $ASOFDATE))
End-Select



Thanks in advance,
gk

_______________________________________________
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