Databases
StackOverflow2013
Postsdbo
POSQLDeveloper output changes when selecting specific columns
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLDeveloper output changes when selecting specific columns
    primarykey
    data
    text
    <p>I am using Oracle SQL Developer and I am trying to run a query like this</p> <pre><code>SELECT * FROM (SELECT id1 CONTRACT_ID1, id2 CONTRACT_ID2 FROM contract_table) LEFT JOIN (SELECT id1 A_CONTRACT_ID, id2 A_SPECIAL_CONTRACT_ID from special_contract_table_a) on (CONTRACT_ID1 = A_CONTRACT_ID) LEFT JOIN (SELECT id1 B1_CONTRACT_ID, id2 B1_DOCUMENT_ID from document_table_b1) ON (CONTRACT_ID2 = B1_CONTRACT_ID) LEFT JOIN (SELECT id1 B2_CONTRACT_ID, id2 B2_DOCUMENT_ID from document_table_b2) ON (A_SPECIAL_CONTRACT_ID = B2_CONTRACT_ID) LEFT JOIN (SELECT id1 B3_CONTRACT_ID, id2 B3_DOCUMENT_ID from document_table_b3) ON (A_SPECIAL_CONTRACT_ID = B3_CONTRACT_ID) LEFT JOIN (SELECT page PAGE, id1 C1_DOCUMENT_ID, id2 C2_DOCUMENT_ID, id3 C3_DOCUMENT_ID from page_table_c) ON (C1_DOCUMENT_ID = B1_DOCUMENT_ID OR C2_DOCUMENT_ID = B2_DOCUMENT_ID OR C3_DOCUMENT_ID = B3_DOCUMENT_ID) LEFT JOIN (SELECT id2 B1_DOCUMENT_ID_2 from, status B1_DOCUMENT_STATUS from document_table_b1) ON (C1_DOCUMENT_ID = B1_DOCUMENT_ID_2) LEFT JOIN (SELECT id2 B2_DOCUMENT_ID_2 from, status B2_DOCUMENT_STATUS from document_table_b2) ON (C2_DOCUMENT_ID = B2_DOCUMENT_ID_2) LEFT JOIN (SELECT id2 B3_DOCUMENT_ID_2 from, status B3_DOCUMENT_STATUS from document_table_b3) ON (C3_DOCUMENT_ID = B3_DOCUMENT_ID_2) ; </code></pre> <p>So basically what this does is, that I select my two base IDs, then get all members of all associated groups and then fetch the status of each of those members (and the status is stored within the group declaration). This works as I expect it as long as I use <code>SELECT * FROM</code>. If I change this to <code>SELECT CONTRACT_ID1, PAGE, B1_DOCUMENT_STATUS, B2_DOCUMENT_STATUS, B3_DOCUMENT_STATUS</code> the output collapses from 24 lines to 4, showing only the matches for <code>document_table_b1</code>. </p> <p>Any idea how to fix this?</p> <p>Additional information: DB-Version is Oracle 10g. Oracle SQL Developer Version is 3.2.09. A comparision between Autotrace and Explainplan shows differences in some of the Access Predicates from <code>ID_1(+) = ID_2</code> (Explainplan) to <code>ID_1 = ID_2</code> (Autotrace) with both select statements. I highly suspect the query optimizer screwing up here. I have not found a way to tinker with its settings yet. Any input in that direction might be helpful. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLucas Hoepner
    1. USLucas Hoepner
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQLDeveloper output changes when selecting specific columns
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQLDeveloper output changes when selecting specific columns
      1. USDaniel Hilgarth
      1. VTFavorite
    3. VO
      singulars
      1. POSQLDeveloper output changes when selecting specific columns
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you try it with explicit aliases for your sub selects?
      singulars
      1. POSQLDeveloper output changes when selecting specific columns
      1. USDaniel Hilgarth
    2. CO@DanielHilgarth done! Better now?
      singulars
      1. POSQLDeveloper output changes when selecting specific columns
      1. USLucas Hoepner
    3. COEh? What did you change? I was asking you to check if you still get the error when you give your sub queries an explicit alias... I didn't ask you to edit your question... Your code: (select x from foo) left outer join (select y from bar) on (x = y). What I am asking: (select x from foo) **as a** left outer join (select y from bar) **as b** on (a.x = b.y)
      singulars
      1. POSQLDeveloper output changes when selecting specific columns
      1. USDaniel Hilgarth
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload