I simply defined the view in the normal DDL way (create view...) and after I was happy with the SQL view, I added it to the torque schema xml. Torque thinks it is a table and it works great as long as you don't try to update it... Here is a sample below:
CREATE OR REPLACE VIEW JOBS_VIEW ( POSITION,
ORDERING, SHORT_NAME ) AS SELECT FFV.FLEX_VALUE AS POSITION,
FFV.ATTRIBUTE2 AS ORDERING,
JN.SHORT_NAME AS SHORT_NAME
FROM FND_FLEX_VALUES FFV,
FND_FLEX_VALUE_SETS FFVS,
JOBS_NAME JN
WHERE FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID AND
FFVS.FLEX_VALUE_SET_NAME LIKE 'SSI_HR_JOB%' AND
FFV.ENABLED_FLAG = 'Y' AND
FFV.ATTRIBUTE1='V' AND
FFV.FLEX_VALUE=JN.LONG_NAME(+)
ORDER BY ORDERING,POSITION;
then in DB-schema.xml
<table name="JOBS_VIEW" skipSql="true"
description="<b style='color:blue;'>CUSTOM</b> Table of Job names, their short name and ordering. Custom view of FND_FLEX_VALUES, FND_FLEX_VALUE_SETS for SSI_HR_JOB%, ENABLED, and V" idMethod="none">
<column name="POSITION" type="VARCHAR" size="150" required="true" primaryKey="true"/>
<column name="ORDERING" type="VARCHAR" size="240" required="false" />
<column name="SHORT_NAME" type="VARCHAR" size="10" required="false"/>
</table>
Hope that helps... David
T E Schmitz wrote:
Hello David, Thank you for your reply.
How did you map Torque objects to VIEWs as these wouldn't have been in the xml schema?
I haven't had any other suggestions but I noticed a posting on the Torque developers list: somebody has implemented explicit inner joins, left joins and right joins and offered to contribute his code.
Best Regards,
Tarlika Elisabeth Schmitz
David Hainlin wrote:
Greetings, Don't know if you got your question answered, but the only way I've done outer joins in Torque is to create a view and then map a torque object to it. Has worked pretty well.
David
-----Original Message----- From: T E Schmitz <[EMAIL PROTECTED]> To: Apache Torque Users List <[EMAIL PROTECTED]> Date: Sun, 02 May 2004 17:43:21 +0100 Subject: left outer join
I have 3 tables SECTION and BRAND and MODEL. SECTION is related to BRAND and to MODEL via two foreign keys. I would like to select ALL SECTIONs whether the FKs are null or not and fetch the BRAND and MODEL attributes in one SQL statement. In other words I need a double outer join:
SELECT SECTION.SECTION_PK, SECTION.SECTION_NAME, SECTION.BRAND_FK, SECTION.MODEL_FK, BRAND.BRAND_NAME MODEL.MODEL_NAME FROM SECTION left outer join MODEL on MODEL_PK =MODEL_FK left outer join BRAND on BRAND_PK =BRAND_FK
I have seen some old discussion threads from 2002 suggesting that Torque does not support outer joins. Is this still the case?
If so, can anyone suggest an efficient work-around?
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
