sql - Ambiguity in Left joins (oracle only?) -

-

my boss found bug in query created, , don't understand reasoning behind bug, although query results prove he's correct. here's query (simplified version) before fix:

select ptno,ptnm,catcd parts  left join categories on (categories.catcd=parts.catcd);

and here after fix:

select ptno,ptnm,parts.catcd parts  left join categories on (categories.catcd=parts.catcd);

the bug was, null values being shown column catcd, i.e. query results included results table categories instead of parts. here's don't understand: if there ambiguity in original query, why didn't oracle throw error? far understood, in case of left joins, "main" table in query (parts) has precedence in ambiguity. wrong, or not thinking problem correctly?

update:

here's revised example, ambiguity error not thrown:

create table parts (ptno number, catcd number, seccd number);  create table categories(catcd number);  create table sections(seccd number, catcd number);   select ptno,catcd  parts  left join categories on (categories.catcd=parts.catcd)  left join sections on (sections.seccd=parts.seccd) ;

anybody have clue?

i'm afraid can't tell why you're not getting exception, can postulate why chose categories' version of column on parts' version.

as far understood, in case of left joins, "main" table in query (parts) has precedence in ambiguity

it's not clear whether "main" mean left table in left join, or "driving" table, see query conceptually... in either case, see "main" table in query you've written not "main" table in actual execution of query.

my guess oracle using column first table hits in executing query. , since individual operations in sql not require 1 table hit before other, dbms decide @ parse time efficient 1 scan first. try getting execution plan query. suspect may reveal it's hitting categories first , parts.


Comments

Post a Comment

Popular posts from this blog

c++ - How do I get a multi line tooltip in MFC -

-
right now, have tool tip pops when hover on edit box. problem tool tip contains multiple error messages , in 1 long line. need have each error message on own line. error messages contained in cstring new line seperating them. my existing code below. bool ontooltiptext(uint, nmhdr* pnmhdr, lresult* presult) { assert(pnmhdr->code == ttn_needtexta || pnmhdr->code == ttn_needtextw); // need handle both ansi , unicode versions of message tooltiptexta* pttta = (tooltiptexta*)pnmhdr; tooltiptextw* ptttw = (tooltiptextw*)pnmhdr; // tchar szfulltext[256]; cstring strtiptext=_t(""); uint nid = pnmhdr->idfrom; if (pnmhdr->code == ttn_needtexta && (pttta->uflags & ttf_idishwnd) || pnmhdr->code == ttn_needtextw && (ptttw->uflags & ttf_idishwnd)) { // idfrom hwnd of tool nid = ::getdlgctrlid((hwnd)nid); } //m_errprojaccel[ch] contains 1 or more error messages each...
Read more

asp.net - In javascript how to find the height and width -

-
i having .aspx , masterpage page. in masterpage loading aspx page . in aspx page design using generating table structure using code-behind server in javascript how find height , width . regards, kumar if you're looking table height , width, should it var tbl = document.getelementbyid('tbldemo'); alert(tbl.offsetwidth + '\n' + tbl.offsetheight);
Read more

c# - DataTable to EnumerableRowCollection -

-
i have datatable according client requirement can contain invalid date "00/00/1999",null,empty string.(typed datset) when collect them enumerables wish convert of invalid forms empty string. (i.e) enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || (how check date invalid date) ? string.empty : order.field<datetime>("orderdate") } how check whether date valid date or not? datetime.tryparse...like given current code: datetime date; enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || !datetime.tryparse(convert.tostring(order.field<datetime...
Read more