silly oracle query error( a headache)
-
below is my query, i have a policy table, previously every policy records should have intermediary field, in it, but now it has changed now it is optional
so every policy may not have intermediary, so iam doing a outer join but this stupid oracle is throwing an error, it says
"ORA-01417: a table may be outer joined to at most one other table"
what the hell is this. how should i solve this problem
below is my query
SELECT DISTINCT SYSPOLICY_NO,SYS.Toolkit.Decrypt(POLICY.POLICY_NO) policy_no,
SYS.Toolkit.Decrypt(QUOTATION.QUOTATION_NO) quotation_no,POLICY.SYSQUOTATION_NO,
SYS.Toolkit.Decrypt(PRODUCT.PROD_NAME) prod_name,POLICY.SUM_INSURED,POLICY.PREMIUM,
TO_CHAR(POLICY.INCEPTION_DT,'DD, MON YYYY') INCEPTION_DT,TO_CHAR(POLICY.EXPIRY_DT,'DD, MON YYYY') EXPIRY_DT,
POLICY_STATUS, SYS.Toolkit.Decrypt(A.CODE_VALUE) AS TYPE,
SYS.Toolkit.Decrypt(B.CODE_VALUE) AS OWNERSHIP,
SYS.Toolkit.Decrypt(I.COMPANY_NAME) company_name,
C.COVERAGE_PCT,POLICY.FLOATER_TYPE floater_type
FROM POLICY,PRODUCT,INTERMEDIARY,CODE_VALUE A,CODE_VALUE B,COB,QUOTATION, COINSURANCE_DETAILS C,risk_details risk,
INS_COMPANY I
Where POLICY.product_ID = PRODUCT.product_ID And
POLICY.INTERMEDIARY_ID = INTERMEDIARY.INTERMEDIARY_ID
AND INTERMEDIARY.INTERMEDIARY_TYPE(+)=A.CODE_VALUE_ID AND
INTERMEDIARY.OWNERSHIP(+)=B.CODE_VALUE_ID
AND PRODUCT.COB_ID=COB.COB_ID AND
QUOTATION.SYSQUOTE_NO = POLICY.SYSQUOTATION_NO AND POLICY.POLICY_STATUS IN ('ACT','CAN','REN','EXP') AND
C.REF_NO = POLICY.SYSPOLICY_NO AND
C.TYPE='P' AND I.COMPANY_ID = C.INS_COMPANY_ID
AND risk.ref_no=policy.syspolicy_no
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
You might be better off posting this to an Oracle site like
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
i have done that and waiting for answers, but there message board is not very proper, after posting u have to search for question where exactly its been posted, its not the latest questions are shown first , they are just shown without any proper order
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Shame about the problems you are having with the other site, but this is a Microsoft SQL Server site (even though there is a part of this site for MS Access and Oracle).
If you really want to post on this site, you should post it in the ORACLE forum. Transact SQL is used by MS SQL Server and not by ORACLE. So most advice you will get here won't work because it will be the wrong version of SQL.
-SQLBill
-
I'll cross the culture lines on this one (see my sig below, SPECIALLY REPRODUCED for this post. Not that it's something I'm proud of, ... well ... at least not here!
)If you are Oracle9i or later, use the ANSI OUTER JOIN sytax instead of the syntax with the (+) "tie fighter" markers. That syntax, which Oracle came up with before ANSI standardized the OUTER JOIN sytax, is not as precise as the ANSI standard, and so sometimes the optimizer cannot tell with absolute certainty what was intended.
For example, instead of SELECT * FROM A, B WHERE A.X = B.X(+) you should write SELECT * FROM A LEFT JOIN B ON A.X = B.X. Usually, the syntax doesn't make a difference, but you can run into situations where the tie-fighter syntax is not precise enough. This is due to the fact that the tie-fighter syntax mixes the "join rule" and the WHERE clause in a single clause, so there is some ambiguity about what to apply when, where, and how. I believe this ambiguity can only surface with queries that involve multiple joins, such as yours.
By the way, T-SQL's *= and =* operators suffer from the same imprecision. This imprecision (which is related to the co-mangling of the join rule and the WHERE clause) is one of the most compelling reasons to use the ANSI JOIN sytax, even if it is verbose.
I didn't actually review your join to see if this might apply, but after all, this is a SQL Server forum! Hope none of the SQL studs / studettes mind me fielding an Oracle question here.
Cheers,
Chris
___________________________________Brainbench MVP for Oracle Admin <<=== don't get to post *that* very often here!
MCSE, MCDBA, OCP, CIW___________________________________
-
i have solved that problem, i had made views of grouping two tables, and gettign the results, and then using them in the final query. iam using oracle 8i
"ADMIT U PPL DONT KNOW ORACLE "
like for example iam using intermediary with codevalue a,codevalue b, iam making a query using this three table, making a view of that query, and then using it in the final query, by this way iam minimizing the use of left joins in the final query.
but iam still not getting why the hell oracle was giving such a stupid error,
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Wake up on the wrong side of the bed, Sukhoi?
I do know Oracle, and I know it extremely well - sorry. I also took a closer look at your query, and what I said is correct - the problem is because of the imprecision of the (+) operator, which I call the "tie fighter" just because it looks like those space ships from Star Wars. In short, it's your query that's the problem. You can't tell Oracle, or SQL Server, or any other DBMS that has a decent optimizer, to do certain combinations of outer joins. The reason is that upon inspection, they just don't make sense. Your original formulation is one that does not make sense - or, put a different way, it is ambiguous.
The others freely admitted that they were not going to try to help you, so I assume your slapshot comment about admitting we don't know Oracle was addressed to me, not them. I have worked with Oracle for over 10 years, have been the Senior Oracle DBA at a Fortune 100 company, and currently work as both an Oracle and SQL Server DBA at an institution that has about 50 Oracle instances running under our care. I was one of 30 people worldwide that were recruited by Brainbench as an Oracle MVP, and in that capacity I both represent Brainbench publicly and give them advice on developing their Oracle exams. Even in that group of 30 people I have been a leader at times in debugging the more difficult issues. It strikes me as a little cheeky that you want me to admit that I don't know Oracle - why would you want me to lie?
If you want to get angry at us, fine - that's not our problem.
In case you still don't believe this problem is with your query, here's the quote from the docs:
In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C.
Oracle8i SQL Reference, page 5-26
As I mentioned before, this is the problem with your SQL: it is asking for an ambiguous outer join. It is not a particularly advanced kind of problem, and it is not specific to Oracle (though Oracle's response to it may be different from that of other vendors). For what it's worth, my previous suggestion to use ANSI joins (which you can't do in 8i anyway) would not help, because the ANSi syntax only helps by separating the join rules from the true WHERE clause by putting join rules into a different clause (the ON clause). It can't help in the case of nonsensical join rules, which is the problem in your original query.
As for your non-SQL related problems ... we can't help you there, pal. You're mad at Oracle for refusing to run a nonsense query, and you're mad at us for trying to help, each of us in different ways. But at least I think I can understand why you get these "headaches."
Having said that ... have a nice day.
Chris
-
Chris,
If it means anything, I thought your answer was lucid and to the point; sukhoi must be crabby because he's writing crappy code.
Sans the Oracle specific functions, this query compiles just file on MS SQL.
SELECT DISTINCT
SYSPOLICY_NO,
SYS.Toolkit.Decrypt(POLICY.POLICY_NO) policy_no,
SYS.Toolkit.Decrypt(QUOTATION.QUOTATION_NO) quotation_no,
POLICY.SYSQUOTATION_NO,
SYS.Toolkit.Decrypt(PRODUCT.PROD_NAME) prod_name,
POLICY.SUM_INSURED,
POLICY.PREMIUM,
TO_CHAR(POLICY.INCEPTION_DT,'DD, MON YYYY') INCEPTION_DT,
TO_CHAR(POLICY.EXPIRY_DT,'DD,MON YYYY') EXPIRY_DT,
POLICY_STATUS,
SYS.Toolkit.Decrypt(A.CODE_VALUE) AS TYPE,
SYS.Toolkit.Decrypt(B.CODE_VALUE) AS OWNERSHIP,
SYS.Toolkit.Decrypt(I.COMPANY_NAME) company_name,
C.COVERAGE_PCT,
POLICY.FLOATER_TYPE floater_type
FROM POLICY
JOIN PRODUCT on POLICY.product_ID = PRODUCT.product_ID
JOIN COB on PRODUCT.COB_ID = COB.COB_ID
JOIN QUOTATION on QUOTATION.SYSQUOTE_NO = POLICY.SYSQUOTATION_NO
JOIN COINSURANCE_DETAILS C on C.REF_NO = POLICY.SYSPOLICY_NO
JOIN INS_COMPANY I on I.COMPANY_ID = C.INS_COMPANY_ID
JOIN risk_details risk on risk.ref_no = policy.syspolicy_no
JOIN INTERMEDIARY on POLICY.INTERMEDIARY_ID = INTERMEDIARY.INTERMEDIARY_ID
LEFT JOIN CODE_VALUE A on INTERMEDIARY.INTERMEDIARY_TYPE = A.CODE_VALUE_ID
LEFT JOIN CODE_VALUE B on INTERMEDIARY.OWNERSHIP = B.CODE_VALUE_ID
Where
POLICY.POLICY_STATUS IN ('ACT','CAN','REN','EXP')
AND C.TYPE = 'P'
Signature is NULL
-
Well ... thanks! I don't usually lose my patience with people, and I feel stupid for tooting my own horn, but I was a little frustrated with Sukhoi. Thanks for helping me calm down!
By the way, the following Oracle snippet ...
SELECT ... FROM ...,INTERMEDIARY,CODE_VALUE A,CODE_VALUE B,... Where ... AND INTERMEDIARY.INTERMEDIARY_TYPE(+)=A.CODE_VALUE_ID AND INTERMEDIARY.OWNERSHIP(+)=B.CODE_VALUE_ID AND ...
... would translate to "old" T-SQL as ...
SELECT ... FROM ...,INTERMEDIARY,CODE_VALUE A,CODE_VALUE B,... Where ... AND INTERMEDIARY.INTERMEDIARY_TYPE =* A.CODE_VALUE_ID AND INTERMEDIARY.OWNERSHIP =* B.CODE_VALUE_ID AND ...
... which translates to ANSI JOIN SQL as something like ...
SELECT ... FROM ...,INTERMEDIARY RIGHT JOIN CODE_VALUE A ON INTERMEDIARY.INTERMEDIARY_TYPE = A.CODE_VALUE_ID RIGHT JOIN CODE_VALUE B ON INTERMEDIARY.OWNERSHIP = B.CODE_VALUE_ID...
... which is to say, I think the LEFTs and RIGHTs are switched in your translation. The tie fighter looks like a plus sign because Oracle will add "null records" as needed to *that* side of the equal sign. So when the tie fighter is on the RIGHT side of the equal sign, it's a LEFT join, and visa-versa.
One problem with these formulations (as I've always understood them) is that it's not clear, when "null records" need to be invented for both tables A and B, whether there should be 1 or 2 "null records." Does that make sense? I think I said that right, but I'm in a hurry to leave the office (just stopped in to fix a firewall problem, and I have two kiddos with me who need to get to bed!).
I reply partly because I'm curious as to whether or not your T-SQL query will still compile if you switch the left joins to right joins. I am expecting that they won't (and that's what my last post was about, ranting aside). The Oracle docs say that you can't generate "null records" for a given rowsource to match up with more than one other table in a query (which makes sense). I know I've run into this problem in MS SQL also ... but if you can provide me with a counter-example by saying your query will work with the LEFTs switched to RIGHTs, I'll have learned something new! So if you still have the rest of the script (I'm thinking table DDL here, in particular) ... would you mind giving it a shot? Otherwise I can try it tonight or tomorrow.
Thanks again,
Chris
-
Chris,
Sukhoi is out of place with the comments made. You did a good job on answering a person who isn't even willing to post in the proper forum.
This site is dedicated to Microsoft SQL Server and this forum is specifically for Transact-SQL. So for Sukhoi to get upset, when he/she is posting ORACLE problems on a MS SQL Server site, is just unbelievable. This site even has an ORACLE forum that Sukhoi could have posted on.
Oh well, you can't make everyone happy.
-SQLBill
-
Thanks Bill - I appreciate the moral support, especially from a 10-Century man such as yourself (wowza! there can't be that many of you, I bet).
I almost didn't post my reply to Sukhoi because I knew I was letting my frustration speak, but every now and then you get the impressions that frustration is justifiable. I don't think that the demands of a PC culture mean that you can't let somebody know once in a while if you think they're clearly out of line. Still, I probably should've just walked away from the thread - just because I've got some vague impression that my frustration seems justified doesn't mean it's OK for me to vent in public.
Sorry about that, everybody.Like you said, you can't make everybody happy every time.
-Chris
-
Well I'm not sure now..I think maybe they should have been rights.
Right join means keep all records from the "right" table whether or not they match a record in the "left" table. Left join means the exact opposite.
Signature is NULL
-
Correct. Which is why the following in Oracle is a LEFT join even though the (+) is on the RIGHT.
SELECT ... FROM A, B WHERE A.X = B.Y (+)
You should read this as "I want to see records from the data source on the LEFT side of the equal sign so badly that it's OK to ADD bogus data to the data source listed on the right. I think I said it correctly in the previous post, but Sukhoi's query had RIGHT joins in in where you used LEFTs.
Looking back at your post, you said that your query compiled against MS SQL, but it has a couple of Oracle-specific constructs in it (the TO_CHAR function and SYS.Toolkit.Decrypt). Did you actually run it against Oracle?
Anyway, I constructed a test script, and found that the ANSI JOIN sytax could handle the query as either a LEFT or RIGHT join, which surprised me, but it's good news. So I've learned something (else) today.
But I can reproduce Sukhoi's original problem in SQL Server. Sukhoi was not able to use ANSI join sytax because s/he was using Oracle8i - Oracle did not support ANSI join syntax until Oracle9i. Therefore, this problem would be similar to the use of the old-style =* and *= operators in Transact-SQL. The following script is long, but worth a quick look. Here's what you're seeing, in a nutshell: first, tables are created. Next, the query from your post (Calvin) is translated into T-SQL. This query, as predicted, runs fine, producing an empty recordset. However, the third query switches from *= operators to =* operators. This switches the query from using LEFT joins to RIGHT joins. This query, which matches the join pattern of Sukhoi's original query, fails with SQL Server error message 303.
Like I said, it's a long script, at least for such a small text box (!), but here goes:
Batch 1: create table policy(policy_no int primary key, sysquotation_no int, product_id int, sum_insured int, premium int, inception_dt datetime, expiry_dt datetime, policy_status char(1), floater_type int, syspolicy_no int, intermediary_id int) create table product (product_id int primary key, cob_id int, prod_name varchar(50)) create table cob (cob_id int primary key) create table quotation (quotation_no int primary key, sysquote_no int) create table coinsurance_details (ref_no int primary key, coverage_pct int, ins_company_id int, type char(1)) create table ins_company (company_id int primary key, company_name varchar(50)) create table risk_details (ref_no int primary key) create table intermediary (intermediary_id int primary key, intermediary_type int, ownership int) create table code_value (code_value_id int primary key, code_value varchar(50)) GO
Batch 1 Output: The command(s) completed successfully.
Batch 2: SELECT DISTINCT SYSPOLICY_NO, POLICY_NO policy_no, QUOTATION.QUOTATION_NO quotation_no, POLICY.SYSQUOTATION_NO, PRODUCT.PROD_NAME prod_name, POLICY.SUM_INSURED, POLICY.PREMIUM, INCEPTION_DT, EXPIRY_DT, POLICY_STATUS, A.CODE_VALUE AS TYPE, B.CODE_VALUE AS OWNERSHIP, I.COMPANY_NAME AS company_name, C.COVERAGE_PCT, POLICY.FLOATER_TYPE floater_type FROM POLICY, PRODUCT, COB, QUOTATION, COINSURANCE_DETAILS C, INS_COMPANY I, RISK_DETAILS RISK, INTERMEDIARY, CODE_VALUE A, CODE_VALUE B WHERE POLICY.product_ID = PRODUCT.product_ID AND PRODUCT.COB_ID = COB.COB_ID AND QUOTATION.SYSQUOTE_NO = POLICY.SYSQUOTATION_NO AND C.REF_NO = POLICY.SYSPOLICY_NO AND I.COMPANY_ID = C.INS_COMPANY_ID AND risk.ref_no = policy.syspolicy_no AND POLICY.INTERMEDIARY_ID = INTERMEDIARY.INTERMEDIARY_ID AND INTERMEDIARY.INTERMEDIARY_TYPE *= A.CODE_VALUE_ID AND INTERMEDIARY.OWNERSHIP *= B.CODE_VALUE_ID AND POLICY.POLICY_STATUS IN ('ACT','CAN','REN','EXP') AND C.TYPE = 'P' GOBatch 2 Output: SYSPOLICY_NO policy_no etc. ... ------------ ----------- ------------
(0 row(s) affected)
Batch 3: SELECT DISTINCT SYSPOLICY_NO, POLICY_NO policy_no, QUOTATION.QUOTATION_NO quotation_no, POLICY.SYSQUOTATION_NO, PRODUCT.PROD_NAME prod_name, POLICY.SUM_INSURED, POLICY.PREMIUM, INCEPTION_DT, EXPIRY_DT, POLICY_STATUS, A.CODE_VALUE AS TYPE, B.CODE_VALUE AS OWNERSHIP, I.COMPANY_NAME AS company_name, C.COVERAGE_PCT, POLICY.FLOATER_TYPE floater_type FROM POLICY, PRODUCT, COB, QUOTATION, COINSURANCE_DETAILS C, INS_COMPANY I, RISK_DETAILS RISK, INTERMEDIARY, CODE_VALUE A, CODE_VALUE B WHERE POLICY.product_ID = PRODUCT.product_ID AND PRODUCT.COB_ID = COB.COB_ID AND QUOTATION.SYSQUOTE_NO = POLICY.SYSQUOTATION_NO AND C.REF_NO = POLICY.SYSPOLICY_NO AND I.COMPANY_ID = C.INS_COMPANY_ID AND risk.ref_no = policy.syspolicy_no AND POLICY.INTERMEDIARY_ID = INTERMEDIARY.INTERMEDIARY_ID AND INTERMEDIARY.INTERMEDIARY_TYPE =* A.CODE_VALUE_ID AND INTERMEDIARY.OWNERSHIP =* B.CODE_VALUE_ID AND POLICY.POLICY_STATUS IN ('ACT','CAN','REN','EXP') AND C.TYPE = 'P'Batch 3 Output: Server: Msg 303, Level 16, State 1, Line 1 The table 'INTERMEDIARY' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.
So, to sum up: it looks like the ANSI join sytax can make sense out of a query like this, for reasons I need to digest later. But without the ANSI sytax, Sukhoi's original query can't be run on SQL Server ... and as Sukhoi's error message attests, it couldn't be run on Oracle8i either. As stated before, I believe (read: "think," not "know for sure") that this is due to the inherent imprecision of the (+) (or *= / =*) sytax, which necessarily comingles join rules and where clause predicates in a single clause. What I need to figure out now is why this is not ambiguous in the ANSI sytax. But ... it might not make my "worth the effort" list. I know that the ANSI syntax cleans up ambiguities in lots of relatively rarely-encountered situations, so that's why my first post told Sukhoi to try the ANSI sytax if he could!
I just might leave it at that, and call it a dead thread (unless somebody replies again, I guess).Cheers,
Chris
-
Yeah, I "ran" it (IE: verified it would compile) "Sans the Oracle specific functions" as I said in my post.
Very interesting on the joins...makes sense to me. Seems like a rather criptic command, though; I much prefer left and right. The old syntax gives me a headache when reading it...
Anyway, great job, Chris, much more than was deserved in this case.
cl
Signature is NULL
-
> much more than was deserved in this case.
I don't know about that ... I thought your original reply certainly deserved to be followed up!
Take care,
Chris
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply