Hi all. I wonder which of these two variants is optimal, the objective
is to select data depending on the input parameters:
1 variant - The selection procedure involves all joins:
SET TERM ^;
CREATE PROCEDURE my_select_proc (
param1 TYPE OF COLUMN table1.field1,
param2 TYPE OF COLUMN table2.field1)
RETURNS (
field1_T1 TYPE OF COLUMN table1.field1,
field2_T1 TYPE OF COLUMN table1.field2,
field1_T2 TYPE OF COLUMN table2.field1,
field2_T2 TYPE OF COLUMN table2.field2)
AS
BEGIN
FOR
SELECT
T1.Field1, T1.Field2, T2.Field1, T2.Field2
FROM
table1 T1
INNER JOIN table2 T2
ON T2.id = T1.id
WHERE
T1.Field1 = param1 AND T2.Field2 = param2
INTO
:field1_T1, :field2_T1, :field1_T2, :field2_T2 DO BEGIN
SUSPEND;
END
END^
SET TERM ;^
2 variant- Create a view, and use them in the procedure:
SET TERM ^;
CREATE VIEW my_view (field1_T1, field2_T1, field1_T2, field2_T2)
AS
SELECT
T1.Field1 field1_T1,
T1.Field2 field2_T1,
T2.Field1 field1_T2,
T2.Field2 field2_T2
FROM
table1 T1
INNER JOIN table2 T2
ON T2.id = T1.id;
CREATE PROCEDURE my_select_proc (
param1 TYPE OF COLUMN table1.field1,
param2 TYPE OF COLUMN table2.field1)
RETURNS (
field1_T1 TYPE OF COLUMN table1.field1,
field2_T1 TYPE OF COLUMN table1.field2,
field1_T2 TYPE OF COLUMN table2.field1,
field2_T2 TYPE OF COLUMN table2.field2)
AS
BEGIN
FOR
SELECT
V.field1_T1,
V.field2_T1,
V.field1_T2,
V.field2_T2
FROM
my_view V
WHERE
V.field1_T1 = param1 AND V.field1_T2 = param2
INTO
:field1_T1, :field2_T1, :field1_T2, :field2_T2 DO BEGIN
SUSPEND;
END
END^
SET TERM ;^
Best Regards,
Sergio
PD: The selection can include up to ten tables. I use FB SS v2.5.1
