Macro does not work when converted into stored procedure

Database
Enthusiast

Macro does not work when converted into stored procedure

Hi,

Following macro executes successfully. When the query batch from this macro is repackaged as stored procedure, I get a syntax error. Please see macro definition, stored procedure definition and the error below. The italicized portion in macro and stored procedure is identical.

The error returned is:

3706: APPROVAL_JOBS:Syntax error: expected something between ')' and ')'.

Replace Macro mymacro As
(
SELECT
WFI.APPROVALSJOBID COMMON_JOBID,
WFI.REVISIONNUMBER JOB_REVISION_NO,
WFP.WORKFLOWPROCESSID JOBID,
ACT.SEQUENCENUMBER REVIEW_SEQ_NO,
ACT.ACTIVITYID REVIEWID,
WFP.PROCESSNAME JOB_NAME,
WFP.DESCRIPTION JOB_DESCRIPTION,
ACT.ACTIVITYNAME REVIEW_NAME,
WFP.STATUS JOB_STATUS,
ACT.STATUS REVIEW_STATUS,
INITIATOR.USERID INITIATOR_ID,
/*Hemant Karkande - Friday 23rd November 2007
added check for empty first name and last name*/
CASE
WHEN (((INITIATOR.FIRSTNAME IS NOT NULL)
AND (INITIATOR.FIRSTNAME <> ''))
AND ((INITIATOR.LASTNAME IS NOT NULL)
AND ((INITIATOR.LASTNAME <> '')))) THEN
INITIATOR.FIRSTNAME || ' ' ||
Case
When (INITIATOR.LASTNAME IS NULL) THEN ''
END
ELSE
INITIATOR.LOGINNAME
END AS INITIATOR_NAME,
REVIEWER.USERID REVIEWER_ID,
CASE
WHEN (((REVIEWER.FIRSTNAME IS NOT NULL)
AND (REVIEWER.FIRSTNAME <> ''))
AND ((REVIEWER.LASTNAME IS NOT NULL)
AND (REVIEWER.LASTNAME <> ''))) THEN
REVIEWER.FIRSTNAME || ' ' ||
Case
When REVIEWER.LASTNAME IS NULL THEN ''
END
ELSE
REVIEWER.LOGINNAME
END AS REVIEWER_NAME,
CASE
WHEN (WFP.ACTUALSTARTDATE IS NULL) THEN
WFP.STARTDATE
ELSE
WFP.ACTUALSTARTDATE
END AS JOB_STARTDATE,
CASE
WHEN (WFP.ACTUALENDDATE IS NULL) THEN
WFP.DUEDATE
ELSE
WFP.ACTUALENDDATE
END AS JOB_DUEDATE,
ACT.STARTDATE REVIEW_STARTDATE,
ACT.DUEDATE REVIEW_DUETDATE,
ACT.ACTUALSTARTDATE REVIEW_ACTUALSTARTDATE,
ACT.ACTUALENDDATE REVIEW_ACTUALENDDATE,
RSP.RESPONSETEXT REVIEWERCOMMENT

FROM ALENT_WORKFLOWPROCESS WFP
INNER JOIN ALOM_ASSOENTITYFACET AEF1
ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSID
AND FACETTYPEID IN (
SELECT FACETTYPEID
FROM ALOM_FACETTYPE
WHERE (FACETTYPENAME = 'WORKFLOWINFORMATION'))
INNER JOIN ALFCT_WORKFLOWINFORMATION WFI
ON WFI.WORKFLOWINFORMATIONID = AEF1.FACETID
AND WFI.WORKFLOWCATEGORY = 'APPROVALSJOB'
/* --- GET APPROVAL JOB INITIATOR --- */
INNER JOIN ALOM_ASSEMBLY ASSM1
ON ASSM1.CONTAINERID = WFP.WORKFLOWPROCESSID
AND ASSM1.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = 'APPROVALS_JOBINITIATOR')
/* --- GET ACTIVITIES OF APPROVALS JOBS --- */
LEFT OUTER JOIN ALOM_ASSEMBLY ASSM2
ON ASSM2.CONTAINERID = WFP.WORKFLOWPROCESSID
AND ASSM2.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = 'PROCESS_ACTIVITY')
/* --- GET REVIEWER OF ACTIVITY --- */
LEFT OUTER JOIN ALOM_ASSEMBLY ASSM3
ON ASSM3.CONTAINERID = ASSM2.CONTAINSID
AND ASSM3.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = 'ACTIVITY_CONTRIBUTOR')
/* --- GET RESPONSES OF ACTIVITY --- */
LEFT OUTER JOIN ALOM_ASSEMBLY ASSM4
ON ASSM4.CONTAINERID = ASSM2.CONTAINSID
AND ASSM4.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = 'ACTIVITY_RESPONSE')

LEFT OUTER JOIN ALENT_USER INITIATOR
ON INITIATOR.USERID = ASSM1.CONTAINSID
LEFT OUTER JOIN ALENT_ACTIVITY ACT
ON ACT.ACTIVITYID = ASSM2.CONTAINSID
LEFT OUTER JOIN ALENT_USER REVIEWER
ON REVIEWER.USERID = ASSM3.CONTAINSID
LEFT OUTER JOIN ALENT_RESPONSE RSP
ON RSP.RESPONSEID = ASSM4.CONTAINSID
WHERE ACT.ENDACTIVITY <> 'TRUE'
AND ACT.STARTACTIVITY <> 'TRUE'

UNION

/* GET ALL JOBS HAVING NO ACTIVITY */
SELECT
DISTINCT WFI.APPROVALSJOBID COMMON_JOBID,
WFI.REVISIONNUMBER JOB_REVISION_NO,
WFP.WORKFLOWPROCESSID JOBID,
CAST(NULL As Decimal(18,0)),
CAST(NULL As Decimal(18,0)),
WFP.PROCESSNAME JOB_NAME,
WFP.DESCRIPTION JOB_DESCRIPTION,
Cast(NULL As varchar(255)),
WFP.STATUS JOB_STATUS,
Cast(NULL As varchar(50)),
INITIATOR.USERID INITIATOR_ID,
/*Hemant Karkande - Friday 23rd November 2007
added check for empty first name and last name*/
--CASE WHEN ((INITIATOR.FIRSTNAME IS NOT NULL) AND (INITIATOR.LASTNAME IS NOT NULL)) THEN
CASE
WHEN (((INITIATOR.FIRSTNAME IS NOT NULL)
AND (INITIATOR.FIRSTNAME <> ''))
AND ((INITIATOR.LASTNAME IS NOT NULL)
AND ((INITIATOR.LASTNAME <> '')))) THEN
INITIATOR.FIRSTNAME || ' ' ||
CASE
WHEN( INITIATOR.LASTNAME IS NULL ) THEN ''
END
ELSE
INITIATOR.LOGINNAME
END AS INITIATOR_NAME,

CAST(NULL As Decimal(18,0)),
Cast(NULL As varchar(100)),
CASE
WHEN (WFP.ACTUALSTARTDATE IS NULL) THEN
WFP.STARTDATE
ELSE
WFP.ACTUALSTARTDATE
END,
CASE
WHEN (WFP.ACTUALENDDATE IS NULL) THEN
WFP.DUEDATE
ELSE
WFP.ACTUALENDDATE
END,
Cast(NULL As Timestamp),
Cast(NULL As Timestamp),
Cast(NULL As Timestamp),
Cast(NULL As Timestamp),
Cast(NULL As Varchar(10000))

FROM ALENT_WORKFLOWPROCESS WFP
INNER JOIN ALOM_ASSOENTITYFACET AEF1
ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSID
INNER JOIN ALFCT_WORKFLOWINFORMATION WFI
ON WFI.WORKFLOWINFORMATIONID = AEF1.FacetID
AND WFI.WORKFLOWCATEGORY = 'APPROVALSJOB'
INNER JOIN ALOM_ASSEMBLY ASSM1
ON ASSM1.CONTAINERID = WFP.WORKFLOWPROCESSID
AND ASSM1.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = 'APPROVALS_JOBINITIATOR')

LEFT OUTER JOIN ALENT_USER INITIATOR
ON INITIATOR.USERID = ASSM1.CONTAINSID

WHERE APPROVALSJOBID NOT IN
(
/* GET JOBS HAVING ACTIVITY */
SELECT DISTINCT APPROVALSJOBID
FROM ALENT_WORKFLOWPROCESS WFP
INNER JOIN ALOM_ASSOENTITYFACET AEF1
ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSID
INNER JOIN ALFCT_WORKFLOWINFORMATION WFI
ON WFI.WORKFLOWINFORMATIONID = AEF1.FacetID
AND WFI.WORKFLOWCATEGORY = 'APPROVALSJOB'
LEFT OUTER JOIN ALOM_ASSEMBLY ASSM2
ON ASSM2.CONTAINERID = WFP.WORKFLOWPROCESSID
AND ASSM2.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = 'PROCESS_ACTIVITY')
LEFT OUTER JOIN ALENT_ACTIVITY ACT
ON ACT.ACTIVITYID = ASSM2.CONTAINSID
WHERE ACT.ENDACTIVITY <> 'TRUE'
AND ACT.STARTACTIVITY <> 'TRUE'
);
);

Here is the stored procedure

[highlight=#ffff11]Replace Procedure APPROVAL_JOBS ( )
Begin

Call DBC.SysExecSQL('
DELETE APPROVAL_JOBS_R ALL;
INSERT INTO APPROVAL_JOBS_R
SELECT
WFI.APPROVALSJOBID COMMON_JOBID,
WFI.REVISIONNUMBER JOB_REVISION_NO,
WFP.WORKFLOWPROCESSID JOBID,
ACT.SEQUENCENUMBER REVIEW_SEQ_NO,
ACT.ACTIVITYID REVIEWID,
WFP.PROCESSNAME JOB_NAME,
WFP.DESCRIPTION JOB_DESCRIPTION,
ACT.ACTIVITYNAME REVIEW_NAME,
WFP.STATUS JOB_STATUS,
ACT.STATUS REVIEW_STATUS,
INITIATOR.USERID INITIATOR_ID,
/*Hemant Karkande - Friday 23rd November 2007
added check for empty first name and last name*/
CASE
WHEN (((INITIATOR.FIRSTNAME IS NOT NULL)
AND (INITIATOR.FIRSTNAME <> ''))
AND ((INITIATOR.LASTNAME IS NOT NULL)
AND ((INITIATOR.LASTNAME <> '')))) THEN
INITIATOR.FIRSTNAME || ' ' ||
Case
When (INITIATOR.LASTNAME IS NULL) THEN ''
END
ELSE
INITIATOR.LOGINNAME
END AS INITIATOR_NAME,
REVIEWER.USERID REVIEWER_ID,
CASE
WHEN (((REVIEWER.FIRSTNAME IS NOT NULL)
AND (REVIEWER.FIRSTNAME <> ''))
AND ((REVIEWER.LASTNAME IS NOT NULL)
AND (REVIEWER.LASTNAME <> ''))) THEN
REVIEWER.FIRSTNAME || ' ' ||
Case
When REVIEWER.LASTNAME IS NULL THEN ''
END
ELSE
REVIEWER.LOGINNAME
END AS REVIEWER_NAME,
CASE
WHEN (WFP.ACTUALSTARTDATE IS NULL) THEN
WFP.STARTDATE
ELSE
WFP.ACTUALSTARTDATE
END AS JOB_STARTDATE,
CASE
WHEN (WFP.ACTUALENDDATE IS NULL) THEN
WFP.DUEDATE
ELSE
WFP.ACTUALENDDATE
END AS JOB_DUEDATE,
ACT.STARTDATE REVIEW_STARTDATE,
ACT.DUEDATE REVIEW_DUETDATE,
ACT.ACTUALSTARTDATE REVIEW_ACTUALSTARTDATE,
ACT.ACTUALENDDATE REVIEW_ACTUALENDDATE,
RSP.RESPONSETEXT REVIEWERCOMMENT

FROM ALENT_WORKFLOWPROCESS WFP
INNER JOIN ALOM_ASSOENTITYFACET AEF1
ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSID
AND FACETTYPEID IN (
SELECT FACETTYPEID
FROM ALOM_FACETTYPE
WHERE (FACETTYPENAME = ''WORKFLOWINFORMATION''))
INNER JOIN ALFCT_WORKFLOWINFORMATION WFI
ON WFI.WORKFLOWINFORMATIONID = AEF1.FACETID
AND WFI.WORKFLOWCATEGORY = ''APPROVALSJOB''
/* --- GET APPROVAL JOB INITIATOR --- */
INNER JOIN ALOM_ASSEMBLY ASSM1
ON ASSM1.CONTAINERID = WFP.WORKFLOWPROCESSID
AND ASSM1.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = ''APPROVALS_JOBINITIATOR'')
/* --- GET ACTIVITIES OF APPROVALS JOBS --- */
LEFT OUTER JOIN ALOM_ASSEMBLY ASSM2
ON ASSM2.CONTAINERID = WFP.WORKFLOWPROCESSID
AND ASSM2.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = ''PROCESS_ACTIVITY'')
/* --- GET REVIEWER OF ACTIVITY --- */
LEFT OUTER JOIN ALOM_ASSEMBLY ASSM3
ON ASSM3.CONTAINERID = ASSM2.CONTAINSID
AND ASSM3.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = ''ACTIVITY_CONTRIBUTOR'')
/* --- GET RESPONSES OF ACTIVITY --- */
LEFT OUTER JOIN ALOM_ASSEMBLY ASSM4
ON ASSM4.CONTAINERID = ASSM2.CONTAINSID
AND ASSM4.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = ''ACTIVITY_RESPONSE'')

LEFT OUTER JOIN ALENT_USER INITIATOR
ON INITIATOR.USERID = ASSM1.CONTAINSID
LEFT OUTER JOIN ALENT_ACTIVITY ACT
ON ACT.ACTIVITYID = ASSM2.CONTAINSID
LEFT OUTER JOIN ALENT_USER REVIEWER
ON REVIEWER.USERID = ASSM3.CONTAINSID
LEFT OUTER JOIN ALENT_RESPONSE RSP
ON RSP.RESPONSEID = ASSM4.CONTAINSID
WHERE ACT.ENDACTIVITY <> ''TRUE''
AND ACT.STARTACTIVITY <> ''TRUE''

UNION

/* GET ALL JOBS HAVING NO ACTIVITY */
SELECT
DISTINCT WFI.APPROVALSJOBID COMMON_JOBID,
WFI.REVISIONNUMBER JOB_REVISION_NO,
WFP.WORKFLOWPROCESSID JOBID,
CAST(NULL As Decimal(18,0)),
CAST(NULL As Decimal(18,0)),
WFP.PROCESSNAME JOB_NAME,
WFP.DESCRIPTION JOB_DESCRIPTION,
Cast(NULL As varchar(255)),
WFP.STATUS JOB_STATUS,
Cast(NULL As varchar(50)),
INITIATOR.USERID INITIATOR_ID,
/*Hemant Karkande - Friday 23rd November 2007
added check for empty first name and last name*/
--CASE WHEN ((INITIATOR.FIRSTNAME IS NOT NULL) AND (INITIATOR.LASTNAME IS NOT NULL)) THEN
CASE
WHEN (((INITIATOR.FIRSTNAME IS NOT NULL)
AND (INITIATOR.FIRSTNAME <> ''))
AND ((INITIATOR.LASTNAME IS NOT NULL)
AND ((INITIATOR.LASTNAME <> '')))) THEN
INITIATOR.FIRSTNAME || ' ' ||
CASE
WHEN( INITIATOR.LASTNAME IS NULL ) THEN ''
END
ELSE
INITIATOR.LOGINNAME
END AS INITIATOR_NAME,

CAST(NULL As Decimal(18,0)),
Cast(NULL As varchar(100)),
CASE
WHEN (WFP.ACTUALSTARTDATE IS NULL) THEN
WFP.STARTDATE
ELSE
WFP.ACTUALSTARTDATE
END,
CASE
WHEN (WFP.ACTUALENDDATE IS NULL) THEN
WFP.DUEDATE
ELSE
WFP.ACTUALENDDATE
END,
Cast(NULL As Timestamp),
Cast(NULL As Timestamp),
Cast(NULL As Timestamp),
Cast(NULL As Timestamp),
Cast(NULL As Varchar(10000))

FROM ALENT_WORKFLOWPROCESS WFP
INNER JOIN ALOM_ASSOENTITYFACET AEF1
ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSID
INNER JOIN ALFCT_WORKFLOWINFORMATION WFI
ON WFI.WORKFLOWINFORMATIONID = AEF1.FacetID
AND WFI.WORKFLOWCATEGORY = ''APPROVALSJOB''
INNER JOIN ALOM_ASSEMBLY ASSM1
ON ASSM1.CONTAINERID = WFP.WORKFLOWPROCESSID
AND ASSM1.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = ''APPROVALS_JOBINITIATOR'')

LEFT OUTER JOIN ALENT_USER INITIATOR
ON INITIATOR.USERID = ASSM1.CONTAINSID

WHERE APPROVALSJOBID NOT IN
(
/* GET JOBS HAVING ACTIVITY */
SELECT DISTINCT APPROVALSJOBID
FROM ALENT_WORKFLOWPROCESS WFP
INNER JOIN ALOM_ASSOENTITYFACET AEF1
ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSID
INNER JOIN ALFCT_WORKFLOWINFORMATION WFI
ON WFI.WORKFLOWINFORMATIONID = AEF1.FacetID
AND WFI.WORKFLOWCATEGORY = ''APPROVALSJOB''
LEFT OUTER JOIN ALOM_ASSEMBLY ASSM2
ON ASSM2.CONTAINERID = WFP.WORKFLOWPROCESSID
AND ASSM2.RELATIONSHIPTYPEID IN (
SELECT RELATIONSHIPTYPEID
FROM ALOM_RELATIONSHIPTYPES
WHERE RELATIONSHIPNAME = ''PROCESS_ACTIVITY'')
LEFT OUTER JOIN ALENT_ACTIVITY ACT
ON ACT.ACTIVITYID = ASSM2.CONTAINSID
WHERE ACT.ENDACTIVITY <> ''TRUE''
AND ACT.STARTACTIVITY <> ''TRUE''
);
');

End;
1 REPLY
Senior Apprentice

Re: Macro does not work when converted into stored procedure

Hint: *ALL* single quotes within a string must be replaced by two single quotes.

But i got two additional quetsions:
1. Why do you want to put that into a SP?
You don't use any additional SP features not available in macros.

2. Why do you use Dynamic SQL at all?
It's much harder to write/maintain, as you already noticed ;-)
Your code will work without sysexecsql.

Dieter