sql functions

[#1]

Dear Team,

is there a way I can convert Oracle functions to Zeplin (Kudu) equivalent?

Below query calls function "apps.xxmt_getvendor_emailadd (ai.vendor_site_id)"

SELECT
sysdate, apps.xxmtg_get_org_name (aip.org_id) operating_unit,
ai.creation_date inv_creation_date, ai.invoice_date,
ai.invoice_received_date Inv_RecvDte,
aba.batch_name, ai.invoice_num,
ai.description invoice_desc, ai.invoice_amount,
aps.payment_priority, aps.due_date,
ac.currency_code, ac.creation_date pmt_creation_date,
apps.X_MTG_get_user_name(ac.created_by) created_by, ac.status_lookup_code,
ac.check_date document_date, ac.check_number,
aip.amount pmt_doc_amount, pd.payment_document_name,
ac.bank_account_name,
ac.VENDOR_NAME Supplier_Name, pov.segment1 SupplierNum,
b.bank_name Supplier_Bank, to_char(b.bank_number) Supplier_Banknum,
br.bank_branch_name Supplier_BankBranch, iby_ac.bank_account_num Supplier_BankAccount,
apps.xxmt_getvendor_emailadd (ai.vendor_site_id) supplier_email,trunc(ac.CREATION_DATE) Payment_Creation_Date,

...

Oracle function <apps.xxmt_getvendor_emailadd> content:

create or replace Function xxmt_getvendor_emailadd(P_Vendor_Site_Id In Number)
Return Varchar2
Is

LvEmailaddress Varchar2(240):=NULL;

Begin

Select email_address
Into LvEmailaddress
From ap_supplier_sites_all
Where vendor_site_id=P_Vendor_Site_Id;

Return LvEmailaddress;

Exception When Others Then

LvEmailaddress:='';

Return LvEmailaddress;

End xxmt_getvendor_emailadd;

Thanks,

Roshan

[#2]

Hi @roshanbi ,

thank you for raising this question on the Cloudera Community!

Do I understand correctly that you are storing data in Kudu and you would like to re-use your Oracle functions and use Zeppelin as your notebook?

Please note, Kudu does not have an SQL Engine, hence it does not have JDBC driver available. First, you need to decide what SQL Engine will be used in combination with Kudu. Would it be for instance Impala, Spark, Nifi...?

Once you know what SQL Engine will be used with Kudu, your next question would be how easy is it to translate your Oracle functions into that specific SQL dialect.

Hope it helps!

Kind regards:

Ferenc

[#3]

Hi,

yes I would like to re-use your Oracle functions and use Zeppelin as your notebook.

we are using impala as SQL engine.

How do I translate that SQL function to Oracle dialect?

Regards,

Roshan

[#4]

Hello @roshanbi ,

thank you for the clarification. I am not aware of any automated way of doing the translation. 

If I was facing with this task, I would dive into the differences between these SQL dialects and if Kudu needs some different design considerations when building up the queries and either to it manually or consider if it takes less effort to build custom automation, then build scripts for the job.

Kind regards:

Ferenc