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!

Anonymous User

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.