Oracle to Hadoop query

tableaucommunity@tableau.com — Mon, 09 Jun 2014 11:55:00 GMT

Hi All,

I am having an issue with a oracle query that i have converted to Hadoop hive but it keeps complaining that the table is missing. Oracle query runs fines its the hadoop query that is giving problems.. Any help will be helpful?

Here are the queries

ORACLE Query:

SELECT vendor_name, segment1, e.full_name, inv.po_number, inv.invoice_number,

inv.ap_term_name, inv.invoice_date, inv.invoice_received_datetime,

inv.creation_datetime, inv.terms_date, ps.discount_date, ps.due_date, ip.CHECK_date,

inv.invoice_usd_amount, ps.discount_avail_usd_amount,

inv.discount_taken_usd_amount, ip.discount_lost_usd_amount,

inv.paid_usd_amount, inv.payment_status_flag,h.hold_lookup_code,h.hold_reason,

decode(hold_lookup_code,NULL,'N','Y') Hold_flag,source_call_name,organization_name

FROM cx_ap_invoices_rpt inv,

ap_paymentschedules_dfact ps,

ap_invoicepayments_dfact ip,

ap_hold_buyer h,

employee_dim e,

vendor_dim v,

source_system_dim ssd,

organization_dim org

WHERE inv.fact_key = ps.invoice_key

AND inv.md_source_system = ps.md_source_system

AND ps.fact_key = ip.paymentschedule_key(+)

AND ps.md_source_system = ip.md_source_system(+)

and inv.invoice_header_id=h.ap_invoices_lkp(+)

and inv.md_source_system=h.md_source_system(+)

AND inv.buyer_key = e.employee_key

AND v.vendor_key = inv.vendor_key

and inv.md_source_system=ssd.source_system_id

and inv.organization_key=org.organization_key

AND inv.md_source_system = 8

AND v.segment1 IN ('132613', '154942', '135932')

AND ps.discount_date >= '01-JAN-2014'

Corresponding Hadoop Query:

SELECT vendor_name, segment1, e.full_name, inv.po_number, inv.invoice_number,

inv.ap_term_name, inv.invoice_date, inv.invoice_received_datetime,

inv.creation_datetime, inv.terms_date, ps.discount_date, ps.due_date, ip.CHECK_date,

inv.invoice_usd_amount, ps.discount_avail_usd_amount,

inv.discount_taken_usd_amount, ip.discount_lost_usd_amount,

inv.paid_usd_amount, inv.payment_status_flag,h.hold_lookup_code,h.hold_reason,

(case when hold_lookup_code is null then 'N' else 'Y' end) Hold_flag,source_call_name,organization_name

from

cx_ap_invoices_rpt inv

inner join

ap_paymentschedules_dfact ps

inv.fact_key = ps.invoice_key

AND inv.md_source_system = ps.md_source_system

left outer join

ap_invoicepayments_dfact ip

ps.fact_key = ip.paymentschedule_key

AND ps.md_source_system = ip.md_source_system

left outer join

ap_hold_buyer h

inv.invoice_header_id=h.ap_invoices_lkp

and

inv.md_source_system=h.md_source_system

inner join

employee_dim e

inv.buyer_key = e.employee_key

inner join

vendor_dim v

v.vendor_key = inv.vendor_key

inner join

source_system_dim ssd

inv.md_source_system=ssd.source_system_id

inner join

organization_dim org

inv.organization_key=org.organization_key

where

inv.md_source_system = 8

AND v.segment1 IN ('132613', '154942', '135932')

AND ps.discount_date >= '01-JAN-2014'

Content in Tableau Community Forums

Oracle to Hadoop query