problem converting this query to oracle

[prakashs43]

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 .

[davey23uk]

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

[prakashs43]

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 :
--------------------------

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 :
-------------

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 ...

[davey23uk]

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

[tamilselvan]

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

Tamil

[prakashs43]

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

       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)

long_txt_1.LONG_TXT_ID = version_stem_txt.STEM_TXT_ID (+)

[davey23uk]

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

[prakashs43]

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 )

[davey23uk]

just keep the join syntax from your old version

[marist89]

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???