Can I call a function from Oracle? 1

[qu1ncy]

Hi, I'm just starting out with Crystal and am wondering how to call a function which is in an Oracle schema whose tables I am pulling data from.
We are using Crystal Reports 7.0. The function is called Get_Hire_Date.

Function GET_HIRE_DATE
(Empid_In IN Varchar2)
RETURN date IS
Hire_date date;
BEGIN
Select eff_date
into Hire_date
from hris_employee_position
where empid = Empid_In
and version = (select max(version)
from hris_employee_position
where change_id in (1,21)
and empid = Empid_In);
RETURN Hire_date;
END;

If I can't call it to use in Crystal, how to I build this using a formula? Any help with this would be appreciated.

Thanks

Q

 

[dgillz]

When you say a function from Oracle, why don't you just pull the data into crystal? Is that data not stored in the oracle database?

I do not beleive you can use an oracle function in crystal, however if this is a calculated field in oracle you can most likely calculate in within crystal. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Naith]

Hey Quincy,

You can't call that function in Crystal. You can export the function results to a temporary table, sp, or view, or cut the function code out into a sql expression and query that.

Alternatively, you can use a formula to get the information for you. (For this to work, you have to group by {HRIS_Employee_Position.EmpID}).

WhilePrintingRecords;
NumberVar HireDate;

If {{HRIS_Employee_Position.Version} =
Maximum({HRIS_Employee_Position.Version,{HRIS_Employee_Position.EmpID}) and
{HRIS_Employee_Position.ChangeID} in [1,21]
Then HireDate := {HRIS_Employee_Position.Eff_Date}

Naith

 

[qu1ncy]

Hi, Thank you both (dgillz & Naith) for your replies.
Naith: appreciate the time you spent on this. I will give the formula a try and let you know the outcome.

Quincy

 

[qu1ncy]

Hey Naith,

I'm not certain I've proceeded correctly, but here goes. I've added a Group based on {HRIS_Employee_Position.EmpID}. In my SQL query I'm also selecting records 'where' "HRIS_EMPLOYEE_POSITION"."STATUS" = 'T'. This pulls in all of an employee's 'events' that have been entered on system, where they have terminated that position. Typically, a summer student who works for us 3 summers in a row will appear with 3 records in their group. So far so good.
I've then created a new Formula called Get_Hire_Date (which I was going to insert into the details as "Hire Date". right track?) and added the following:

WhilePrintingRecords;
numberVar HireDate;

if {{HRIS_EMPLOYEE_POSITION.VERSION} =
Maximum ({HRIS_EMPLOYEE_POSITION.VERSION},
{HRIS_EMPLOYEE_POSITION.EMPID}) and
{HRIS_EMPLOYEE_POSITION.CHANGE_ID} in [1,21]

Then HireDate:={HRIS_EMPLOYEE_POSITION.EFF_DATE}

Here's the snag. When I click the 'Check' button, Crystal advises me that "This field name is not known." and places the cursor just after the 'If'.
I've double-checked my field names and they are correct.

What have I done wrong here?

Q

 

[Naith]

This is carelessness on my behalf, I'm afraid.

Where I said "{{HRIS_EMPLOYEE_POSITION.VERSION}", what I meant to say was {HRIS_EMPLOYEE_POSITION.VERSION} - (single '{').

Sorry - I should learn to read my code before I submit.

Naith

 

[qu1ncy]

Hi Naith,

I got it working! I ended up creating a view (employing the function within the view) in the Oracle schema. I based my CR report on that view.
Works beautifully !! I get exactly what I want.

Thanks for the lead.
The reference manual I was using(CR 7 for Dummies)barely mentions views. Time for a developer's reference!!

Q