InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 16:32:31 GMT20http://www.teradata.com/teradataforum/Topic11705-9-1.aspxHint: *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.DieterTue, 03 Jun 2008 03:37:28 GMTdnoethhttp://www.teradata.com/teradataforum/Topic11705-9-1.aspxHi,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([i]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 2007added 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 REVIEWERCOMMENTFROM ALENT_WORKFLOWPROCESS WFPINNER JOIN ALOM_ASSOENTITYFACET AEF1 ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSID AND FACETTYPEID IN (SELECT FACETTYPEIDFROM ALOM_FACETTYPEWHERE (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 RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = 'APPROVALS_JOBINITIATOR')/* --- GET ACTIVITIES OF APPROVALS JOBS --- */LEFT OUTER JOIN ALOM_ASSEMBLY ASSM2 ON ASSM2.CONTAINERID = WFP.WORKFLOWPROCESSID AND ASSM2.RELATIONSHIPTYPEID IN (SELECT RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = 'PROCESS_ACTIVITY')/* --- GET REVIEWER OF ACTIVITY --- */ LEFT OUTER JOIN ALOM_ASSEMBLY ASSM3 ON ASSM3.CONTAINERID = ASSM2.CONTAINSID AND ASSM3.RELATIONSHIPTYPEID IN (SELECT RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = 'ACTIVITY_CONTRIBUTOR')/* --- GET RESPONSES OF ACTIVITY --- */LEFT OUTER JOIN ALOM_ASSEMBLY ASSM4 ON ASSM4.CONTAINERID = ASSM2.CONTAINSID AND ASSM4.RELATIONSHIPTYPEID IN (SELECT RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = 'ACTIVITY_RESPONSE')LEFT OUTER JOIN ALENT_USER INITIATOR ON INITIATOR.USERID = ASSM1.CONTAINSIDLEFT OUTER JOIN ALENT_ACTIVITY ACT ON ACT.ACTIVITYID = ASSM2.CONTAINSIDLEFT OUTER JOIN ALENT_USER REVIEWER ON REVIEWER.USERID = ASSM3.CONTAINSIDLEFT OUTER JOIN ALENT_RESPONSE RSP ON RSP.RESPONSEID = ASSM4.CONTAINSIDWHERE 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 2007added 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 WFPINNER JOIN ALOM_ASSOENTITYFACET AEF1 ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSIDINNER 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 RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = 'APPROVALS_JOBINITIATOR')LEFT OUTER JOIN ALENT_USER INITIATOR ON INITIATOR.USERID = ASSM1.CONTAINSIDWHERE 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 RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = 'PROCESS_ACTIVITY') LEFT OUTER JOIN ALENT_ACTIVITY ACT ON ACT.ACTIVITYID = ASSM2.CONTAINSID WHERE ACT.ENDACTIVITY <> 'TRUE' AND ACT.STARTACTIVITY <> 'TRUE');[/i]);Here is the stored procedure[highlight=#ffff11]Replace Procedure APPROVAL_JOBS ( )BeginCall DBC.SysExecSQL('DELETE APPROVAL_JOBS_R ALL;INSERT INTO APPROVAL_JOBS_R[i]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 2007added 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 REVIEWERCOMMENTFROM ALENT_WORKFLOWPROCESS WFPINNER JOIN ALOM_ASSOENTITYFACET AEF1 ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSID AND FACETTYPEID IN (SELECT FACETTYPEIDFROM ALOM_FACETTYPEWHERE (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 RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = ''APPROVALS_JOBINITIATOR'')/* --- GET ACTIVITIES OF APPROVALS JOBS --- */LEFT OUTER JOIN ALOM_ASSEMBLY ASSM2 ON ASSM2.CONTAINERID = WFP.WORKFLOWPROCESSID AND ASSM2.RELATIONSHIPTYPEID IN (SELECT RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = ''PROCESS_ACTIVITY'')/* --- GET REVIEWER OF ACTIVITY --- */ LEFT OUTER JOIN ALOM_ASSEMBLY ASSM3 ON ASSM3.CONTAINERID = ASSM2.CONTAINSID AND ASSM3.RELATIONSHIPTYPEID IN (SELECT RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = ''ACTIVITY_CONTRIBUTOR'')/* --- GET RESPONSES OF ACTIVITY --- */LEFT OUTER JOIN ALOM_ASSEMBLY ASSM4 ON ASSM4.CONTAINERID = ASSM2.CONTAINSID AND ASSM4.RELATIONSHIPTYPEID IN (SELECT RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = ''ACTIVITY_RESPONSE'')LEFT OUTER JOIN ALENT_USER INITIATOR ON INITIATOR.USERID = ASSM1.CONTAINSIDLEFT OUTER JOIN ALENT_ACTIVITY ACT ON ACT.ACTIVITYID = ASSM2.CONTAINSIDLEFT OUTER JOIN ALENT_USER REVIEWER ON REVIEWER.USERID = ASSM3.CONTAINSIDLEFT OUTER JOIN ALENT_RESPONSE RSP ON RSP.RESPONSEID = ASSM4.CONTAINSIDWHERE 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 2007added 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 WFPINNER JOIN ALOM_ASSOENTITYFACET AEF1 ON AEF1.ENTITYID = WFP.WORKFLOWPROCESSIDINNER 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 RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = ''APPROVALS_JOBINITIATOR'')LEFT OUTER JOIN ALENT_USER INITIATOR ON INITIATOR.USERID = ASSM1.CONTAINSIDWHERE 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 RELATIONSHIPTYPEIDFROM ALOM_RELATIONSHIPTYPESWHERE RELATIONSHIPNAME = ''PROCESS_ACTIVITY'') LEFT OUTER JOIN ALENT_ACTIVITY ACT ON ACT.ACTIVITYID = ASSM2.CONTAINSID WHERE ACT.ENDACTIVITY <> ''TRUE'' AND ACT.STARTACTIVITY <> ''TRUE'');[/i]');End;[/highlight]Tue, 03 Jun 2008 01:52:58 GMTmeetsatishg@gmail.com