convert MS access SQL to Oracle SQL

[guestAsh]

Hi,

Could anyone help me to convert this Access SQL that I designed in MS ACCESS to oracle SQL:


I’ve tried doing this myself, but i just don't have enough knowledge of oracle SQL. any help will be gratefully received!

SELECT GPS_TBLDESIGN.QUEST_NO, GPS_TBLDESIGN.DESIGN_ID, GPS_TBLFIELDSET.DESIGN_ID
FROM (GPS_TBLANSWER INNER JOIN (GPS_TBLPAGE INNER JOIN ((GPS_TBLDESIGN INNER JOIN GPS_TBLFIELDSET ON GPS_TBLDESIGN.DESIGN_ID = GPS_TBLFIELDSET.DESIGN_ID) INNER JOIN GPS_TBLLEGEND ON GPS_TBLFIELDSET.LEGEND_ID = GPS_TBLLEGEND.LEGEND_ID) ON GPS_TBLPAGE.LEGENDID = GPS_TBLLEGEND.LEGEND_ID) ON GPS_TBLANSWER.QUEST_NO = GPS_TBLDESIGN.QUEST_NO) LEFT JOIN GPS_TBLMATRIX_XREF ON GPS_TBLDESIGN.DESIGN_ID = GPS_TBLMATRIX_XREF.DESIGN_ID
GROUP BY GPS_TBLDESIGN.QUEST_NO, GPS_TBLDESIGN.DESIGN_ID, GPS_TBLFIELDSET.DESIGN_ID, GPS_TBLDESIGN.SURVEY_ID, GPS_TBLPAGE.PAGEID
HAVING (((GPS_TBLDESIGN.SURVEY_ID)=946) AND ((GPS_TBLPAGE.PAGEID)=1));

 

[SantaMufasa]

Guest,

I'll bet we can help. First, before we get into the Oracle aspects, let's clarify a few fundamental items about your ACCESS query:

a) You are requesting three columns of output (QUEST_NO and two matching DESIGN_IDs from different tables) yet your are GROUPing on those three columns, plus two other non-displaying columns, SURVEY_ID and PAGEID. As these latter two columns change in value, it will produce distinct rows, yet the reader will not be able to see why the rows are distinct since you are not displaying those two values that may be responsible for the output rows to be distinct. Is this really what you want?

b) Usually, when we use GROUP BY, it is because we have some aggregate function (e.g., SUM, COUNT, MIN, MAX, AVG, et cetera) displaying, as well. Is this what you want?

c) Usually, when we use a HAVING clause, it is to filter aggregate results (e.g., "HAVING COUNT(*) > 1", "HAVING SUM(<expression>) > 1000", et cetera). Although you can use HAVING to filter non-aggregate table-row values, it is preferable to use a WHERE clause to filter out unwanted table-row values such as "WHERE GPS_TBLDESIGN.SURVEY_ID=946 AND GPS_TBLPAGE.PAGEID=1". Additionally, the parentheses in your original example are unnecessary under your circumstances (even in Access, I believe).

Now, to specifics about your original syntactical questions: although Oracle has implemented syntactical support for "INNER JOIN" and "LEFT OUTER JOIN" syntax in more recent versions, I believe it would be helpful to see/use the "traditional" Oracle equivalencies of the code you posted. I believe it may even simplify the code.

To provide such an "equivalency", could you please just review for us (in non-code, non-technical terms) a "functional narrative" of what joining you want to take place amongst your tables?

Looking forward to offering some meaningful help.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

How does this version of your original code seem to you, Quest:

SELECT GPS_TBLDESIGN.QUEST_NO
      ,GPS_TBLDESIGN.DESIGN_ID
      ,GPS_TBLFIELDSET.DESIGN_ID
  FROM GPS_TBLANSWER a
      ,GPS_TBLPAGE p
      ,GPS_TBLDESIGN d
      ,GPS_TBLFIELDSET fs
      ,GPS_TBLLEGEND l
      ,GPS_TBLMATRIX_XREF mx
 WHERE d.SURVEY_ID=946
   AND p.PAGEID=1
   AND d.design_id=fs.design_id
   AND l.legend_id=fs.legend_id
   AND p.legendID=l.legend_id
   AND a.quest_no=d.quest_no
   AND d.design_id=mx.design_id
 GROUP BY d.QUEST_NO
         ,d.DESIGN_ID
         ,fs.DESIGN_ID
         ,d.SURVEY_ID
         ,p.PAGEID;

Since I do not have tables/rows that approximate your data, I cannot test it. But you can look it over and tell us your reactions. If it does not produce the results you want, let us know what you want different.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[dpgirl]

This is not related to your question, but I noticed that you were able to include a Code box in your posting. Out of curiosity, how do you do that?

 

[SantaMufasa]

Good question, DPGirl...to create a CODE box around any text, you use square brackets "[ ]" around the word, "code" at the beginning of your code block, then end the block with square brackets around "/code".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[BJCooperIT]

He used

. Click on the "Process TGML" link at the bottom of the posting window. It shows you many ways to add variety to your posts.
[sup][i][b][COLOR=navy]Barbara[/color][/b][/i][/sup]

[COLOR=teal][sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][/color][COLOR=purple][sup] ~George Bernard Shaw[/sup][/color]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: [url=http://www.emuproductsplus.com]Emu Products Plus[/url]