problem converting this query to oracle

Hi friends,
could anybody pls help me in converting below access query into oracle query .
i am not able to understand from clause here .

SELECT DISTINCT item.SCALE_ID, item.ITEM_ID, item.COMPLETE_DT, long_txt_1.LONG_TXT AS Stemtxt, long_txt.LONG_TXT, scale.SCALE_NM
FROM (((version_item INNER JOIN (version_stem_txt INNER JOIN long_txt AS long_txt_1 ON version_stem_txt.STEM_TXT_ID = long_txt_1.LONG_TXT_ID) ON version_item.QUEST_ID = version_stem_txt.QUEST_ID) INNER JOIN long_txt ON version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID) INNER JOIN item ON version_item.ITEM_ID = item.ITEM_ID) INNER JOIN scale ON item.SCALE_ID = scale.SCALE_ID
WHERE (((item.SCALE_ID)=[Forms]![FrmFindMatchItems]![subfrmItem].[Form]![VerScale]) AND ((item.ITEM_ID)<>[Forms]![FrmFindMatchItems]![subfrmItem].[Form]![item_id]) AND ((item.COMPLETE_DT)<>#1/1/1950#) AND ((long_txt_1.LONG_TXT) Like "*" & [Forms]![FrmFindMatchItems]![SearchTxt] & "*"));

CAN anybody let me know if i am missing anything in below mentioned query ...

i cannot execute access query as i don't have access to cross check number of records

SELECT
DISTINCT item.SCALE_ID,
item.ITEM_ID,
item.COMPLETE_DT,
long_txt_1.LONG_TXT AS Stemtxt,
long_txt.LONG_TXT,
scale.SCALE_NM
From
long_txt long_txt_1 , long_txt , item , scale , version_item , version_stem_txt
where
version_stem_txt.STEM_TXT_ID = long_txt_1.LONG_TXT_ID
and version_item.QUEST_ID = version_stem_txt.QUEST_ID
and version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID
and version_item.ITEM_ID = item.ITEM_ID
and item.SCALE_ID = scale.SCALE_ID
-- and item.SCALE_ID = &scale_id
-- and item.ITEM_ID)<> &item_id
and item.COMPLETE_DT != to_date( '1/1/1950' , 'mm/dd/yyyy')
-- and long_txt_1.LONG_TXT Like '*%*'

Thanks for your help .

format your code using the [ code ] and [ / code ] tags, it's really hard to read otherwise

Hi dave,

sorry for not formatting code in the first place ..

pls find formatted code ...
if i am missing anything while converting to oracle sql

MS Access generated Code :
--------------------------

Code:

---

SELECT
      DISTINCT item.SCALE_ID,
      item.ITEM_ID,
      item.COMPLETE_DT,
      long_txt.LONG_TXT,
      scale.SCALE_NM,
      long_txt_1.LONG_TXT
FROM
      scale INNER JOIN (((version_item INNER JOIN long_txt
            ON version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID)
            INNER JOIN item
            ON version_item.ITEM_ID = item.ITEM_ID)
            LEFT JOIN (long_txt AS long_txt_1
            RIGHT JOIN version_stem_txt
            ON long_txt_1.LONG_TXT_ID=version_stem_txt.STEM_TXT_ID)
            ON version_item.QUEST_ID = version_stem_txt.QUEST_ID)
            ON scale.SCALE_ID = item.SCALE_ID
WHERE
            (((item.SCALE_ID)=[Forms]![FrmFindMatchItems]!   
              [subfrmItem].[Form]![VerScale])
              AND ((item.COMPLETE_DT)>#1/1/1950#)
            AND ((long_txt.LONG_TXT) Like "*" &
              [Forms]![FrmFindMatchItems]![SearchTxt] & "*"));

---

oracle code :
-------------

Code:

---

CREATE OR REPLACE VIEW QRYMATCHITEMS_VW
    ( SCALE_ID, ITEM_ID, COMPLETE_DT, LONG_TXT, SCALE_NM,
    STEM_TXT )
AS SELECT 
    DISTINCT item.SCALE_ID, 
    item.ITEM_ID, 
    item.COMPLETE_DT, 
    long_txt.LONG_TXT, 
    scale.SCALE_NM, 
    long_txt_1.LONG_TXT as stem_txt 
FROM
    scale , version_item , long_txt , item , long_txt long_txt_1 , 
    version_stem_txt 
where 
    version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID 
    and version_item.ITEM_ID = item.ITEM_ID 
    and  long_txt_1.LONG_TXT_ID = version_stem_txt.STEM_TXT_ID 
    and  version_item.QUEST_ID = version_stem_txt.QUEST_ID 
    and  scale.SCALE_ID = item.SCALE_ID 
    AND item.COMPLETE_DT > to_date(  '1/1/1950' , 'mm/dd/yyyy')

---

please advise if i am missing outer joins etc ...

oracle supports writing joins in that way, so you can write the joins in the same way if you prefer

Where are LEFT JOIN AND RIGHT JOIN in the Oracle code?
You have to use either (+) or LEFT OUTER JOIN.

Tamil

Code:

---


CREATE OR REPLACE VIEW QRYMATCHITEMS_VW
    ( SCALE_ID, ITEM_ID, COMPLETE_DT, LONG_TXT, SCALE_NM,
    STEM_TXT )
AS SELECT 
    DISTINCT item.SCALE_ID, 
    item.ITEM_ID, 
    item.COMPLETE_DT, 
    long_txt.LONG_TXT, 
    scale.SCALE_NM, 
    long_txt_1.LONG_TXT as stem_txt 
FROM
    scale , version_item , long_txt , item , long_txt long_txt_1 , 
    version_stem_txt 
where 
    version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID 
    and version_item.ITEM_ID = item.ITEM_ID 
    and  long_txt_1.LONG_TXT_ID = version_stem_txt.STEM_TXT_ID (+)
    and  version_item.QUEST_ID = version_stem_txt.QUEST_ID 
    and  scale.SCALE_ID = item.SCALE_ID 
    AND item.COMPLETE_DT > to_date(  '1/1/1950' , 'mm/dd/yyyy')

---

should this statement be interpreted like this

Code:

---

      LEFT JOIN (long_txt AS long_txt_1
      RIGHT JOIN version_stem_txt
      ON long_txt_1.LONG_TXT_ID=version_stem_txt.STEM_TXT_ID)

---

Code:

---

long_txt_1.LONG_TXT_ID = version_stem_txt.STEM_TXT_ID (+)

---

no need to convert it, the syntax is fine and is perfectly valid (the joins)

Hi dave ,

do i need to use (+) operator ...
for left join and right join

hi tamilselvam ,
where should i use + operator ...

I don't understand this bit ( as i don't see outer join )

just keep the join syntax from your old version

Quote:

---

Originally posted by prakashs43

I don't understand this bit ( as i don't see outer join )

---

The docs explain it quite well...maybe you should try them first next time???