cx_oracle question

[V1212]

import cx_Oracle
import pandas as pd

pointer = connection.cursor()


# part 1
query = """
SELECT a.HM_ID, c.DI_ID
FROM wh.wc206ng_m_08 a, wh.wc26ng_m_03 b,wh.wc201mb c
WHERE a.p_mb_id = b.mb_id
 """

df = pd.read_sql(query, connection)


# Part 2
query2 = """
SELECT a.*, b.PODE,b.CODE
FROM wh.wc201di_06 b 
JOIN df a
ON a.DI_ID = b.DI_ID"""

df2 = pd.read_sql(query2,connection)
print(df2)


pointer.close()
connection.close()

print("Connection Closed")

I keep getting this error message for query 2:

ORA-00942: table or view does not exist

Query1 works on its own. I believe I'm getting this error message because Im using the results of query1 in query2. I dont know how to fix this issue.

Thanks for the help!

[BlakeTerhune]

Where you are referring to df in query2, it's just a string. There's nothing to distinguish df in query 2 as a Pandas dataframe object in memory vs a table named "df" in your Oracle connection. You would need to refer to df in your query as a variable.

query2 = f"""
    SELECT a.*, b.PODE,b.CODE
    FROM wh.wc201di_06 b
    JOIN {df} a
    ON a.DI_ID = b.DI_ID"""

However, I don't use Pandas so I'm not sure how exactly you would go about this. I suspect you would be better off not mixing use of cx_Oracle and Pandas. Either query all the data from your Oracle tables into their own Pandas dataframes and do the joins and queries on the dataframes, or build out PL-SQL to do the query solely using cx_Oracle.

[Anonymous User · Accepted solution]

You could nest the first query into the second in sql fashion-

# Part 2
query2 = """
SELECT a.*, b.PODE, b.CODE
FROM wh.wc201di_06 b 
JOIN (SELECT c.HM_ID, e.DI_ID
FROM wh.wc206ng_m_08 c, wh.wc26ng_m_03 d, wh.wc201mb e
WHERE c.p_mb_id = d.mb_id) a
ON a.DI_ID = b.DI_ID"""

df2 = pd.read_sql(query2,connection)
print(df2)

As written, the sql engine would try to find whatever the df value is within the database tables set in the oracle connection. If the nesting statements doesn't work, try reading both queries into pandas and then merge like @BlakeTerhune mentions. 

[V1212]

Thanks, I was able to use nesting and common table expression to get a working script.