Here is a brain breaker:
We have the following table structures:
company: fields company_id, name, etc.
guid: fields guid, system, primary key.
The first is a standard customer table. The latter is used to link this record with others from other systems, usign a common GUID. so two records would be created if a company exists on a CRM and and ERP system: guid1, "ERP", "PK1" and guid1, "CRM", "CRM99". By joining these two together I should be able to see all existing references in all systems.
The problem we have is that we need to create a view, where we retrieve the ERP PK for the user, from the CRM system.
A user is linked to an ERP system. The user should only see his/her ERP numbers. But there may be records with no ERP number in the GUID table. In this case we shoudl return null
In oracle we managed to do this via a function:
select company.*, employee.name_of_erp,
adcp_get_erp_num (comapny.company_id , employee.name_of_erp) erp
from company, employee
where adcp_get_erp_num does the finding of the ERP PK for a given company_id and ERP, and returns null if not found.
We cannot use functions in Selects in SQLBAse... and how to do this as an external fucntion DLL is a wonder!
Any ideas? Views must run fast!
We have the following table structures:
company: fields company_id, name, etc.
guid: fields guid, system, primary key.
The first is a standard customer table. The latter is used to link this record with others from other systems, usign a common GUID. so two records would be created if a company exists on a CRM and and ERP system: guid1, "ERP", "PK1" and guid1, "CRM", "CRM99". By joining these two together I should be able to see all existing references in all systems.
The problem we have is that we need to create a view, where we retrieve the ERP PK for the user, from the CRM system.
A user is linked to an ERP system. The user should only see his/her ERP numbers. But there may be records with no ERP number in the GUID table. In this case we shoudl return null
In oracle we managed to do this via a function:
select company.*, employee.name_of_erp,
adcp_get_erp_num (comapny.company_id , employee.name_of_erp) erp
from company, employee
where adcp_get_erp_num does the finding of the ERP PK for a given company_id and ERP, and returns null if not found.
We cannot use functions in Selects in SQLBAse... and how to do this as an external fucntion DLL is a wonder!
Any ideas? Views must run fast!