Year Parameter within Date in Command 1

[lbass]

I am using CR XI, with CR ORacle ODBC Driver 4.2 to an Oracle 8 database. I am trying to use a fiscal year parameter within a command (in a subreport) in this way:

where
"table"."date" < date({?fiscal year},7,1)

{?fiscal year} is a number parameter, which will be linked to the main report fiscal year parameter. Does anyone know the correct syntax? I've tried many different approaches, e.g., like:

'01-JUL-{?fiscal year}'

I've searched the SQL Server Programming and Oracle 8 forums without luck. Any help would be appreciated--even if it is directing me to the correct forum.

-LB

 

[synapsevampire]

Posting in an Oracle forum would return it quickly enough, anyway, I'll assume that you're entering the 4 year number, and don't wish to automate the month and date portion, so try:

"table"."date" <
to_date(to_char({?fiscal year},'0000')||'-07-01','YYYY-MM-DD')

To_date reference:

http://www.techonthenet.com/oracle/functions/to_date.php

To_char reference:

http://www.techonthenet.com/oracle/functions/to_char.php

Hopefully this will resolve.

-k

 

[synapsevampire]

Also curious why you're using ODBC as opposed to Native connectivity? The only real difference should be that ODBC is slower.

-k

 

[lbass]

Thanks for responding, SV. I tried the above and got an error message, so checked out the links and changed the '0000' to '9999', as it looked like zeros were for padding with zeros. Still no luck. I get an error message:

"unrecognizable ODBC SQL escape sequence"

I posted in the Oracle 8 forum, too.

Re: the "native" connectivity--I don't really understand what this means. My current driver is a Data Direct driver that was recommended by BO. From your other posts on this topic, I thought you meant Oracle Server was the "native connectivity" in the driver list, but it doesn't appear--maybe because Oracle 8 is an old version? I don't really understand drivers, but would like to use the optimal one, obviously. How would I know a "native connection" if I saw one?

-LB

 

[lbass]

Okay, this problem is solved. It turned out that the problem was partly due to Crystal Reports--where if you add a new (number only?) parameter to an existing command, it doesn't recognize it. I had understood that this was corrected in the first service pack--but I guess not. When I tried a new command from scratch, the following syntax worked:

where
"table"."date" < to_date('{?fiscal year}-07-01')

I should have realized earlier that that was the problem, since it wasn't prompting me for the year!

Thanks for your help, SV!

-LB

 

[lbass]

As a follow-up, I realized I really needed the {?fiscal year}-1. Had to change the syntax again to:

"table"."date" <
TO_DATE(TO_CHAR({?Fiscal Year}-1)||'-07-01')

...as you originally suggested. Wasted my time because I didn't understand the CR glitch, I guess.

-LB

 

[synapsevampire]

Sorry about the 0000, was going from memory, but I had faith that if I sent along links to the basic functions you'd figure it out.

-k

 

[lbass]

Could you expand on the native connectivity issue? Thanks.

-LB

 

[synapsevampire]

If you're not seeing Oracle Server as a connection type, yet ODBC works, then there are a couple of possibilities.

Perhaps it wasn't installed, so check that, or perhaps the ORACLE CLIENT installed isn't supported, I think that the 9 client supports 8 and 9, but I can't recall, post which client you're using and I'll try to look it up.

-k

 

[lbass]

It's Oracle 8. CR XI didn't come with drivers for Oracle 8, so originally I used a Microsoft ODBC for Oracle driver, then recently switched to the one I mentioned earlier.

-LB

 

[synapsevampire]

I'm speaking of the Oracle client, not drivers.

Go under Start->Programs and you should see an Oracle group, it generally states the version as well.

-k

 

[lbass]

I know you mean the Oracle software, and it is version 8. Are you expecting more specificity, e.g., 8.xxx? Or are you surprised at how dated this is? This is being used for our statewide database and because of the number of users and related cost, is unlikely to be updated in the near future. I'm not at work to look at the particulars, but I know it's 8.x, because I looked it up when I upgraded to CR XI and there were no drivers that worked.

-LB

 

[synapsevampire]

There are 2 components, there is the local Oracle client, and there is the database, I speak to the locally installed client.

So try installing the Oracle 9 client and see if it shows, it's bakward compatible with the Oracle 8 database.

-k