posted 10 years ago
Hi, I am not sure if the subject heading correctly conveys what I am asking, but here goes...I have the following SQL query which retrieves the correct record from a database for me (tested it in DBVisualizer connected to database). I am trying to figure out what the equivalent would be in JPQL.
SQL statement:
SELECT DISTINCT p.POLICY_1_NUM, p.TOTAL_PAYOUT_AMT, f.*
FROM FOREIGN_PARTY f, POLICY_PAYMENT p
WHERE p.POLICY_1_NUM = '1234567'
AND f.FOREIGN_PARTY_ID = p.FOREIGN_PARTY_ID
I have an Oracle database with a parent table (FOREIGN_PARTY) and a child table (POLICY_PAYMENT), with FOREIGN_PARTY_ID being the Primary Key in FOREIGN_PARTY and Foreign Key in POLICY_PAYMENT.
Parent Entity:
Child Entity:
I have tried this for JPQL (using @NamedQuery, and only need to retrieve data from a couple of columns in the child table):
SELECT f, p.policy1Num, p.totalPayoutAmt FROM ForeignParty f, PolicyPayment p WHERE p.policy1Num = :policy1Num
Which retrieves ALL records from parent table, yet strangely enough lists the policy number entered (the :policy1Num parameter) for all records, which is not correct. I also tried this for JPQL (which I thought more closely resembles the SQL statement above):
SELECT f FROM ForeignParty f JOIN f.policyPaymentCollection p WHERE p.policy1Num = :policy1Num AND f.foreignPartyId = p.foreignPartyId
But I get a The left and right expressions type must be of the same type error, referring to the f.foreignPartyId = p.foreignPartyId condition in the WHERE clause. I know each of those objects refers to a different entity, but both refer to the foreignPartyId variable associated with the PK/FK, so I don't understand why it doesn't work.
I am just trying to retrieve a value (or values) from the child table in addition to the parent table, and then populate a primefaces form with those values. I have no problem with primefaces itself, just the retrieval process as it relates to JPQL. I know it is also because I don't fully understand JPQL (and am likely doing the JPQL queries incorrectly), but am currently going through online tutorials and articles to learn it. If anyone can shed some light for me it would be greatly appreciated. Thank you!