Combining Alias Tables in Actuate 7 - ODBC

[shenay921]

I have two main tables EMPPER and EMPFUNDA connected by EMP_ID.

EMPPER (EMP_ID, and other fields I don't need)
EMPFUNDA (EMP_ID, FUND_DATE, ACT_TYPE, ACT_AMT, etc).

The ACT_TYPE is an E or an M and employees usually have both with and amt specific to that TYPE (The amount varies and can be zero). The EMP_ID repeats itself as it creates a row once for E and then again for M.

EX: 4556789 9/21/05 E 56.70
4556789 9/21/05 M 88.20

1234567 9/21/05 E 66.20
1234567 9/21/05 M 77.00

The only unique identifier is a column I think Actuate creates called COLUMN_KEY.

I need a report that shows: EMP_ID, FUND_DATE, ACT_TYPE(E), ACT_AMT(E),ACT_TYPE(M), ACT_AMT(M) -- all on one row

EX. 1234567 9/21/05 M 77.00 E 66.20

-- the problem is I keep getting the E and the M info on two rows.

I have tried alias tables, grouping, different kinds of joins but can't get the info in the correct format.

Appreciate any help!

Thank you!

 

[johnberchmans]

You can use a SingleInputFilter and merge the two rows to get one row.

I hope this helps.

-- JB

 

[shenay921]

Hi JB,

Thanks for the response -- how do I do the SingleInputFilter? I am not finding it in the Actuate book I have.

Thank you!

Laura

 

[johnberchmans]

You can find a sample report at

C:\Program Files\Actuate7\eRDPro\Examples\DataAccess\SingleInputFilter

-- JB

 

[shenay921]

Hi JB,

Appreciate all of your help!

I am looking at the example. So any ideas on how this would look? How would this merge the two rows into one?

Something like...

Function Fetch() As AcDataRow
Do While True
Set Fetch = InputAdapter.Fetch()
If Fetch Is Nothing Then
Exit Function
End If

If Fetch.GetValue("EMPFUNDA_EMP_ID") = "EMPPER_EMP_ID" Then
Exit Function
End If
Loop
End Function

Something like that?

 

[johnberchmans]

Instead of using the SingleInputFilter, you can code the SQL as follows:
*****
SELECT EMP_ID, FUND_DATE, ACT_TYPE_E, ACT_AMT_E, ACT_TYPE_M, ACT_AMT_M
FROM
(SELECT A.EMP_ID, FUND_DATE, ACT_TYPE AS ACT_TYPE_E, SUM(ACT_AMT) AS ACT_AMT_E
FROM EMPPER A, EMPFUNDA B
WHERE A.EMP_ID = B.EMP_ID
AND B.ACT_TYPE = 'E'
GROUP BY A.EMP_ID, FUND_DATE, ACT_TYPE AS ACT_TYPE_E) TE,
(SELECT A.EMP_ID, FUND_DATE, ACT_TYPE AS ACT_TYPE_M, SUM(ACT_AMT) AS ACT_AMT_M
FROM EMPPER A, EMPFUNDA B
WHERE A.EMP_ID = B.EMP_ID
AND B.ACT_TYPE = 'M'
GROUP BY A.EMP_ID, FUND_DATE, ACT_TYPE AS ACT_TYPE_E) TM
WHERE TE.EMP_ID = TM.EMP_ID
AND TE.FUND_DATE = TM.FUND_DATE
ORDER BY EMP_ID, FUND_DATE
*****
If you have already tried this, please ignore this.
I need some time to write the code for merging two rows.

-- JB

 

[shenay921]

Thank you again for looking at this.

So, in the first SELECT it will select the fields without specifying the table?

I will try the code as I have not tried it this way yet.

Thanks again!

 

[johnberchmans]

You have to qualify the columns EMP_ID, FUND_DATE in the top SELECT as follows:

SELECT TE.EMP_ID, TE.FUND_DATE, ACT_TYPE_E, ACT_AMT_E, ACT_TYPE_M, ACT_AMT_M

Sorry about that.
-- JB

 

[shenay921]

Hi JB,

An error came up saying that there was an error near the AS stmt.

Here's the code I ran:

SELECT EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE, ACT_TYPE_E, ACT_AMT_E, ACT_TYPE_M, ACT_AMT_M
FROM
(SELECT A.EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE, ACT_TYPE AS ACT_TYPE_E, SUM(ACT_AMT) AS ACT_AMT_E
FROM EMPPER A, EMPFUNDA B
WHERE A.EMP_ID = B.EMP_ID
AND B.ACT_TYPE = 'E'
GROUP BY A.EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE, ACT_TYPE AS ACT_TYPE_E) TE,
(SELECT A.EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE, ACT_TYPE AS ACT_TYPE_M, SUM(ACT_AMT) AS ACT_AMT_M
FROM EMPPER A, EMPFUNDA B
WHERE A.EMP_ID = B.EMP_ID
AND B.ACT_TYPE = 'M'
GROUP BY A.EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE, ACT_TYPE AS ACT_TYPE_E) TM,
WHERE TE.EMP_ID = TM.EMP_ID
AND TE.CHECK_NO = TM.CHECK_NO
AND TE.BPLAN_CODE = TM.BPLAN_CODE
AND TE.ACT_DATE = TM.ACT_DATE
AND BPLAN_CODE<>'457'
ORDER BY EMP_ID

The additional fields are from the empfunda Table.

 

[johnberchmans]

In the SQL please remove the AS ACT_TYPE_E term from the Group by clause.

And the EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE columns in the top-most SELECT should be qualified.

And in the join AND BPLAN_CODE<>'457', BPLAN_CODE[/color red] should be qualified.

Please run the SQL (after modification) in a SQL tool and test it before placing it in the report.

I hope this helps.

-- JB

 

[shenay921]

Hi JB,

I appreciate your help.

Ok, I pared down the code just to try to get it to run to:

(I am getting the error: You tried to execute a query that does not include the specified expression ACT_TYPE_E as part of an aggregate function.)

SELECT dbo_empper.EMP_ID, TE.CHECK_NO, TE.BPLAN_CODE, TE.ACT_DATE, TE.ACT_TYPE_E, TE.ACT_AMT_E, TE.ACT_TYPE_M, TM.ACT_AMT_M
FROM
(SELECT A.EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE, ACT_TYPE AS ACT_TYPE_E, SUM(ACT_AMT) AS ACT_AMT_E
FROM dbo_EMPPER A, dbo_EMPFUNDA B
WHERE A.EMP_ID = B.EMP_ID
AND B.ACT_TYPE = 'E'
GROUP BY A.EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE) TE,
(SELECT A.EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE, ACT_TYPE AS ACT_TYPE_M, SUM(ACT_AMT) AS ACT_AMT_M
FROM dbo_EMPPER A, dbo_EMPFUNDA B
WHERE A.EMP_ID = B.EMP_ID
AND B.ACT_TYPE = 'M'
GROUP BY A.EMP_ID, CHECK_NO, BPLAN_CODE, ACT_DATE) TM;