cancel
Showing results for 
Search instead for 
Did you mean: 

Trouble getting date in command SQL to work

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

He had the right idea. It did not fix the problem exactly but it was the correct fix. Wierd. Tha syntax made others work. HAve no idea of why not this time but his answer was right on.

0 Kudos

Hello,

I think the problem is your parameter is a string but your are using Date function in it.... Why are you using a String and not a Date parameter?

Doing a google search I found this http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html:

The general usage of TO_DATE is:

TO_DATE(<string>, '<format>')

For strings:

The general usage of TO_CHAR is:

TO_CHAR(<date>, '<format>')

So you may want to look around for examples to meet your needs.

Don

Former Member
0 Kudos

The reason I did not use date is that I could never make it work and this is nto the first time I have tried.

I recoded the date clause to be TO_DATE('{?EOMDate}', 'MM/DD/YYYY'). That failed with invalid character. I hard coded in '09/30/2011' verus the parameter and it did work. Thank you for that information. As to why the parameter did not work, that is not clear at all. String parameters are always enclosed in single quotes. Still I tried it without the quotes and still got the invalid character error. I ahve another report I did with dates and the syntax is the same TO_DATE ('{?Start}', 'MM/DD/YYYY') . Could it be that the command is just corrupt by now with all the changes I've made?

Former Member
0 Kudos

It is not corruption. I opened a blank report and put the command in. IT worked with the ahrd-coded date and did not work with the parameter. That makes no sense as I have another report that uses this construct and it works.

Former Member
0 Kudos

It gets weirder. I started antoher new report and used date this time which has never worked for me. It worked. I don't understand why but I am going to run with it. Thank you for your help. The TO_DATE construct was absolutely on target and I was mssing that.

0 Kudos

Great please mark as answered.

Don