We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

ORA 00936 missing expression

deeboll
deeboll asked
on
Medium Priority
837 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%'
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
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%'

Author

Commented:
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.
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')
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.