Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I re-wrote your query as:</p> <pre><code>SELECT TO_CHAR(SYSDATE,'DD MONTH,YYYY'), a.appl_no, a.assigned_to, b.co_name, b.co_name2, a.credit_acct_no, a.credit_bank_no, a.credit_branch_no, a.service_id FROM newappl a JOIN newappl_hq b ON b.appl_no = a.appl_no WHERE a.appl_no = ? AND rownum = 1 AND credit_status = 'CRPEND' UNION ALL SELECT TO_CHAR(SYSDATE,'DD MONTH,YYYY'), a.appl_no, a.assigned_to, c.trading_name, ' ', d.bank_acct_no, d.bank_no, d.bank_branch_no, a.service_id FROM newappl a JOIN newappl_ret c ON c.appl_no = a.appl_no JOIN newappl_ret_bank d ON d.ret_id = c.ret_id WHERE c.appl_no = ? AND rownum = 1 AND credit_status = 'CRPEND' </code></pre> <p>From what I can see, the <a href="http://www.techonthenet.com/oracle/errors/ora00918.php" rel="nofollow noreferrer">ORA-00918</a> is about the reference to the <code>credit_status</code> column. Of the tables involved, is there a <code>credit_status</code> column in more than one of them? Because it's the only un-aliased column in either query.</p> <p>Couple other things to mention:</p> <ul> <li>don't need to TRUNC a date if you're going to TO_CHAR it for just the day/month/year info.</li> <li>don't need to alias columns in the latter part of UNION'd statements, UNIONs only need the same number of columns in the SELECT clause and that their data types match</li> <li>don't subquery for what you don't need to</li> <li>always test the query in PLSQL Developer/etc before dumping it into a Prepared Statement. This looks like it could be a stored procedure with a single parameter (assuming the appl_no is identical for both sides)</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. PO PreparedStatement error
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USOMG Ponies
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 - This is the superior answer.
      singulars
      1. PO
      1. USduffymo
 

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