db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Hainlin <dhain...@comcast.net>
Subject Re: left outer join
Date Tue, 04 May 2004 21:50:14 GMT
Hello Tarlika,
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="&lt;b style='color:blue;'>CUSTOM&lt;/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 <mailreg@numerixtechnology.de>
>>>> To: Apache Torque Users List <torque-user@db.apache.org>
>>>> 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: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message