Serge,
Thanks for your quick reply. Attached are examples of the T-SQL code vs. the
SQL PL code.
TSQL:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE GetApplDirectiveByAgency_SP
(
@AgencyID udt_Agency = NULL,
@ApplicationID1 udt_Application = NULL
)
AS
BEGIN
DECLARE
@ReturnValue udt_ReturnValue,
@Exist udt_Exist,
@Row udt_Row,
@Sequence udt_Sequence,
@errorNumber udt_ErrorNumber,
@errorText udt_ErrorText,
@AgencyID1 udt_Application
SELECT @AgencyID1 = Agency_Network.AgencyID1
FROM Agency_Network INNER JOIN Agency_Network Agency_Network_1
ON Agency_Network.AgencyID1 = Agency_Network_1.AgencyID2
WHERE (Agency_Network.RelationshipID = 'Includes')
AND (Agency_Network_1.AgencyID1 = 'User Roles')
AND (Agency_Network_1.RelationshipID = 'Includes')
AND (Agency_Network.AgencyID2 = @AgencyID)
SELECT DISTINCT @AgencyID1 'AgencyID',
Application_Directive.ApplicationID,
Application_Directive.DirectiveType,
Application_Directive.Directive,
Application_Directive.Note,
Application_Directive.HistoryRevisionCount
FROM [Application_Network],
[Application_Directive],
[Agency_Application]
WHERE Application_Network.ApplicationID2 =
Application_Directive.ApplicationID
AND Agency_Application.ApplicationID =
Application_Directive.ApplicationID
AND (Agency_Application.AgencyID = @AgencyID
OR Agency_Application.AgencyID IN (
SELECT Agency_Network.AgencyID1
FROM Agency_Network INNER JOIN Agency_Network Agency_Network_1
ON Agency_Network.AgencyID1 = Agency_Network_1.AgencyID2
WHERE (Agency_Network.RelationshipID = 'Includes')
AND (Agency_Network_1.AgencyID1 = 'User Roles')
AND (Agency_Network_1.RelationshipID = 'Includes')
AND (Agency_Network.AgencyID2 = @AgencyID)))
AND Application_Network.ApplicationID1 = @ApplicationID1
ORDER BY
AgencyID,
Application_Directive.ApplicationID,
Application_Directive.DirectiveType,
Application_Directive.Directive
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SQL PL:
CREATE PROCEDURE db2admin.GetApplDirectiveByAgency1_SP(
IN v_AgencyID VARCHAR(75),
IN v_ApplicationID1 VARCHAR(75))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE v_ReturnValue INT;
DECLARE v_Exist INT;
DECLARE v_Row INT;
DECLARE v_Sequence INT;
DECLARE v_errorNumber INT;
DECLARE v_errorText VARCHAR(64);
DECLARE v_AgencyID1 VARCHAR(75);
DECLARE l_sqlcode INT DEFAULT 0;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT DISTINCT v_AgencyID1 AS AgencyID,
db2admin.Application_Directive.ApplicationID,
db2admin.Application_Directive.DirectiveType,
db2admin.Application_Directive.Directive,
db2admin.Application_Directive.Note,
db2admin.Application_Directive.HistoryRevisionCoun t
FROM db2admin.Application_Network, db2admin.Application_Directive,
db2admin.Agency_Application
WHERE db2admin.Application_Network.ApplicationID2 =
db2admin.Application_Directive.ApplicationID AND
db2admin.Agency_Application.ApplicationID =
db2admin.Application_Directive.ApplicationID AND
(CAST(db2admin.Agency_Application.AgencyID AS VARCHAR(75)) =
v_AgencyID
OR
db2admin.Agency_Application.AgencyID IN
(SELECT db2admin.Agency_Network.AgencyID1
FROM db2admin.Agency_Network INNER JOIN db2admin.Agency_Network AS
Agency_Network_11 ON
db2admin.Agency_Network.AgencyID1 = Agency_Network_11.AgencyID2
WHERE CAST(db2admin.Agency_Network.RelationshipID AS VARCHAR(75)) =
'Includes' AND
CAST(Agency_Network_11.AgencyID1 AS VARCHAR(75)) =
'User Roles' AND
CAST(Agency_Network_11.RelationshipID AS VARCHAR(75)) =
'Includes' AND
CAST(db2admin.Agency_Network.AgencyID2 AS VARCHAR(75)) =
v_AgencyID))
AND
CAST(db2admin.Application_Network.ApplicationID1 AS VARCHAR(75)) =
v_ApplicationID1
ORDER BY AgencyID, db2admin.Application_Directive.ApplicationID,
db2admin.Application_Directive.DirectiveType,
db2admin.Application_Directive.Directive ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE;
SELECT db2admin.Agency_Network.AgencyID1
INTO v_AgencyID1
FROM db2admin.Agency_Network INNER JOIN db2admin.Agency_Network AS
Agency_Network_1 ON
db2admin.Agency_Network.AgencyID1 = Agency_Network_1.AgencyID2
WHERE CAST(db2admin.Agency_Network.RelationshipID AS VARCHAR(75)) =
'Includes' AND
CAST(Agency_Network_1.AgencyID1 AS VARCHAR(75)) = 'User Roles' AND
CAST(Agency_Network_1.RelationshipID AS VARCHAR(75)) = 'Includes'
AND
CAST(db2admin.Agency_Network.AgencyID2 AS VARCHAR(75)) = v_AgencyID
FETCH FIRST 1 ROWS ONLY;
OPEN temp_cursor;
END
Any insights you may have would be greatly appreciated. I'll jot down mtk's
email address. Thanks.
Adam
"Serge Rielau" <sr*****@ca.eye-bee-m.com> wrote in message
news:bu**********@hanover.torolab.ibm.com...
Adam,
The same is true in DB2. However MTK's first concern is with correctness.
There may be some behavior or feature in SQL Server that is different
enough from DB2 so the MTK felt the need to transpose to cursors.
Possibly there are function calls that were mapped to procedures?
If you show me an example (T-SQL vs generated SQL PL) I may be able to
tell you why the MTK worked the way it did.
Also not ethat you can send comments and requests to mt*@us.ibm.com
The MTK team is very eager to improve the tool and they have a quick
turnaround.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
