question

tcalbert93 avatar image
tcalbert93 asked

Error question

Please provide your SQL statement and the result set that is a list of relationship managers (ID and full name) who are not assigned to a vendor account. Note that the list should be sorted alphabetically by last name in descending order. [link text][1] SELECT r.rm_id "Relationship Manager ID", r.rm_fname "RM First Name", r.rm_lname " RM Last Name", v.vendor_num "Vendor ID" FROM talbert3_vendor v LEFT OUTER JOIN talbert3_relationship_manager r ON r.rm_id (+) = v.rm_id ORDER BY (r.rm_lname); I am trying to answer the question above, but am receiving this error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'V' [1]: /storage/temp/3077-sid-3-logical-model.docx
oracle
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SELECT r.rm_id, r.rm_fname, r.rm_lname, v.v_num FROM talbert3_relationship_manager r, talbert3_vendor v WHERE r.rm_id (+)= v.rm_id ORDER BY (r.rm_lname); with this statement I do not receive an error, but it does not return me the rm managers that are not assigned to a vendor
0 Likes 0 ·

1 Answer

·
KenJ avatar image
KenJ answered
You have conflated the legacy Oracle JOIN syntax with the ANSI JOIN syntax. When you perform an implicit join using the oracle syntax, the (+) operator is used to denote the optional table in the join. If (+) is used after the left table, then it is an implicit right join. If (+) is used after the right table, it is an implicit left join. Because (+) is valid syntax in this context, your second query compiles and runs. It is missing some of the managers because you made r the optional table rather than v. When you use the ANSI join syntax, you cannot use the (+) operator; the optional table is defined by the syntax. The 'right' table is optional in a left join and the 'left' table is optional in a right join. Your first query fails to compile because you attempt to use the oracle (+) operator within the context of an ANSI join. The query should run fine after you remove it. Of course, you have performed the left outer join on the vendors table, so you will still be missing some of the managers. Similar to the second query you posted, you'll need to use a right outer join or reverse the order in which the tables are referenced. As discussed in [this question][1], the ansi join is the preferred method for performing joins in Oracle. [1]: https://ask.sqlservercentral.com/questions/135371/managers-who-are-not-assigned-to-a-supplier.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.