New to PL/SQL and pass-through

[iamareplicant]

Experts,

I am writing my first PL/SQL statements in my Access2K3 pass-thru queries. I need to convert exisitng Access queries from native Access to PL/SQL. I have had some success, but mostly frustration.

Here is one query that, if I can get a handle on, would serve as an example to hammer out about 30 other queries that need to be converted.

I am hoping for one of you experts to, not rewrite the query per se, but give me a translation of the Access query to PL/SQL.

The big issue of course is the way that Oracle PL/SQL does joins.

Here is the query I want to use as my example:

SELECT TEST_SKILLTRAC.SKILLTRAC_ID, 
TEST_SKILLTRAC.SKILLTRAC_NAME, 
TEST_SKILLTRAC_USER.USER_ID, 
TEST_SKILLTRAC_STATUS.SKILLTRAC_STATUS_CD

FROM 

(TEST_SKILLTRAC INNER JOIN TEST_SKILLTRAC_USER ON 

TEST_SKILLTRAC.SKILLTRAC_ID = TEST_SKILLTRAC_USER.SKILLTRAC_ID) 

INNER JOIN TEST_SKILLTRAC_STATUS ON 

TEST_SKILLTRAC_USER.SKILLTRAC_STATUS_CD = TEST_SKILLTRAC_STATUS.SKILLTRAC_STATUS_CD

WHERE (((TEST_SKILLTRAC_USER.USER_ID)>0) AND ((1)=1))

I beleive that PL/SQL should handle this like (and the below is very crude - it is the join part of the SQL statement that I am fussing over):

SELECT 
FIELDS
FROM
tbl1,
tbl2,
tbl3,
WHERE
tbl1.field = tb2.field AND
tbl2.field = tbl3.field
AND
tbl1.userid = 2

Any help would be most appreciated.

JBG

 

[JerryKlmns]

There are 4 dedicated forums for Oracle

Oracle 5, 6, 7 forum185
Oracle 8, 8i forum186
Oracle 9i forum759
Oracle 10g forum1177

you better post there. (although some1 in this forum could get you started!)

 

[lespaul]

you are also going to want to read the JOINS document linked below. You are taking INNER JOINS and converting them to CARTESIAN JOINS. Oracle does INNER JOINS just without the parens:

SELECT S.SKILLTRAC_ID,
S.SKILLTRAC_NAME,
U.USER_ID,
SS.SKILLTRAC_STATUS_CD

FROM TEST_SKILLTRAC S
INNER JOIN TEST_SKILLTRAC_USER U ON S.SKILLTRAC_ID = U.SKILLTRAC_ID

INNER JOIN TEST_SKILLTRAC_STATUS SS ON
U.SKILLTRAC_STATUS_CD = SS.SKILLTRAC_STATUS_CD

WHERE U.USER_ID >0 AND 1=1

I'm also not sure what your

WHERE ... AND 1 = 1

statement is for.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[iamareplicant]

Much obliged for the responses. I appreciate it.