table linking in CRXI R2 with Oracle 10G

[rns031364]

I'm having trouble linking two tables on date fields. Both field types are date, the only difference is that one table has the data stored in date/time format and the other table has the data stored in straight date format. If I pull the sql to Toad, I get the same result, no data. But if I trunc the date/time field in the where clause the join works fine. Has anyone else had this issue or is there a report/database option that needs to be set for this to work? I suppose I could write the sql directly in as a command and do the trunc there but I would rather not if I don't have to.

 

[Turkbear]

Hi,
The command object is the better way to go, otherwise you would need to use a sub-report and link by a formula that truncates the date/time to just the date...Subs are generally to be avoided if some other method exists..

One other way would be to create a view on the table with the Truncated DateTime value and use that view instead of the table..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rns031364]

Thanks Turkbear

I'm displaying these reports via the .net viewer in a 2005 ASP.net app. I pass parms from the ASP.net app to the report. Are there any tricks involved passing parms to the command object vs passing parms to the main report?

I'm converting some old reports from 8.5 to XI. The 8.5 report makes the link correctly against the same Oracle DB in the problem descibed in my orginal post. That seems a little odd. Any thoughts about that?

rns

 

[Turkbear]

Hi,
I have noticed that the Oracle SQL created by XI is 'stricter' then the code created by 8.5 ( or 10)-
If you use the 'Show Sql' option under the Database tab, you can probably see the difference ( it is particulary obvious with Outer Joins - XI creates the SQL using the newer ( for Oracle) ANSI stype - ..Left Outer Join ON.., instead of the previous use of the (+) operator)

The parameter you create in the command object becomes a 'regular' parameter when the report uses that Command Object, so the prompting should work the same..





To Paraphrase:"The Help you get is proportional to the Help you give.."