problem converting this query to oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: problem converting this query to oracle

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    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 .

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333

    Re: problem converting this query to oracle

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

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 ...

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    oracle supports writing joins in that way, so you can write the joins in the same way if you prefer

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Where are LEFT JOIN AND RIGHT JOIN in the Oracle code?
    You have to use either (+) or LEFT OUTER JOIN.

    Tamil

  6. #6
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 (+)

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no need to convert it, the syntax is fine and is perfectly valid (the joins)

  8. #8
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 )

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    just keep the join syntax from your old version
    Last edited by davey23uk; 01-10-2005 at 03:38 PM.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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???
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width