[sqr-users] SQL Server issue

[JOHN_HARRIS@qvc.com]

I have the following SQL that runs fine against a SQLServer database:

SELECT      Z.PersonName, Z.CATEGORY, COUNT(ID), SUM(Z.HOURS_WORKED)
FROM  (SELECT A.PersonName                      PersonName
                        ,A.CATEGORY                   CATEGORY
                        ,1                                  TOTAL_CLOSED
                        ,SUM(A.HOURS_WORKED)    HOURS_WORKED
                        ,A.ID                               ID
      FROM ( ( SELECT dbo.GET_SHOWNAME(PRO_HISTORYLINES.REG_CREATED_BY_OID)
      PersonName
            , SUM(PRO_HISTORYLINES.HPR_SPENTTIME)
                  SPENTTIME
            , (PRO_HISTORYLINES.HPR_SPENTTIME)*24
            HOURS_WORKED
            , PROBLEMS.PRO_ID
            ID
            , MAX(ITSM_workgroups.WOG_NAME)
                  GROUP_NAME
            , 'PR'
                                          CATEGORY
            FROM ITSM_HISTORYLINES_PROBLEM  PRO_HISTORYLINES WITH (NOLOCK)
            INNER JOIN ITSM_PROBLEMS PROBLEMS  WITH (NOLOCK) ON
(PRO_HISTORYLINES.HPR_PRO_OID=PROBLEMS.PRO_OID)
            INNER JOIN ITSM_persons WITH (NOLOCK) ON
PRO_HISTORYLINES.REG_CREATED_BY_OID = ITSM_persons.per_acc_oid
            INNER JOIN ITSM_MEMBERS WITH (NOLOCK) ON
ITSM_MEMBERS.MEM_PER_OID=ITSM_PERSONS.PER_OID
            INNER JOIN ITSM_workgroups WITH (NOLOCK) ON
ITSM_MEMBERS.mem_wog_oid=ITSM_WORKGROUPS.wog_oid
            WHERE ITSM_workgroups.wog_notselectable=0 AND
PRO_HISTORYLINES.HPR_SPENTTIME>0 AND
                  PRO_HISTORYLINES.HPR_SPENTTIME IS NOT NULL AND
                  dbo.TIMEZONE_ADJ(PRO_HISTORYLINES.HPR_CREATED,'UTC')
BETWEEN '01/06/2007' AND '01/12/2007'
            GROUP BY dbo.GET_SHOWNAME(PRO_HISTORYLINES.REG_CREATED_BY_OID),
PROBLEMS.PRO_ID,
                        PRO_HISTORYLINES.HPR_SPENTTIME)*24 ) ) A
      GROUP BY A.PersonName, A.CATEGORY, A.ID ) Z
GROUP BY Z.PersonName ,Z.CATEGORY
ORDER BY Z.PersonName

This SQR code aborts:

BEGIN-SELECT ON-ERROR=RECORD_SQL_ERROR ($SQL-ERROR,
                                        'MAJOR', '',
                                        'GET_THE_DATA_01')
Z.PersonName                        &S_PERSON_NAME
   LET $PERSON_NAME = &S_PERSON_NAME
   PRINT $PERSON_NAME               (+1,20,0) ON-BREAK PRINT=CHANGE
                                    BEFORE=SPAWN_NEW_REPORT
                                    AFTER=PERSON_NAME_BREAK
                                    SAVE=$SAVE_PERSON_NAME
                                    LEVEL=2 SET=1
Z.CATEGORY                          &S_CATEGORY             (+1,30,0)
   LET $CATEGORY = &S_CATEGORY
COUNT(ID)                          &S_TOTAL_CLOSED         (0,40,0) EDIT
999,999
   LET $TOTAL_CLOSED = &S_TOTAL_CLOSED
SUM(Z.HOURS_WORKED)                 &S_HOURS_WORKED
   LET $HOURS_WORKED = &S_HOURS_WORKED
FROM
(SELECT
A.PersonName                              PersonName
,A.CATEGORY                               CATEGORY
,1                                    TOTAL_CLOSED
,SUM(A.HOURS_WORKED)                        HOURS_WORKED
,A.ID                                     ID
FROM
(
(
SELECT
  dbo.GET_SHOWNAME(PRO_HISTORYLINES.REG_CREATED_BY_OID)     PersonName,
  SUM(PRO_HISTORYLINES.HPR_SPENTTIME)                       SPENTTIME,
  (PRO_HISTORYLINES.HPR_SPENTTIME)*24                       HOURS_WORKED,
  PROBLEMS.PRO_ID                                     ID,
  MAX(ITSM_workgroups.WOG_NAME)                             GROUP_NAME,
  'PR'                                                            CATEGORY
FROM
  ITSM_HISTORYLINES_PROBLEM  PRO_HISTORYLINES WITH (NOLOCK)
   INNER JOIN ITSM_PROBLEMS PROBLEMS  WITH (NOLOCK) ON
  (PRO_HISTORYLINES.HPR_PRO_OID=PROBLEMS.PRO_OID)
   INNER JOIN ITSM_persons WITH (NOLOCK) ON
  PRO_HISTORYLINES.REG_CREATED_BY_OID = ITSM_persons.per_acc_oid
  INNER JOIN ITSM_MEMBERS WITH (NOLOCK) ON
  ITSM_MEMBERS.MEM_PER_OID=ITSM_PERSONS.PER_OID
  INNER JOIN ITSM_workgroups WITH (NOLOCK) ON
  ITSM_MEMBERS.mem_wog_oid=ITSM_WORKGROUPS.wog_oid
WHERE
    ITSM_workgroups.wog_notselectable=0 AND
    PRO_HISTORYLINES.HPR_SPENTTIME>0 AND PRO_HISTORYLINES.HPR_SPENTTIME IS
NOT NULL AND
    dbo.TIMEZONE_ADJ(PRO_HISTORYLINES.HPR_CREATED,'UTC') BETWEEN
$DATE_START1 AND $DATE_TO1
GROUP BY
  dbo.GET_SHOWNAME(PRO_HISTORYLINES.REG_CREATED_BY_OID),
  PROBLEMS.PRO_ID,
  (PRO_HISTORYLINES.HPR_SPENTTIME)*24
)
) A
GROUP BY
 A.PersonName,
 A.CATEGORY,
 A.ID
) Z
GROUP BY
Z.PersonName
,Z.CATEGORY
ORDER BY
Z.PersonName
END-SELECT

with the following error:

(SQR 1303) Error in SQL (perhaps missing &name after expression):
SELECT Z.PersonName, Z.CATEGORY, COUNT(ID), SUM(Z.HOURS_WORKED)  FROM (
SELECT A.PersonName     PersonName ,A.CATEGORY      CATEGORY ,1
TOTAL_CLOSED ,SUM(A.HOURS_WORKED)   HOURS_WORKED ,A.ID        ID FROM ( (
SELECT dbo.GET_SHOWNAME(PRO_HISTORYLINES.REG_CREATED_BY_OID)  PersonName,
SUM(PRO_HISTORYLINES.HPR_SPENTTIME)      SPENTTIME,
(PRO_HISTORYLINES.HPR_SPENTTIME)*24        HOURS_WORKED, PROBLEMS.PRO_ID
ID, MAX(ITSM_workgroups.WOG_NAME)           GROUP_NAME, 'PR'
CATEGORY FROM ITSM_HISTORYLINES_PROBLEM  PRO_HISTORYLINES WITH (NOLOCK)
INNER JOIN ITSM_PROBLEMS PROBLEMS  WITH (NOLOCK) ON
(PRO_HISTORYLINES.HPR_PRO_OID=PROBLEMS.PRO_OID) INNER JOIN ITSM_persons
WITH (NOLOCK) ON PRO_HISTORYLINES.REG_CREATED_BY_OID =
ITSM_persons.per_acc_oid INNER JOIN ITSM_MEMBERS WITH (NOLOCK) ON
ITSM_MEMBERS.MEM_PER_OID=ITSM_PERSONS.PER_OID INNER JOIN ITSM_workgroups
WITH (NOLOCK) ON ITSM_MEMBERS.mem_wog_oid=ITSM_WORKGROUPS.wog_oid WHERE
ITSM_workgroups.wog_notselectable=0 AND PRO_HISTORYLINES.HPR_SPENTTIME>0
AND PRO_HISTORYLINES.HPR_SPENTTIME IS NOT NULL AND
dbo.TIMEZONE_ADJ(PRO_HISTORYLINES.HPR_CREATED,'UTC') BETWEEN ? AND ? GROUP
BY dbo.GET_SHOWNAME(PRO_HISTORYLINES.REG_CREATED_BY_OID), PROBLEMS.PRO_ID,
(PRO_HISTORYLINES.HPR_SPENTTIME)*24 ) ) A GROUP BY A.PersonName,
A.CATEGORY, A.ID ) Z GROUP BY Z.PersonName ,Z.CATEGORY ORDER BY
Z.PersonName
(SQR 1304) Check SELECT columns, expressions and 'where' clause for syntax.

SQR: Program Aborting.
Any help would be appreciated.

Thank You,
John Harris
QVC Inc., 1200 Wilson Drive, MC 220, West Chester, PA 19380-4262


Telephone 484-701-3303
Fax 484-701-8437
e-mail jharris@qvc.com



Than


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