Harvinder - Are you using descending columns on any of your indexes? I have
heard that Microsoft Access has problems querying tables that use this
feature? Just a thought.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Thursday, September 20, 2001 1:31 PM
To: Multiple recipients of list ORACLE-L
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
