| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: query fails when run using microsoft odbc driver
Hi,
I think the problem arise may be due to this "(select GetUTCDate() now
from dual)" as now is the reserve key word used by microsoft as sysdate
in oracle.
Try it. Its just a thought.
Ketan.
Harvinder Singh wrote:
>
> Hi,
>
> We have a query which runs fine from sqlplus but return following error when
> runs from appliction using
> MICROSOFT ODBC Driver:
> ora-00904 INVALID COLUMN NAME ....
>
> Code of query is:
> it seems like specification of table template_po_map is creating
> problem.......
> how to fix it .....
>
> 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
> ,template_po_map.b_RecurringCharge
> ,t_ep_po.c_boris
> t_ep_po_c_boris,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
> (select GetUTCDate() now from dual)
> cdate,
> t_po,t_ep_po,
> t_effectivedate te,
> t_effectivedate ta,
>
> t_base_props,
> (SELECT id_po,
> decode(MAX(YesNo),1,'Y','N') b_RecurringCharge
> FROM
> (SELECT
> t_pl_map.id_po,
>
> decode(tb.n_kind,20,decode(sign(count(*)),1,1,0),0) YesNo
> FROM
> t_av_internal tav,
> t_pricelist,
> t_base_props tb,
> t_pl_map,
> t_recur,
> t_discount,
> t_aggregate
> WHERE
> -- Check currency
> 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
> tav.id_acc = 134 AND
>
> t_pricelist.id_pricelist = t_pl_map.id_pricelist AND
> tav.c_currency =
> t_pricelist.nm_currency_code AND
> -- Check cycle type
>
> (t_recur.id_cycle_type is null or
>
> t_recur.id_cycle_type = (select id_cycle_type
> from
> t_acc_usage_cycle, t_usage_cycle
> where
> t_acc_usage_cycle.id_acc = 134
> AND
> t_usage_cycle.id_usage_cycle = t_acc_usage_cycle.id_usage_cycle)) AND
>
> (t_discount.id_cycle_type is null or
>
> t_discount.id_cycle_type = (select id_cycle_type from t_acc_usage_cycle,
> t_usage_cycle where
> t_acc_usage_cycle.id_acc = 134
> and
> t_usage_cycle.id_usage_cycle = t_acc_usage_cycle.id_usage_cycle)) AND
>
> (t_aggregate.id_cycle_type is null or
>
> t_aggregate.id_cycle_type = (select id_cycle_type from t_acc_usage_cycle,
> t_usage_cycle where
> t_acc_usage_cycle.id_acc = 134
> and
> t_usage_cycle.id_usage_cycle = t_acc_usage_cycle.id_usage_cycle)) AND
> tb.id_prop =
> t_pl_map.id_pi_template AND
> -- Not already have
> id_po not in
> (select distinct
> t_sub.id_po
> from t_sub,
> t_effectivedate tesub
> where t_sub.id_acc =
> 134
> AND
> t_sub.id_eff_date = tesub.id_eff_date
> AND tesub.dt_end
> is NULL
> AND
> tesub.dt_start <= GetUTCDate()
> )
> GROUP BY
> t_pl_map.id_po, tb.n_kind
> ) template_po_map0
> GROUP BY id_po
> )
> template_po_map
> WHERE
> te.id_eff_date = t_po.id_eff_date
> AND
> ta.id_eff_date = t_po.id_avail AND
> -- Check dates
> (ta.dt_start <= cdate.now or
> ta.dt_start is null) AND
> (cdate.now <= ta.dt_end or
> ta.dt_end is null) AND
> te.n_begintype <> 0 AND
> ta.n_begintype <> 0 AND
> t_base_props.id_prop = t_po.id_po
> and t_po.id_po =
> template_po_map.id_po
> and t_ep_po.id_prop(+) = t_po.id_po
>
> /
>
> Thanks
> -harvinder
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
> INET: Harvinder.Singh_at_MetraTech.com
>
> 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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ketan Patel INET: ketanfororacle_at_yahoo.com 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 Tue Sep 25 2001 - 01:22:19 CDT
 |
 |