4
Dear all,
I'll try to convert from SQL server Store Procedure to DB2 Store Procedure.
I've make SP under DB2 like this
CREATE PROCEDURE GetSearchedReco rds (pKeyWords VARCHAR(1000),
pIsAll INTEGER,
pIsExact INTEGER,
pDateCondition INTEGER,
pDateType INTEGER,
pDateOn TIMESTAMP,
pDateTo TIMESTAMP,
pLimit INTEGER,
pFolderCategory INTEGER,
pFolderID CHAR(32),
pSort INTEGER,
pUserID CHAR(32) )
LANGUAGE SQL
BEGIN
DECLARE vsqlQuery VARCHAR(8000);
DECLARE vTopLimit VARCHAR(30);
DECLARE voperator VARCHAR(5);
DECLARE vkey VARCHAR(300);
DECLARE vkeywordCount INTEGER;
DECLARE vloop INTEGER;
DECLARE l_sqlstatus INTEGER;
DECLARE vDateTypeColumn VARCHAR(20);
DECLARE vSortCondition VARCHAR(35);
DECLARE key_cursor CURSOR FOR SELECT ur.Value
FROM table(SqlSplitD elim(pKeyWords) ) ur;
IF (pLimit = 0) THEN
SET vTopLimit = ' ';
ELSE
SET vTopLimit = ' TOP ' || CAST (RTRIM(CHAR(pLi mit)) AS VARCHAR(30)) || ' ';
END IF;
SET
vsqlQuery = 'Select ' || vTopLimit || ' rec.RecordID, rec.Subject, fol.FolderName, rec.ReportDate, us.FirstName ||''|| us.LastName as Source,
field1.Field1Ty peName, field2.Field2Ty peName, field3.Field3Ty peName, substr(rec.Comm ents, 1, 50) as Comments,
(Select Count(FileName) From Attachments Where DocumentID = rec.RecordID) As AttachmentCount
From ((((Records rec Left Join Field1Types field1 On rec.Field1TypeI D = field1.Field1Ty peID)
Left Join Field2Types field2 On rec.Field2TypeI D = field2.Field2Ty peID)
Left Join Field3Types field3 On rec.Field3TypeI D = field3.Field3Ty peID)
Inner Join Names_Users us On rec.CreatorUser ID = us.UserID)
Inner Join Folders fol On fol.FolderID = rec.FolderID
';
IF (pIsAll = 0) THEN
SET voperator = ' or ';
ELSE
IF (pIsAll = 1) THEN
SET voperator = ' and ';
END IF;
END IF;
SET vsqlQuery = vsqlQuery || ' Where F_GetFolderAcce ssTypeID(fol.Fo lderID,''' || CAST (pUserID AS VARCHAR(100)) || ''') > 1 ';
SET vkeywordCount = (SELECT COUNT(ur.value)
FROM table(SqlSplitD elim(pKeywords) )ur) ;
IF (vkeywordCount <> 0) THEN
SET vsqlQuery = vsqlQuery || ' and (';
END IF;
SET vloop = 0;
OPEN key_cursor;
SET l_sqlstatus = 0;
FETCH FROM key_cursor INTO vkey;
WHILE l_sqlstatus = 0 DO
IF (vloop = vkeywordCount - 1) THEN
SET vsqlQuery = vsqlQuery || ' (rec.Subject like ''%' || vkey || '%'' or fol.foldername like ''%' || vkey || '%'' or us.FirstName like ''%' || vkey || '%'' or us.LastName like ''%' || vkey || '%'' or field1.field1ty pename like ''%' || vkey || '%'' or field2.field2ty pename like ''%' || vkey || '%''or field3.field3ty pename like ''%' || vkey || '%'' or rec.comments like ''%'|| vkey||'%'' ))';
ELSE
SET vsqlQuery = vsqlQuery || ' (rec.Subject like ''%' || vkey || '%'' or fol.foldername like ''%' || vkey || '%'' or us.FirstName like ''%' || vkey || '%'' or us.LastName like ''%' || vkey || '%'' or field1.field1ty pename like ''%' || vkey || '%'' or field2.field2ty pename like ''%'|| vkey || '%'' or field3.field3ty pename like ''%' || vkey || '%'' or rec.comments like ''%'|| vkey||'%'' )) ' || voperator ;
END IF;
SET vloop = vloop + 1;
SET l_sqlstatus = 0;
FETCH FROM key_cursor INTO vkey;
END WHILE ;
CLOSE key_cursor;
IF (pDateType = 0) THEN
IF (pDateCondition <> 0) THEN
SET vDateTypeColumn = ' rec.ReportDate ';
ELSE
SET vDateTypeColumn = ' ';
END IF;
ELSE
IF (pDateType = 1) THEN
SET vDateTypeColumn = ' rec.CreateDate ';
ELSE
IF (pDateType = 2) THEN
SET vDateTypeColumn = ' rec.ModifiedDat e ';
ELSE
SET vDateTypeColumn = ' ';
END IF;
END IF;
END IF;
IF (pDateCondition > 0
AND pDateCondition <= 4) THEN
IF (vDateTypeColum n <> ' ') THEN
SET vsqlQuery = vsqlQuery || ' and ';
IF (pDateCondition = 1) THEN
SET vsqlQuery = vsqlquery ||''|| vDateTypeColumn ||''|| '=' ||''|| Cast(pDateOn As varchar(11)) ;
ELSE
IF (pDateCondition = 2) THEN
SET vsqlQuery = vsqlquery ||''|| vDateTypeColumn ||''|| '>' ||''|| Cast(pDateOn As varchar(11)) ;
ELSE
IF (pDateCondition = 3) THEN
SET vsqlQuery = vsqlquery ||''|| vDateTypeColumn ||''|| '<' ||''|| Cast(pDateOn As varchar(11)) ;
ELSE
IF (pDateCondition = 4) THEN
SET vsqlQuery = vsqlquery ||''|| vDateTypeColumn ||''|| '>' ||''|| Cast(pDateOn As varchar(11)) ||''|| 'and'||''|| vDateTypeColumn ||''|| '<' ||''|| Cast(pDateOn As varchar(11)) ;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
IF (pFolderCategor y = 1
OR pFolderCategory = 2) THEN
SET vsqlQuery = vsqlQuery || ' and ';
IF (pFolderCategor y = 1) THEN
SET vsqlQuery = vsqlQuery || ' rec.FolderID = ''' || CAST (pFolderID AS VARCHAR(100)) || '''';
ELSE
IF (pFolderCategor y = 2) THEN
SET vsqlQuery = vsqlQuery || ' rec.FolderID In
(Select Value From F_GetDescendant sFolderID(''' || CAST (pFolderID AS VARCHAR(100)) || ''')
Union Select ''' || CAST (pFolderID AS VARCHAR(100)) || ''')';
END IF;
END IF;
END IF;
IF (pSort = 0) THEN
SET vSortCondition = ' order by rec.FolderID ASC ';
ELSE
IF (pSort = 1) THEN
SET vSortCondition = ' order by rec.ReportDate DESC ';
ELSE
IF (pSort = 2) THEN
SET vSortCondition = ' order by rec.ReportDate ASC ';
ELSE
SET vSortCondition = ' ';
END IF;
END IF;
END IF;
SET vsqlQuery = vsqlQuery || vSortCondition;
EXECUTE IMMEDIATE vsqlQuery;
END
but when I try to execute this SP , giving error, An EXECUTE IMMEDIATE statement contains a SELECT or VALUES
statement. SQLSTATE=42612,
anyone help me please
Regards
Agus Wahyu
