Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: what is wrong in query

Re: what is wrong in query

From: Jan Pruner <jan_at_pruner.cz>
Date: Thu, 06 Sep 2001 10:16:21 -0700
Message-ID: <F001.00385887.20010906101603@fatcity.com>

Oh my god, man!?!
Did you write this chaos yourself??

Try this:
Oracle's JDBC drivers do not support outer join syntax: {oj outer-join}. The workaround is to use Oracle outer join syntax:

Instead of:
Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery

     ("SELECT ename, dname 
       FROM {OJ dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno} 
       ORDER BY ename");

 

Use Oracle SQL syntax:
Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery

     ("SELECT ename, dname 
       FROM emp a, dept b WHERE a.deptno = b.deptno(+)
       ORDER BY ename");


------------------------------------

Jan Pruner

Dne ?t 6. z? 2001 19:46 jste napsal(a):
> Hi,
>
> I am trying to run following query thru ODBC and getting error:
>
> 09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
> Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
> relational operator
> Which relational operator it is referring?????.....
>
> select DISTINCT(t_po.id_po), t_po.id_eff_date,
> t_po.id_avail, t_po.b_user_subscribe, t_po.b_user_unsubscribe,
> t_base_props.n_name,
> t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
> t_base_props.nm_desc,
> t_base_props.nm_display_name,te.n_begintype as
> te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset
> as te_n_beginoffset,te.n_endtype as
> te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
> ta.n_begintype as ta_n_begintype,
> ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
> ta.n_endtype as ta_n_endtype, ta.dt_end as
> ta_dt_end, ta.n_endoffset as ta_n_endoffset,
> decode(sign((select count(id_pi_type) from
> t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND
> tb.n_kind = 20 and t_po.id_po =
> t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
> ,t_ep_po.c_ExternalInformationURL
> t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
> t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
> t_ep__c_InternalInformationURL
> from t_av_internal tav,t_effectivedate
> te,t_effectivedate ta,t_base_props,t_pricelist,
> t_base_props
> template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
> dual) cdate,
> t_pl_map,t_recur,t_discount,t_aggregate
> where
> {oj t_po LEFT OUTER JOIN t_ep_po on
> t_ep_po.id_prop = t_po.id_po},
> t_recur.id_prop = t_pl_map.id_pi_template(+)
> and
> t_discount.id_prop = t_pl_map.id_pi_template
> and
> t_aggregate.id_prop =
> t_pl_map.id_pi_template and
> t_pl_map.id_po = t_po.id_po AND
> t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
> tav.id_acc = 136 AND
> t_pricelist.id_pricelist =
> t_pl_map.id_pricelist AND tav.c_currency = t_pricelist.nm_currency_code
> AND
> te.id_eff_date = t_po.id_eff_date AND
> ta.id_eff_date = t_po.id_avail AND
> t_base_props.id_prop = t_po.id_po AND
> template_base.id_prop =
> t_pl_map.id_pi_template AND
> t_po.id_po not in
> (select id_po from t_sub,t_effectivedate
> tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
> (tesub.dt_end is NULL AND tesub.dt_start <=
> cdate.now))
> AND
> ((ta.dt_start <= cdate.now or ta.dt_start is
> null) AND (cdate.now <= ta.dt_end or ta.dt_end is null)) AND
> t_acc_usage_cycle.id_acc = 136 AND
> t_usage_cycle.id_usage_cycle =
> t_acc_usage_cycle.id_usage_cycle AND
> (t_recur.id_cycle_type is null or
> t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> (t_discount.id_cycle_type is null or
> t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> (t_aggregate.id_cycle_type is null or
> t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> te.n_begintype <> 0 AND ta.n_begintype <> 0
>
> Thanks
> -Harvinder

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jan Pruner
  INET: jan_at_pruner.cz

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Sep 06 2001 - 12:16:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US