Re: SQR error (1303 & 1304)

[Stephen Keen <stephen_keen@SQR-SERVICES.COM>]

hey Derick

At first glance I do not see what table was aliased as SIDE1, or side2?

-----Original Message-----
From: Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Derek Anderson
Sent: Friday, January 04, 2002 11:53 PM
To: SQR-USERS@list.iex.net
Subject: SQR error (1303 & 1304)


I am trying to run an SQR that functions fine in DB2, but when moved to a
new SQL Server environment I receive the below listed error message.  This
is unique in that this is the only sqr that I am having problems with.  The
issue is further complicated because I can extract the SQL and run it in the
SQL Server query tool, and it brings back results.  So - nutshell - the SQL
can execute in individual query tools (DB2 & SQL Server), runs in SQR in
DB2, but when I attempt to run it on SQL Server platform I receive the
following message:

(SQR 1303) Error in SQL (perhaps missing &name after expression):
SELECT SIDE1.EMPLID, SIDE1.EMPL_RCD, SIDE1.WORKGROUP, SIDE2.TRC,
SIDE2.TL_QUANTITY, SIDE1.NAME, SIDE1.DESCR  FROM (SELECT
GRP.EMPLID,GRP.EMPL_RCD,JCD.DESCR,PER.NAME,ED.WORKGROUP FROM PS_TL_GROUP_DTL
GRP, PS_JOB JOB, PS_JOBCODE_TBL JCD, PS_TL_EMPL_DATA ED, PS_PERSONAL_DATA
PER WHERE GRP.EMPLID    = JOB.EMPLID AND   GRP.EMPL_RCD = JOB.EMPL_RCD AND
JOB.EMPLID    = ED.EMPLID AND   JOB.EMPL_RCD = ED.EMPL_RCD AND   GRP.EMPLID
   = PER.EMPLID AND   JOB.SETID_JOBCODE = ? AND   JOB.SETID_JOBCODE =
JCD.SETID AND   GRP.GROUP_ID  = ? AND   JOB.JOBCODE   = JCD.JOBCODE AND
JOB.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND   JOB.EFFDT = (SELECT MAX(EFFDT)
FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT
<= ?) AND   JOB.EFFSEQ = (SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID =
JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND JOB.SETID_JOBCODE = ? AND EFFDT =
JOB.EFFDT) AND   JCD.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL WHERE
JOBCODE = JCD.JOBCODE AND SETID = ? AND EFFDT <=?) AND   ED.EFFDT = (SELECT
MAX(EFFDT) FROM PS_TL_EMPL_DATA WHERE EMPLID = ED.EMPLID AND EMPL_RCD =
ED.EMPL_RCD AND EFFDT <= ?) AND JCD.EFF_STATUS = 'A')  AS SIDE1 LEFT OUTER
JOIN (SELECT ERN.TRC,ERN.TL_QUANTITY,GRP2.EMPLID,GRP2.EMPL_RCD,GRP2.GROUP_ID
FROM PS_TL_RPTD_ELPTIME ERN, PS_TL_GROUP_DTL GRP2 WHERE ERN.EMPLID =
GRP2.EMPLID AND   ERN.EMPL_RCD = GRP2.EMPL_RCD AND   ERN.DUR = ? AND
GRP2.EMPL_RCD      = ERN.EMPL_RCD AND   GRP2.GROUP_ID      = ?) AS SIDE2 ON
SIDE1.EMPLID = SIDE2.EMPLID AND SIDE1.EMPL_RCD = SIDE2.EMPL_RCD ORDER BY
SIDE1.NAME
(SQR 1304) Check SELECT columns, expressions and 'where' clause for syntax.

SQR for PeopleSoft: Program Aborting.


This is the SQL in the SQR:

begin-SELECT on-error=ERROR-DISPLAY
SIDE1.EMPLID,
SIDE1.EMPL_RCD,
SIDE1.WORKGROUP,
SIDE1.DESCR,
SIDE1.NAME,
SIDE2.TRC,
SIDE2.TL_QUANTITY
FROM
(SELECT
GRP.EMPLID,
GRP.EMPL_RCD,
ED.WORKGROUP,
JCD.DESCR,
PER.NAME
FROM
PS_TL_GROUP_DTL GRP,
PS_JOB JOB,
PS_JOBCODE_TBL JCD,
PS_TL_EMPL_DATA ED,
PS_PERSONAL_DATA PER
WHERE GRP.EMPLID    = JOB.EMPLID
AND   GRP.EMPL_RCD = JOB.EMPL_RCD
AND   JOB.EMPLID    = ED.EMPLID
AND   JOB.EMPL_RCD = ED.EMPL_RCD
AND   GRP.EMPLID    = PER.EMPLID
AND   JOB.SETID_JOBCODE = $SETID
AND   JOB.SETID_JOBCODE = JCD.SETID
AND   GRP.GROUP_ID  = $GroupID
AND   JOB.JOBCODE   = JCD.JOBCODE
AND   JOB.EMPL_STATUS IN ('A', 'P', 'Q', 'U')
AND   JOB.EFFDT =
      (SELECT MAX(EFFDT)
        FROM PS_JOB
        WHERE EMPLID = JOB.EMPLID
   AND EMPL_RCD = JOB.EMPL_RCD
AND EFFDT <= $ThruDate)
AND   JOB.EFFSEQ =
       (SELECT MAX(EFFSEQ)
       FROM PS_JOB
       WHERE EMPLID = JOB.EMPLID
      AND EMPL_RCD = JOB.EMPL_RCD
      AND JOB.SETID_JOBCODE = $SETID
      AND EFFDT = JOB.EFFDT)
AND   JCD.EFFDT =
       (SELECT MAX(EFFDT)
       FROM PS_JOBCODE_TBL
       WHERE JOBCODE = JCD.JOBCODE
       AND SETID = $SETID
       AND EFFDT <= $THRUDATE)
AND   ED.EFFDT =
       (SELECT MAX(EFFDT)
       FROM PS_TL_EMPL_DATA
       WHERE EMPLID = ED.EMPLID
        AND EMPL_RCD = ED.EMPL_RCD
       AND EFFDT <= $Thrudate)
AND JCD.EFF_STATUS = 'A')  AS SIDE1
LEFT OUTER JOIN
(SELECT ERN.TRC,
ERN.TL_QUANTITY
FROM
PS_TL_RPTD_ELPTIME ERN,
PS_TL_GROUP_DTL GRP2
WHERE ERN.EMPLID = GRP2.EMPLID
  AND   ERN.EMPL_RCD = GRP2.EMPL_RCD
  AND   ERN.DUR = $ProcessDate
  AND   GRP2.EMPL_RCD      = ERN.EMPL_RCD
  AND   GRP2.GROUP_ID      = $GroupID) AS SIDE2
ON  SIDE1.EMPLID = SIDE2.EMPLID
AND SIDE1.EMPL_RCD = SIDE2.EMPL_RCD
ORDER BY SIDE1.NAME
end-select
end-procedure

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.