Re: SQR error (1303 & 1304)

[Al H <star111lists@MINDSPRING.COM>]

Start by looking at the dates in your $ variables.  When moving between
platforms, its quite often string dates that cause incompatibility
issues.  This is especially suspect since you are getting positive results
in Query Analyzer, since you would have had to replace the $ variables with
valid dates in order to execute the SQL there.  My guess would be that you
have a date variable in the installation settings that doesn't match the
SETENV.SQC paramaters.


At 11:52 PM 1/4/2002 -0500, you wrote:
>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.