Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ORA 00936 missing expression

Posted on 2011-04-25
4
Medium Priority
?
824 Views
Last Modified: 2014-03-19
For the life of me, I can't figure out where I went wrong with this query - I have a feeling its got to do with quotations. Can someone help me out here

SELECT
t.updated_date as "Last Updated Date",
dxr.ext_deal_id as "Endur Deal#",
w.status_name as "Workflow Status",
CASE WHEN edc.exch_deal_code like "%/%" THEN substr(edc.exch_deal_code,1,9) ELSE edc.exch_deal_code END as "Exchange ID",
CASE WHEN t.buy_sell_flag = "S" THEN t.volume_nbr * -1 ELSE t.volume_nbr END as "Bought/Sold",
p.price as "Price",
dt.label as "Deal Type",
u1.user_name as "Trade Owner",
t.deal_group_id as "Trade DG",
to_char(t.trade_date,'mm/dd/yy') as "Trade Date",
to_char(t.start_date,'mm/dd/yy') as "Start Date",
to_char(t.end_date,'mm/dd/yy') as "End Date",
t.created_date as "Created Date",
u2.user_name as "Execution Trader",
u4.user_name as "CParty Trade Owner",
t2.deal_group_id as "CParty DG",
v.label as "Volume Type",
u3.user_name as "Last Updated By",
FROM
transaction t,
ecom_ext_deal_xref dxr,
workflow_status w,
tran_leg p,
deal_type dt,
ecom_user u1,
ecom_user u2,
ecom_user u3,
ecom_user u4,
deal_composer dc,
transaction t2,
broker_ref eb,
deal_party_ref edp,
broker_ref cb,
deal_party_ref cdp,
commodity c,
stusco_deal_type_xref sdt,
commodity_exchange ce,
volume_type v,
currency cur,
spread_deal_ref spr,
eeip_deal_confirm edc
WHERE t.coralconnect_deal_id = dxr.ecom_deal_id(+)
  AND t.workflow_status_id = w.workflow_status_id(+)
  and t.tran_num = p.tran_num(+)
  and t.trader_id = u1.user_id(+)
  and t.created_by_id = u2.user_id(+)
  and t.updated_by_id = u3.user_id(+)
  and t.customer_id = u4.user_id(+)
  and (t.deal_type_id = dt.deal_type_id(+)
  and dt.commodity_id = c.commodity_id(+))
  and t.tran_num = dc.linked_tran_num(+)
  and dc.offset_tran_num = t2.tran_num(+)
  and (t.broker_id = eb.broker_id(+)
  and eb.party_id = edp.party_id(+))
  and (t.clearing_broker_id = cb.broker_id(+)
  and cb.party_id = cdp.party_id(+))
  and (t.deal_type_id = sdt.deal_type_id(+)
  and sdt.commodity_exch_id = ce.commodity_exch_id(+))
  and t.volume_type_id = v.volume_type_id(+)
  and t.currency_id = cur.currency_id(+)
  and t.coralconnect_deal_id = spr.coralconnect_deal_id(+)
  and CASE WHEN t.comments like "Confirm Id %" THEN substr(t.comments,12, length(t.comments) - 11) ELSE null END = edc.confirm_id(+)  
  and p.price is not null
  and c.source_system = ""
  and t.active_flag = "Y"
  and w.status_name = "SNDCPT"
  and u1.user_name in ('','')  and t.trade_date = trunc(sysdate) and dxr.ext_deal_id like '1%'
0
Comment
Question by:deeboll
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35464803
remove the comma after "Last Updated By",

u3.user_name as "Last Updated By",
FROM
transaction t,

must be like this

u3.user_name as "Last Updated By"
FROM
transaction t,
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35464809
ORA 00936 missing expressionQuestion:
For the life of me, I can't figure out where I went wrong with this query - I have a feeling its got to do with quotations. Can someone help me out here

SELECT
t.updated_date as "Last Updated Date",
dxr.ext_deal_id as "Endur Deal#",
w.status_name as "Workflow Status",
CASE WHEN edc.exch_deal_code like "%/%" THEN substr(edc.exch_deal_code,1,9) ELSE edc.exch_deal_code END as "Exchange ID",
CASE WHEN t.buy_sell_flag = "S" THEN t.volume_nbr * -1 ELSE t.volume_nbr END as "Bought/Sold",
p.price as "Price",
dt.label as "Deal Type",
u1.user_name as "Trade Owner",
t.deal_group_id as "Trade DG",
to_char(t.trade_date,'mm/dd/yy') as "Trade Date",
to_char(t.start_date,'mm/dd/yy') as "Start Date",
to_char(t.end_date,'mm/dd/yy') as "End Date",
t.created_date as "Created Date",
u2.user_name as "Execution Trader",
u4.user_name as "CParty Trade Owner",
t2.deal_group_id as "CParty DG",
v.label as "Volume Type",
u3.user_name as "Last Updated By"
FROM
transaction t,
ecom_ext_deal_xref dxr,
workflow_status w,
tran_leg p,
deal_type dt,
ecom_user u1,
ecom_user u2,
ecom_user u3,
ecom_user u4,
deal_composer dc,
transaction t2,
broker_ref eb,
deal_party_ref edp,
broker_ref cb,
deal_party_ref cdp,
commodity c,
stusco_deal_type_xref sdt,
commodity_exchange ce,
volume_type v,
currency cur,
spread_deal_ref spr,
eeip_deal_confirm edc
WHERE t.coralconnect_deal_id = dxr.ecom_deal_id(+)
  AND t.workflow_status_id = w.workflow_status_id(+)
  and t.tran_num = p.tran_num(+)
  and t.trader_id = u1.user_id(+)
  and t.created_by_id = u2.user_id(+)
  and t.updated_by_id = u3.user_id(+)
  and t.customer_id = u4.user_id(+)
  and (t.deal_type_id = dt.deal_type_id(+)
  and dt.commodity_id = c.commodity_id(+))
  and t.tran_num = dc.linked_tran_num(+)
  and dc.offset_tran_num = t2.tran_num(+)
  and (t.broker_id = eb.broker_id(+)
  and eb.party_id = edp.party_id(+))
  and (t.clearing_broker_id = cb.broker_id(+)
  and cb.party_id = cdp.party_id(+))
  and (t.deal_type_id = sdt.deal_type_id(+)
  and sdt.commodity_exch_id = ce.commodity_exch_id(+))
  and t.volume_type_id = v.volume_type_id(+)
  and t.currency_id = cur.currency_id(+)
  and t.coralconnect_deal_id = spr.coralconnect_deal_id(+)
  and CASE WHEN t.comments like "Confirm Id %" THEN substr(t.comments,12, length(t.comments) - 11) ELSE null END = edc.confirm_id(+)  
  and p.price is not null
  and c.source_system = ""
  and t.active_flag = "Y"
  and w.status_name = "SNDCPT"
  and u1.user_name in ('','')  and t.trade_date = trunc(sysdate) and dxr.ext_deal_id like '1%'
0
 

Author Closing Comment

by:deeboll
ID: 35467151
Pratima - you saved my life. I was working late at night and worked on this for about couple hours only to realize that I had an extra comma - Silly me but what a relief! I thank you for going through it diligently.
0
 
LVL 1

Expert Comment

by:drozeveld
ID: 39939859
Hoping that you can see the same little problem in my "missing expression" statement here:

SELECT SO.SO_WR_DT,
SO.SO_STORE_CD,
SO_LN.DEL_DOC_NUM,
SO.SHIP_TO_ZIP_CD,
SO.SHIP_TO_CITY,
SO.ORD_TP_CD,
Sum(SO_LN.UNIT_PRC*QTY)
FROM SALES.SO SO, SALES.SO_LN SO_LN
WHERE SO.DEL_DOC_NUM = SO_LN.DEL_DOC_NUM
AND ((SO.STAT_CD<>'V'))
GROUP BY SO.SO_WR_DT,
SO.SO_STORE_CD,
SO_LN.DEL_DOC_NUM,
SO.SHIP_TO_ZIP_CD,
SO.SHIP_TO_CITY,
SO.ORD_TP_CD,
SO_LN.VOID_DT
HAVING (SO.SO_WR_DT Between ? And ?)
AND (SO_LN.VOID_DT Is Null)
AND (SO.SHIP_TO_ZIP_CD Between '48612' And '49770')
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question