Skip to Content
0
Former Member
Oct 21, 2011 at 07:23 PM

Trouble getting date in command SQL to work

197 Views

I have the following SQL in a CR 11 command:

select j.instrument_type,j.cost_type_cd,j.customer,j.bs, j.product,j.del_pd, j.bl_num, j.date2,j.match,j.jrnl_ref,j.mot_name,

j.tank_name, j.trade_num , j.date3, j.volume, j.invoice, j.trade_price,

j.mrkt_price, j.book_pl, j.pl, o.strategy_name, j.snapshot

from JE_TABLE_20110930_LB j, ORG_STRATEGY o

where j.STRATEGY in (select external_ref from org_strategy where strategy_num={?Strat}) and j.strategy=o.external_ref

and j.cost_in_mtm=1 and

j.jrnl_dt=('{?EOMDate}', 'MM/DD/YYYY')

order by j.PL

The parameter EOMDate is defined as string.

When I try to save this, I input a date in the format I chose and get an invalid character error. So, I tried hard codong the date:

select j.instrument_type,j.cost_type_cd,j.customer,j.bs, j.product,j.del_pd, j.bl_num, j.date2,j.match,j.jrnl_ref,j.mot_name,

j.tank_name, j.trade_num , j.date3, j.volume, j.invoice, j.trade_price,

j.mrkt_price, j.book_pl, j.pl, o.strategy_name, j.snapshot

from JE_TABLE_20110930_LB j, ORG_STRATEGY o

where j.STRATEGY in (select external_ref from org_strategy where strategy_num={?Strat}) and j.strategy=o.external_ref

and j.cost_in_mtm=1 and j.jrnl_dt=('09/30/2011', 'MM/DD/YYYY')

order by j.PL

This got an ORacle error 1797: this operator must be followed by ANY or ALL. That really makes no sense.

So, I hard coded it back to '20110930' and it worked with no errors. So, I creatd the paramter again as string and set the jrnl_dt='{?EOMDate}' and got the invalid character error again.

Does anyone out there see what I am doing wrong. I went with a string variable after using a date variable did not work, thinking it would be easier. Any help would be deeply appreciated.